views:

2003

answers:

4

Before someone gets in a tizzy because this was answered elsewhere (not sure if it was,but...), the 5 "related questions" shown right now do not match what I'm looking for, and I searched the string "sql insert" and did not find quite what I was looking for.

So I'm basically trying to figure out the simplest way to perform your basic insert operation in C#.NET using the SqlClient namespace. I'm using SqlConnection for my db link, I've already had success executing some reads, and I want to know the simplest way to insert data. I'm finding what seem to be pretty verbose methods when I google.

+8  A: 
using (var conn = new SqlConnection(yourConnectionString))
{
    var cmd = new SqlCommand("insert into Foo values (@bar)", conn);
    cmd.Parameters.AddWithValue("@bar", 17);
    conn.Open();
    cmd.ExecuteNonQuery();
}
Matt Hamilton
Spot on! Using a parameter not only will protect you from SQL injection attacks (depending on .net parameter type of course), but it will also allow SQL Server to cache the compiled query, and just substitute the parameter next time this code is called.
MPritch
Suggest changing to:using (var conn = new SqlConnection(yourConnectionString))using (var cmd = new SqlCommand("insert into Foo values (@bar)", conn)){ cmd.Parameters.AddWithValue("@bar", 17); conn.Open(); cmd.ExecuteNonQuery();}
MPritch
(Moving an old answer to here as a comment) I left the using off the SqlCommand deliberately as the OP was looking for the "simplest" code to achieve the insert (although I kept it on the connection 'coz that's more important). I agree that it's good practice.
Matt Hamilton
A: 
using (SqlConnection myConnection new SqlConnection("Your connection string")) 
{ 
    SqlCommand myCommand = new SqlCommand("INSERT INTO ... VALUES ...", myConnection); 
    myConnection.Open(); 
    myCommand.ExecuteNonQuery(); 
}
aku
-1. This approach would encourage building up a SQL string by hand and not using parameterised SQL. Sorry
MPritch
+1  A: 

@Matt and @aku, it's also good practice to wrap the SqlCommand in a using statement.

Eric Z Beard
Could you clarify this in more detail or provide a link? I understand the concept behind this approach but am wondering how far the benefits go.
steve
It's recommended to put a using statement around any disposable object, so you get the try-finally-dispose logic guaranteed without any chance of messing it up and not releasing resources.
Eric Z Beard
A: 

Since you seem to be just getting started with this now is the best time to familiarize yourself with the concept of a Data Access Layer (obligatory wikipedia link). It will be very helpful for you down the road when you're apps have more interaction with the database throughout and you want to minimize code duplication. Also makes for more consistent behavior, making testing and tons of other things easier.

AlexCuse