Nsryjdtyk

I'm trying to connect my DB to my ListView, and I'm trying to find a better way than what's in the book. I looked at a couple forums and a lot of them have the same thing like what's in my code down below.

We didn't have a lot of time to go over databases in class, so a lot of my knowledge with connection strings come from the internet and a small chapter in the book.
My Database name is GameStoreLibrary.

using System.Data;

using System.Data.SqlServerCe;



public partial class DisplayGameStoreTable : Form

{

    //WHAT THE C# FORUMS SAY TO DO

    public SqlCeConnection cn = new SqlCeConnection(@"

           Data Source=.;

           Initial Catalog=DB GameStoreLibrary;

           Integrated Security=True;

           MultipleActiveResultSets=True");



private void DisplayGameStoreTable_Load(object sender, EventArgs e)

{

    try

    {

        cn.Open();

    }



    catch(SqlCeException ex)

    {

        MessageBox.Show(ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);

        Application.ExitThread();

    }

 }



private void NewGameBttn_Click(object sender, EventArgs e)

{

    listView1.Items.Clear();

    SqlCeCommand cm = new SqlCeCommand("SELECT * FROM newGames ORDER BY gametitle ASC", cn);



    try

    {

        SqlCeDataAdapter da = new SqlCeDataAdapter(cm);

        DataTable dt = new DataTable();

        da.Fill(dt);



        foreach (DataRow dr in dt.Rows)

        {

            ListViewItem item = new ListViewItem(dr["gametitle"].ToString());

            item.SubItems.Add(dr["releasedate"].ToString());

            item.SubItems.Add(dr["console"].ToString());

            item.SubItems.Add(dr["company"].ToString());

            item.SubItems.Add(dr["gameprice"].ToString());

            item.SubItems.Add(dr["quantity"].ToString());

        }

    }



    catch(Exception ex)

    {

        MessageBox.Show(ex.Message, Application.ProductName, MessageBoxButtons.OK, MessageBoxIcon.Error);

    }

}

Small Tip :

Try to use a DBConnect class instead of typing connection string every single time and closing the connection.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data.SqlClient;



namespace InventoryManagementSystem

{

    class DBConnect : IDisposable

    {





        private static String connectionString = @"Data Source=(LocalDB)v11.0;AttachDbFilename=D:PrivateInventoryManagementSystemInventoryManagementSystemInventoryDB.mdf;Integrated Security=True";

        public SqlConnection con = new SqlConnection(connectionString);



        public DBConnect()

        {

            try

            {

                con.Open();

                Console.WriteLine("Database connected");

            }

            catch (Exception e)

            {

                Console.WriteLine(e.StackTrace);

                Console.WriteLine("Database Connection Failed");

                throw new Exception();

            }

        }



        public void Dispose()

        {

            con.Close();

        }

    }

}

After you have this in your project you just have to create an object whenever you want to access the database.

public void getData(){



    using(DBConnect db = new DBConnect()){

        String q = "select * from TestTable";

        SqlCommand cmd = new SqlCommand(q,db.con);

        SqlDatareader r = cmd.ExcecuteReader();



    }

}

This will automatically close the connections too.

To add on to Gihan's answer, it's also an accepted practice to create the App.Config file and put the connection string in there so it's not inside your source code. Then it's easier to change without recompiling anything.

Use the ConnectionStrings section of the App.Config and then you can get the connection string using the code:

System.Configuration.ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;