SQL Insert/Update/Select/Delete using C#

Lusiphur 2 21K Views

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();

Dani AI

Generated

A few safe, modern patterns will make these ADO.NET snippets easier to reuse and debug.

  • Always parameterize. It prevents SQL injection and fixes the common type/quoting issues seen above (e.g., trying to add a quoted string to a numeric column). Also ensure your variable names actually exist in your code; if you copied @paramSource from somewhere, you must declare it and assign a value before executing.

  • Wrap connections/commands in using so they are disposed deterministically. Use transactions when multiple commands must all succeed or all fail.

To increment a numeric balance based on user input, avoid string concatenation and use a numeric parameter:

using (var conn = new SqlConnection(connectionString))
using (var cmd = new SqlCommand(
    "UPDATE dbo.Customer2 " +
    "SET cust_blnc = cust_blnc + @delta " +
    "WHERE cust_name = @name AND cust_village = @village;", conn))
{
    cmd.Parameters.Add("@delta", SqlDbType.Decimal).Value = Convert.ToDecimal(textBox3.Text);
    cmd.Parameters.Add("@name", SqlDbType.NVarChar, 100).Value = textBox2.Text;
    cmd.Parameters.Add("@village", SqlDbType.NVarChar, 100).Value = textBox1.Text;

    conn.Open();
    int rows = cmd.ExecuteNonQuery();
}

When you need atomicity across multiple operations:

using (var conn = new SqlConnection(connectionString))
{
    conn.Open();
    using (var tx = conn.BeginTransaction())
    using (var cmd = conn.CreateCommand())
    {
        cmd.Transaction = tx;
        // TODO: set cmd.CommandText and parameters, call ExecuteNonQuery/ExecuteScalar, possibly multiple times
        tx.Commit(); // or tx.Rollback() on error
    }
}

Optional tips: validate and parse inputs before assigning to parameters, size NVARCHAR parameters appropriately, and check rows to confirm an UPDATE actually matched your WHERE clause.

Member Avatar for tom_289
tom_289 0 Light Poster

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

Member Avatar for Lusiphur
Lusiphur 185 Practically a Posting Shark

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

Member Avatar for tom_289
tom_289 0 Light Poster

nice post, it gave me a concept, but if sumone is still confused i will recommend this video :) its a video series of 5, and link is for video 4, soo u can easily find what u looking for in series...1-5
http://www.youtube.com/watch?v=Y-aGjF6_Ptc&feature=related

thanks alot

Member Avatar for lapssone
lapssone 0 Newbie Poster

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

Member Avatar for Lusiphur
Lusiphur 185 Practically a Posting Shark

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?

Member Avatar for topxuchenghua
topxuchenghua 0 Newbie Poster

Lack of
sqlConn.BeginTransaction();

Member Avatar for Lusiphur
Lusiphur 185 Practically a Posting Shark

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 :)

Member Avatar for N☼xXx
N☼xXx 0 Newbie Poster

thx :)

Member Avatar for Revathy_1
Revathy_1 0 Newbie Poster

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.

Member Avatar for djjeavons
djjeavons 113 partis vestri scientia

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

Member Avatar for sarvesh_3
sarvesh_3 0 Newbie Poster

how to update the data with privious value in c#

Member Avatar for sarvesh_3
sarvesh_3 0 Newbie Poster

with sql server database

Member Avatar for sarvesh_3
sarvesh_3 0 Newbie Poster

cmd11.CommandText = "UPDATE customer2 SET cust_blnc = cust_blnc +'"+textBox3.Text+"' WHERE cust_name='"+textBox2.Text+"' and cust_village='"+textBox1.Text+"'";
this code not update the table with privious value

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Edit Preview