G
Guest
One thing I love about the .NET framework is that if you think there ought to be an easy way to do something, there usually is.
I have an app where a user specifies a value. I look up that value in the database, and see if there is a corresponding record. If there is none, I create the record with default values. My code looks a bit like this:
SqlDataAdapter da= new SqlDataAdapter("Select * from mytable where mykey=keyvalue",myconnectionstring);
DataSet ds=new DataSet();
da.Fill(ds);
if (ds.Tables["Table"].Rows.Count==0) //if the table is empty
{DataRow row=ds.Tables["Table"].NewRow();
SetMyDefaultValues(row); //Add the default values to the new row.
ds.Tables["Table"].Rows.Add(row); //Put the new row back in the table.
da.Update(ds); //Save the change to the table, but of course it fails at this point.
}
Of course, the last line fails, with the exception "Update requires a valid insert command...."
That seems fair enough, since I don't have an insert command. My question is, is there an easy way to create an insert command based on the contents of the table and/or row object?
In the designer, there is a wizard that does a wonderful job. I just add a sqladapter to a form, launch the wizard, and before you know it, I have a beautiful insert command for the adapter. In fact, my workaround for my problem is that I do just that. In the designer, I create a form and execute the adapter wizard. Then I choose "properties" for the data adapter, find the insert command text, copy it, and paste it into code.
(Something like,
string insertcommandtext=<pasted code>;
SqlCommand insertcommand=new SqlCommand(insertcommandtext);
da.InsertCommand=insertcommand;
da.Update(ds);
)
That's all very well, and it works fine, but it is a little unwieldy, and the program is under development, and I fully expect the set of fields to grow as time goes on, as the rest of the team decides to throw in new stuff. Every time they grow, I'll have to reexecute the adapter wizard, and do some cutting and pasting, which could lead to errors.
It seems like there ought to be a
da.GenerateInsertCommandUsingTableSchema(ds.Tables[0])
After all, that is what the wizard must be doing. I just want to do whatever it is that the wizard is doing, but at run time instead of design time.
Dave
I have an app where a user specifies a value. I look up that value in the database, and see if there is a corresponding record. If there is none, I create the record with default values. My code looks a bit like this:
SqlDataAdapter da= new SqlDataAdapter("Select * from mytable where mykey=keyvalue",myconnectionstring);
DataSet ds=new DataSet();
da.Fill(ds);
if (ds.Tables["Table"].Rows.Count==0) //if the table is empty
{DataRow row=ds.Tables["Table"].NewRow();
SetMyDefaultValues(row); //Add the default values to the new row.
ds.Tables["Table"].Rows.Add(row); //Put the new row back in the table.
da.Update(ds); //Save the change to the table, but of course it fails at this point.
}
Of course, the last line fails, with the exception "Update requires a valid insert command...."
That seems fair enough, since I don't have an insert command. My question is, is there an easy way to create an insert command based on the contents of the table and/or row object?
In the designer, there is a wizard that does a wonderful job. I just add a sqladapter to a form, launch the wizard, and before you know it, I have a beautiful insert command for the adapter. In fact, my workaround for my problem is that I do just that. In the designer, I create a form and execute the adapter wizard. Then I choose "properties" for the data adapter, find the insert command text, copy it, and paste it into code.
(Something like,
string insertcommandtext=<pasted code>;
SqlCommand insertcommand=new SqlCommand(insertcommandtext);
da.InsertCommand=insertcommand;
da.Update(ds);
)
That's all very well, and it works fine, but it is a little unwieldy, and the program is under development, and I fully expect the set of fields to grow as time goes on, as the rest of the team decides to throw in new stuff. Every time they grow, I'll have to reexecute the adapter wizard, and do some cutting and pasting, which could lead to errors.
It seems like there ought to be a
da.GenerateInsertCommandUsingTableSchema(ds.Tables[0])
After all, that is what the wizard must be doing. I just want to do whatever it is that the wizard is doing, but at run time instead of design time.
Dave