Димитър Грудев Димитър Грудев - 2 years ago 118
C# Question

Execute SQL query c#

this is the code :

con.SqlQuery(" SELECT TOP 1000 [Name],[CompanyName],[Version] FROM[MicroinvestDatabasesCatalog].[dbo].[MicroinvestDatabases] ORDER BY NAME");
con.NonQueryEx();


and the class for connection and query:

public SqlConnection conn;
public SqlCommand cmd;
public SqlDataAdapter da;
public DataTable dt;
public DataSet ds;

public void SqlDbConnect()
{
SqlConnection conn = new SqlConnection($"Data Source={server};User ID={user};Password={password};");
conn.Open();

}

public void SqlQuery(string queryText)
{
cmd = new SqlCommand(queryText, conn);
}

public DataTable QueryEx()
{
da = new SqlDataAdapter(cmd);
dt = new DataTable();
da.Fill(dt);
return dt;
}
public void NonQueryEx()
{

cmd.ExecuteNonQuery();



}


but have problem with cmd.ExecuteNonQuery();
Can someone help with this problem?
Try different query but is still same...

Answer Source

Problem is in your class SqlDbConnect. You have a field public SqlConnection conn but in the constructor you create a new variable of type SqlConnection and open that instead. In the constructor you need to use the classes field:

    public SqlConnection conn;
    public SqlCommand cmd;
    public SqlDataAdapter da;
    public DataTable dt;
    public DataSet ds;

    public void SqlDbConnect()
    {
        conn = new SqlConnection($"Data Source={server};User ID={user};Password={password};");
        conn.Open();
    }

    public void SqlQuery(string queryText)
    {
        cmd = new SqlCommand(queryText, conn);
    }

    public DataTable QueryEx()
    {
        da = new SqlDataAdapter(cmd);
        dt = new DataTable();
        da.Fill(dt);
        return dt;
    }
    public void NonQueryEx()
    {
        cmd.ExecuteNonQuery();
    }

Additionally, if you want to select data you can't use ExecuteNonQuery. Instead better use the method QueryEx() which returns you a DataTable:

con.SqlQuery("SELECT TOP 1000 [Name],[CompanyName],[Version] FROM[MicroinvestDatabasesCatalog].[dbo].[MicroinvestDatabases] ORDER BY NAME");
DataTable dt = con.QueryEx();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download