CODEWITHSUNDEEP
c#sqlconnection
Jaluvshuskies
Jaluvshuskies

Reputation: 35

C# How to execute a string and then store the results of that?

For reference, this page (add.ashx.cs), is an add page to a database.

What I'm trying to do is :

  1. figure out how to execute string queryID, and then
  2. store the results of queryID

I'm a bit new at this, but this is what I'm working with so far. Am I on the right path, and what should I change? I don't believe the code below includes storing the results, but just executing queryID.

// new query to get last ID value
// store the command.executeNonQuery results into a variable

string queryID = "SELECT TOP (1) IDENT_CURRENT('dbo.license_info') FROM dbo.license_info";
// first: look up how to execute queryID
// then: store results of query ^

// execute queryID? (section below)
SqlConnection sqlConnection1 = new SqlConnection(queryID);
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
cmd.CommandText = "Select * FROM queryID";
cmd.CommandType = CommandType.Text;
cmd.Connection = sqlConnection1;

sqlConnection1.Open();

reader = cmd.ExecuteReader();
// data is accessible through the datareader object here

sqlConnection1.Close();

Upvotes: 0

Views: 2375

Answers (1)

Romano Zumbé
Romano Zumbé

Reputation: 8079

There are some things missmatched in your code sample. First queryID is your actual query. Second in SqlConnection you need to provide a connection string, that connects to your database (SQL Server, ACCESS, ...). A valid example could look like this:

    // this is just a sample. You need to adjust it to your needs
    string connectionStr = "Data Source=ServerName;Initial Catalog=DataBaseName;Integrated Security=SSPI;";
    SqlConnection sqlConnection1 = new SqlConnection(connectionStr);
    SqlCommand cmd = new SqlCommand(sqlConnection1 );
    SqlDataReader reader;

    cmd.CommandText = "SELECT TOP (1) IDENT_CURRENT('dbo.license_info') FROM dbo.license_info";
    cmd.CommandType = CommandType.Text;
    sqlConnection1.Open();

    reader = cmd.ExecuteReader();

    List<string> results = new List<string>();

    if(reader.HasRows)
    {
        while(reader.Read())
        {
            results.Add(reader[0].ToString());
        }
    }

    sqlConnection1.Close();

Another thing is, that you execute a reader but only select one single value. You can perfectly use ExecuteScalar for that:

// this is just a sample. You need to adjust it to your needs
string connectionStr = "Data Source=ServerName;Initial Catalog=DataBaseName;Integrated Security=SSPI;";
SqlConnection sqlConnection1 = new SqlConnection(connectionStr);
SqlCommand cmd = new SqlCommand(sqlConnection1 );

cmd.CommandText = "SELECT TOP (1) IDENT_CURRENT('dbo.license_info') FROM dbo.license_info";
cmd.CommandType = CommandType.Text;
sqlConnection1.Open();

string result = cmd.ExecuteScalar().ToString();

sqlConnection1.Close();

One last thing. You should use objects that implement IDisposable in a using block. This way the will be removed from memory when they are no longer needed:

// this is just a sample. You need to adjust it to your needs
string connectionStr = "Data Source=ServerName;Initial Catalog=DataBaseName;Integrated Security=SSPI;";
using(SqlConnection sqlConnection1 = new SqlConnection(connectionStr))
{
    SqlCommand cmd = new SqlCommand(sqlConnection1 );

    cmd.CommandText = "SELECT TOP (1) IDENT_CURRENT('dbo.license_info') FROM dbo.license_info";
    cmd.CommandType = CommandType.Text;
    sqlConnection1.Open();

    string result = cmd.ExecuteScalar().ToString();
}

Upvotes: 3

Related Questions