Inserting Data: Technique Question

J

joe_pool_is

Well-known member
Joined
Jan 18, 2004
Messages
451
Location
Texas
Ive got some code that works whenever I fill my SQL Server tables, but now that I am getting used to how to do this, my technique is starting to look like it does some unnecessary steps.

I have found that before I can call a SqlDataAdapters Update method after specifying an InsertCommand, I have to provide it with a Select statement and call the Fill method so that the Column Names are known whenever I try to populate fields for my InsertCommand.

After I call the SqlDataAdapters Fill method, I create a NewRow for the DataTable, and supply each Column Name with a Value.

Next, I create a new SqlCommand and add Parameter values to it.

After this, I assign this SqlCommand object to the SqlDataAdapters InsertCommand and call the SqlDataAdapters Update method.

Am I doing too many steps? It seems unnecessary to populate the Select statement and call the Fill method on the SqlDataAdapter first. It also seems like a waste of time to create a NewRow for the DataTable.

Like I said, this does work. Should I continue in this fashion, or is there a more streamlined technique?
Code: 
string select = string.Format("SELECT [Column1], [Column2], [Column3] " + 
                "FROM Table2 WHERE ([Column2={0})", strValue2);
string insert = "INSERT INTO Table2 ([Column1], [Column2], [Column3]) " +
                "VALUES (@VAL1, @VAL2, @VAL3)";
SqlCommand cmd = new SqlCommand(select, m_db);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
try {
  da.Fill(dt);
  DataRow dr = dt.NewRow();
  dr["Column1"] = strValue1;
  dr["Column2"] = strValue2;
  dr["Column3"] = strValue3;
  cmd = new SqlCommand(insert, m_db);
  cmd.Parameters.Add("@VAL1", SqlDbType.VarChar, 20).Value = strValue1;
  cmd.Parameters.Add("@VAL2", SqlDbType.VarChar, 20).Value = strValue2;
  cmd.Parameters.Add("@VAL3", SqlDbType.VarChar, 20).Value = strValue3;
  da.InsertCommand = cmd;
  int nCount = da.Update(dt);
  if (nCount != 1) {
    Console.WriteLine(string.Format("{0} records were affected.", nCount);
  }
} catch (SqlException e) {
  Console.WriteLine(e.Message);
} finally {
  cmd.Dispose();
  dt.Dispose();
  da.Dispose();
}
 
Why use a SqlDataAdapter at all? I usually do it something like this:

Code: 
string insert = "INSERT INTO Table2 ([Column1], [Column2], [Column3]) " +
                "VALUES (@VAL1, @VAL2, @VAL3)";
SqlCommand cmd = new SqlCommand(insert, m_db);
m_db.Open();
try {
  cmd.Parameters.Add("@VAL1", SqlDbType.VarChar, 20).Value = strValue1;
  cmd.Parameters.Add("@VAL2", SqlDbType.VarChar, 20).Value = strValue2;
  cmd.Parameters.Add("@VAL3", SqlDbType.VarChar, 20).Value = strValue3;
  int nCount = cmd.ExecuteNonQuery();
  if (nCount != 1) {
    Console.WriteLine(string.Format("{0} records were affected.", nCount);
  }
} catch (SqlException e) {
  Console.WriteLine(e.Message);
} finally {
  cmd.Dispose();
  m_db.Close();
  m_db.Dispose();
}

assuming that m_db is your SqlConnection object.

Todd
 
That works pretty darned well!

Why are all the examples showing me how to do this with a SqlDataAdapter and DataTable?

If I were to use a SELECT statement in an SqlCommand object, could I pull the information from it? How would I, for example, get VAL2 from Column2? What if more than one record is returned?
 
joe_pool_is said:
Why are all the examples showing me how to do this with a SqlDataAdapter and DataTable?
Click to expand...

I cant tell you why they do that. My first couple of applications used the SqlDataAdapter method too. I dont remember when I figured out that it was overkill for what I was trying to accomplish.

joe_pool_is said:
If I were to use a SELECT statement in an SqlCommand object, could I pull the information from it? How would I, for example, get VAL2 from Column2?
Click to expand...

Yes. You would create your SELECT statement to only return Column2 and then do something like:

Code: 
VAL2 = cmd.[SIZE=2]ExecuteScalar()
[/SIZE]

Of course ExecuteScalar returns an object, and so it would need to convert it to your needed data type.

joe_pool_is said:
What if more than one record is returned?
Click to expand...

Look into the ExecuteReader method. If you want an example, I can give you one later, but I gotta go right now.

Todd
 
MS have made a big deal about the Dataset / dataadapter approach and that is where they have concentrated their efforts (designers etc.) - plenty of tutorials etc. just take the simplest approach and go with whatever MS are pushing.

Personally for anything other than a simple project I would rather use a O/R mapper to generate my data access layer and save myself the effort and boredom of doing it myself...
 
Back
Top