Executing SQL query from C# code

Question

I'm trying to execute SQL query from C# code, but it doesn't return any values, but when I write query directly to SQL it works fine.

static int TestGettingData()
{
        int rows;
        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand("SELECT [UserName] FROM [aspnet_Users]", connection))
            {
                connection.Open();
                rows = command.ExecuteNonQuery();
            }
        }
        return rows;
}

And connection string:

<connectionStrings>
<add name ="DefaultConnection" connectionString="Server=myServer;
            Database=MyDatabase;User 
            Id=User;
            Password=password;
            MultipleActiveResultSets=True;"
            providerName="System.Data.SqlClient"/>
</connectionStrings>

And TestGettingData() always returning -1.

"I'm trying to execute SQL query" - and yet you're calling ExecuteNonQuery. — Jon Skeet
– Jon Skeet, Commented May 22, 2015 at 8:49

fubo · Accepted Answer · 2015-05-22 09:35:51Z

There are multiple ways to get Data out of your Database

ExecuteScalar

if you have one result field you can use

string Command = "SELECT [UserName] FROM [aspnet_Users];";
using (SqlConnection myConnection = new SqlConnection(ConnectionString))
{
    myConnection.Open();
    using (SqlCommand myCommand = new SqlCommand(Command, myConnection))
    {
        string Result = (string)myCommand.ExecuteScalar(); // returns the first column of the first row
    }
}

SqlDataAdapter

if you expect multiple rows / columns you can load it into a DataTable by

string Command = "SELECT [UserName] FROM [aspnet_Users]";
using (SqlConnection myConnection = new SqlConnection(ConnectionString))
{
    using (SqlDataAdapter myDataAdapter = new SqlDataAdapter(Command, myConnection))
    {
        DataTable dtResult = new DataTable();
        myDataAdapter.Fill(dtResult);
    }
}

SqlDataReader

Another solution is the SQLDataReader. E.g. if you want to load all rows of a column into a List. This has less overhead than the DataAdapter.

List<string> Result = new List<string>();
string Command = "SELECT [UserName] FROM [aspnet_Users];";
using (SqlConnection mConnection = new SqlConnection(ConnectionString))
{
    mConnection.Open();
    using (SqlCommand cmd = new SqlCommand(Command, mConnection))
    {
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                Result.Add((string)reader[0]);
            }
        }
    }
}

Why is ` myConnection.Open();` not required on the second example?
@BeKindToNewUsers The Fill method implicitly opens the Connection that the DataAdapter is using if it finds that the connection is not already open - source

Soner Gönül · Accepted Answer · 2015-05-22 08:49:57Z

Because documentation say so;

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

You can use COUNT(*) wiht ExecuteScalar method to get your number.

using (SqlCommand command = new SqlCommand("SELECT COUNT([UserName]) FROM [aspnet_Users]", connection))
{
     connection.Open();
     rows = (int)command.ExecuteScalar();
}

Aedahl · Accepted Answer · 2015-05-22 10:29:44Z

Here's what I think is the best way to access and retrieve data from a DB:

Create a DBconnection class

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SQLexample
{
/// <summary>
/// A class that makes the connection to the SQL Database
/// </summary>
class DBconnection
{
    // variables
    private string sql_string;
    private string strCon;
    System.Data.SqlClient.SqlDataAdapter da_1;

    // set methods
    public string Sql
    {
        set { sql_string = value; }
    }

    public string connection_string
    {
        set { strCon = value; }
    }

    // DataSet
    public System.Data.DataSet GetConnection
    {
        get { return MyDataSet(); }
    }

    // MyDataSet method
    private System.Data.DataSet MyDataSet()
    {
        System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(strCon);
        con.Open();

        da_1 = new System.Data.SqlClient.SqlDataAdapter(sql_string, con);

        System.Data.DataSet dat_set = new System.Data.DataSet();
        da_1.Fill(dat_set, "Table_Data_1");

        con.Close();

        return dat_set;
    }

    // Update DB method
    public void UpdateDB(System.Data.DataSet ds)
    {
        System.Data.SqlClient.SqlCommandBuilder cb = new System.Data.SqlClient.SqlCommandBuilder(da_1);
        cb.DataAdapter.Update(ds.Tables[0]);
    }
}
}

Then, from your other classes, whenever you want to access DB you do it like this:

try
{
    objConnect = new DBconnection();
    conStringAUTH = Properties.Settings.Default.authConnectionString;

    objConnect.connection_string = conStringAUTH;
    objConnect.Sql = "QUERY GOES HERE";

    ds = objConnect.GetConnection;

    // Data manipulation
    maxRows = ds.Tables[0].Rows.Count;

    if (maxRows == 0)
    {
        // Your query returned no values
    }

}
catch (Exception err)
{
    MessageBox.Show(err.Message);
}

Hope this helps...

Collectives™ on Stack Overflow

Executing SQL query from C# code

3 Answers 3

2 Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Linked

Related