Adding a row to SQL table using C#

[Larry Antram]

This is probably a dumb question, but I'm trying to figure out how to add a row to an SQL table using C# -- without having to create a messy INSERT command. For example, in ASP/JScript I can do something like this:

con = Server.CreateObject( "ADODB.Connection" );
con.Open( "CONNECT_STRING" );

rs = Server.CreateObject( "ADODB.Recordset" );
rs.CursorLocation = adUseClient;
rs.Open( "TABLE\_NAME", con, adOpenKeyset, adLockOptimistic, adCmdTable );

    rs.AddNew();
    rs( "COLUMN\_NAME" ) = COLUMN\_VALUE;
    rs( "COLUMN\_NAME" ) = COLUMN\_VALUE;
    ... etc ...
    rs.Update();
    rs.Close();

con.Close();

In C++, using these ADO classes[^], I can do something like this:

CADODatabase db;
if( db.Open( "CONNECT_STRING" )
{
CADORecordSet rs( &db );
if( rs.Open( "TABLE_NAME", CADORecordSet::openTable ) )
{
rs.AddNew();
rs.SetFieldValue( "COLUMN_NAME", COLUMN_VALUE );
rs.SetFieldValue( "COLUMN_NAME", COLUMN_VALUE );
... etc ...
rs.Update();
rs.Close();
}
db.Close();
}

I'm trying to learn how to do this in C#, and I've got this so far (okay, not much):

SqlConnection sql = new SqlConnection( "CONNECT_STRING" );
SqlCommand cmd = new SqlCommand( "COMMAND", sql );
cmd.ExecuteNonQuery();

I assume this would work, but as mentioned, I don't want to be burdened with creating a huge complicated INSERT command or escaping the ' character in fields. I'd rather just set the individual fields to some sort of object (like I've done with ADO Recordset in the past) and then use that to add a row to the table. Can anyone point me in the right direction? Any idea what classes I need to use to do this? Or a rough overview of the procedure?

[Heath Stewart]

You're not using the SQL statements correctly in .NET if you think INSERTs (or any other command) is hard. Take a look at the documentation for SqlParameter and SqlCommand.Parameters. You don't have to worry about properly escaping param values and can work with input, output, and return params with no problem:

SqlConnection conn = new SqlConnection(connectionString);
try
{
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "INSERT INTO MyTable (ID, Name, Birthday) " +
"VALUES(@ID, @Name, @Birthday)";
cmd.Parameters.Add("@ID", SqlDbType.Int);
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 40);
cmd.Parameters.Add("@Birthday", SqlDbType.DateTime);
string[] names = new string[] {"Dad", "Mom", "Brother"};
conn.Open();
for (int i=0; iThis is just an example to show you the power of parameterized statements. The same is done with SqlCommands when using the SqlDataAdapter.

The other way is to create a DataSet, build the schema (or create a strongly-typed DataSet using VS.NET's DataSet designer (or other data designers, like dragging and dropping a table from the connections tab), or the xsd.exe utility), and add rows to that, which you then call SqlDataAdapter.Update, but you'll still need parameterized queries.

This is how ADO.NET works and - if you architect your solution right - can be much better than the old ADO way (for example, DataSets are very good at tracking changes, dealing with relationships, and even identity columns).

-----BEGIN GEEK CODE BLOCK-----
Version: 3.21
GCS/G/MU d- s: a- C++++ UL@ P++(+++) L+(--) E--- W+++ N++ o+ K? w++++ O- M(+) V? PS-- PE Y++ PGP++ t++@ 5 X+++ R+@ tv+ b(-)>b++ DI++++ D+ G e++>+++ h---* r+++ y+++
-----END GEEK CODE BLOCK-----

[Larry Antram]

Thank you! That is exactly what I wanted to know. :-D