CODEWITHSUNDEEP
c#sqlsql-serverwinforms
furiousNoob
furiousNoob

Reputation: 53

How to put search result of a query in variables in C#?

I want to search some input in a data table and if exact data is found then I want to put those data into another table. If not, I will simply clear the corresponding TextBox. I have done theses so far.

    private void btn_InputConfirm_Click(object sender, EventArgs e) {
    string strConnection = @"Data Source=F_NOOB-PC\;Initial Catalog=ComShopDB;Integrated Security=True";
    SqlConnection objcon = new SqlConnection(strConnection);
    try {
        string strcmd1 = "SELECT partID,partAvailable FROM Parts WHERE partID LIKE '" + txtbox_ProductSerial.Text + "'AND partAvailable ='yes'";
        SqlCommand objcmd1 = new SqlCommand(strcmd1, objcon);
        objcon.Open();
        objcmd1.ExecuteNonQuery();
        objcon.Close();
    }
    catch (Exception ex) {
        MessageBox.Show(ex.Message);
    }
}

Some help will be very much appreciated. Thanks in advance.

Upvotes: 0

Views: 66

Answers (2)

Racil Hilan
Racil Hilan

Reputation: 25351

The easiest is to use DataAdapter and then use its Fill() function on a DataTable or DataSet. You do not need to open and close the connection as the Fill() function will do that for you:

private void btn_InputConfirm_Click(object sender, EventArgs e)
{

  string strConnection = @"Data Source=F_NOOB-PC\;Initial Catalog=ComShopDB;Integrated Security=True";
  SqlConnection objcon = new SqlConnection (strConnection);

  try
  {
      //Writing command//
      string strcmd1 = "SELECT partID,partAvailable FROM Parts WHERE partID LIKE '" + txtbox_ProductSerial.Text + "'AND partAvailable ='yes'";
      System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(strcmd1, objcon);
      System.Data.DataSet ds = new System.Data.DataSet();
      aa.Fill(ds);
  }
  catch ( Exception ex )
  {
    MessageBox.Show (ex.Message);
  }

Upvotes: 0

Selman Genç
Selman Genç

Reputation: 101701

You can use a DataTable, use ExecuteReader method and load all records into DataTable, then use AsEnumerable and some LINQ you can get your results as a List.

DataTable dt = new DataTable();
var reader = objcmd1.ExecuteReader();
if(reader.HasRows)
{
    dt.Load(reader);
    var myValues = dt.AsEnumerable()
              .Select(d => new { 
                                 Id = d["partID"], 
                                 Available = d["partAvailable"]
                               }).ToList();
}

Also you should consider using parameterized queries instead to prevent SQL Injection Attacks.

Upvotes: 3

Related Questions