Switching from SQL connection and SELECT etc. strings to a connection class

c# class datagridview database-connection connection-string

94 просмотра

1 ответ

I am trying to figure out how i can use a class for SQL Server connection and population of my datagrid view. So before I had something like this as an Click event for my button to select everything from my database.

SqlConnection con = new SqlConnection(@"Data Source =SQLEXPRESS; Initial Catalog = TestDB;");
        SqlConnection myConnection = new SqlConnection("user id=Admin;" +
                                   "password=05370537;server=localhost;" +
                                   "Trusted_Connection=yes;" +
                                   "database=TestDB; " +
                                   "connection timeout=30");
        SqlCommand comm = new SqlCommand();
        comm.Connection = myConnection;
        comm.CommandText = "SELECT * FROM Data";

        DataTable dta = new DataTable();
        SqlDataAdapter adapter = new SqlDataAdapter(comm);
        adapter.Fill(dta);
        dataGridView1.DataSource = dta;
        myConnection.Close();

Since I had those connection strings for every button I wanted to put them in a class to clean up my code... so I did that:

public class SqlDbConnect
{
    private SqlConnection con;
    public SqlCommand Cmd;
    private SqlDataAdapter sda;
    public DataTable dta;

    public SqlDbConnect()
    {
        con = new SqlConnection("user id=Admin;" +
                                   "password=05370537;server=localhost;" +
                                   "Trusted_Connection=yes;" +
                                   "database=Omnimet; " +
                                   "connection timeout=30");
        con.Open();
    }

    public void SqlQuery(string queryText)
    {
        Cmd = new SqlCommand(queryText, con);
    }

    public DataTable QueryEx()
    {
        sda = new SqlDataAdapter(Cmd);
        dta = new DataTable();
        sda.Fill(dta);
        return dta;
    }

    public void NonQueryEx()
    {
        try
        {
            Cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString(), "Error",MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }
}

now I want to use the class to fill my datagridview... How can I do so? Filling a combobox for example works just fine...

con = new SqlDbConnect();
        con.SqlQuery("INSERT INTO dbo.ESQUEL (Name) VALUES (@NameP)");
        con.Cmd.Parameters.AddWithValue("@NameP", textBox.Text.Trim());
        con.NonQueryEx();
        con.SqlQuery("SELECT * FROM dbo.ESQUEL");
        comboBox.Items.Clear();

        foreach (DataRow dr in con.QueryEx().Rows)
        {
            comboBox.Items.Add(dr[1].ToString());
        }

I assume it should look something like my dummycode here

           con = new SqlDbConnect();

       con.SqlQuery("SELECT * FROM Data");
       con.QueryEx();

        foreach(DataRow dr in con.QueryEx().Rows)
        {
            dataGridView1.DataSource=SqlDbConnect.dta;
        }

but I am just new to this and can't get it to work... All help is appreciated...

Thank You in advance....

Автор: incubuzz1978 Источник Размещён: 27.10.2019 11:37

Ответы (1)


2 плюса

Решение

Get rid of the foreach and directly assign the datatable to the DataSource property.

dataGridView1.DataSource=con.QueryEx();
Автор: ChrisBint Размещён: 24.03.2016 02:21
Вопросы из категории :
32x32