As it seems to come up often enough I figured I'd throw together a quick snippet outlining basic methods for data manipulation in SQL Server using C#.

In all examples the coder will need to substitute their own connection string details and variables.

I utilised parameters for 3 of the examples but they can just as easily be replaced by direct references to dynamic controls in your application if desired. The benefit of parameters, however, is that they set the 'type' of the variable being passed and add an additional level of type checking to the app.

I'm hoping I didn't bugger any of the code samples when throwing them together.

//Basic SELECT method to populate a DataSet from a SqlDataAdapter
SqlConnection sqlConn = new SqlConnection(connection string here);
SqlDataAdapter sqlAdapt = new SqlDataAdapter(@"SELECT * FROM tableName WHERE conditionColumn='False'", sqlConn);
SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(sqlAdapt);
DataSet sqlSet = new DataSet();
feedbackAdapt.Fill(sqlSet, "dataSetTableName");
feedbackConn.Close();

//Basic INSERT method with Parameters
SqlConnection sqlConn = new SqlConnection(connection string here);
SqlCommand sqlComm = new SqlCommand();
sqlComm = sqlConn.CreateCommand();
sqlComm.CommandText = @"INSERT INTO tableName (paramColum) VALUES (@paramName)";
sqlComm.Parameters.Add("@paramName", SqlDbType.VarChar);
sqlComm.Parameters["@paramName"].Value = paramSource;
sqlConn.Open();
sqlComm.ExecuteNonQuery();
sqlConn.Close();

//Basic UPDATE method with Parameters
SqlConnection sqlConn = new SqlConnection(connection string here);
SqlCommand sqlComm = new SqlCommand();
sqlComm = sqlConn.CreateCommand();
sqlComm.CommandText = @"UPDATE tableName SET paramColumn='@paramName' WHERE conditionColumn='@conditionName'";
sqlComm.Parameters.Add("@paramName", SqlDbType.VarChar);
sqlComm.Parameters["@paramName"].Value = paramSource;
sqlComm.Parameters.Add("@conditionName", SqlDbType.VarChar);
sqlComm.Parameters["@conditionName"].Value = conditionSource;
sqlConn.Open();
sqlComm.ExecuteNonQuery();
sqlConn.Close();

//Basic DELETE method with Parameters
SqlConnection sqlConn = new SqlConnection(connection string here);
SqlCommand sqlComm = new SqlCommand();
sqlComm = sqlConn.CreateCommand();
sqlComm.CommandText = @"DELETE FROM tableName WHERE conditionColumn='@conditionName'";
sqlComm.Parameters.Add("@conditionName", SqlDbType.VarChar);
sqlComm.Parameters["@conditionName"].Value = conditionSource;
sqlConn.Open();
sqlComm.ExecuteNonQuery();
sqlConn.Close();

on line 7 what does "feedbackAdapt" as i think its a parameter or sumthing....

on line 7 what does "feedbackAdapt" as i think its a parameter or sumthing....

Was actually a typo

feedbackadapt and feedbackconn should be sqladapt and sqlconn

Sorry for the confusion

oh, my God, above what is written in the recovery are not in line with the meaning of

oh, my God, above what is written in the recovery are not in line with the meaning of

Sorry if this seems rude but... um... huh? This post made no sense at all to me. What were you trying to say here?

Lack of
sqlConn.BeginTransaction();

Not sure if this is currently a standards requirement for TSQL or not but at the time that I wrote the snippets it wasn't absolutely required in order to make the code work.

My intention was to show the basic usage as *required* to complete the tasks as at the time it was a very commonly asked question and the snippets are sufficient to allow someone a working ability to fulfill the functions stated (with the exception of the above corrected typos of course).

I'm sure that the code could benefit from a plethora of refinements but still functions as stated :)

HI,
when i use the above code, I get an error saying: The name 'paramSource' does not exist in the current context.! could you please help me why that's the case?
Thank you.

Hi

This is because paramSource has not been declared in the code above. Essentially, this is the value that you want to associate to the parameter, so substitute paramSource for the value that you want to provide to the parameter.

HTH

The article starter has earned a lot of community kudos, and such articles offer a bounty for quality replies.