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);
        dataGridView1.DataSource = dta;

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");

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

    public DataTable QueryEx()
        sda = new SqlDataAdapter(Cmd);
        dta = new DataTable();
        return dta;

    public void NonQueryEx()
        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.SqlQuery("SELECT * FROM dbo.ESQUEL");

        foreach (DataRow dr in con.QueryEx().Rows)

I assume it should look something like my dummycode here

           con = new SqlDbConnect();

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

        foreach(DataRow dr in con.QueryEx().Rows)

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.

Автор: ChrisBint Размещён: 24.03.2016 02:21
Вопросы из категории :