locked
Best way to write INSERT in C# RRS feed

  • Question

  • Hi,

    what is the best way to use INSERT INTO in C# and why?

    1. way

    INSERT INTO TableName (name,surname,adress,...) VALUES (@name,@surname,@adress,...)
    + cmd.Parameters.AddWithValue("@name",txtName.Text);

    2. way

    INSERT INTO TableName (name,surname,adress,...) VALUES ('"+txtName.Text+"','"+txtSurname.Text+"','"+txtAdress.Text+"')
    ... without parameters


    Tuesday, September 27, 2016 4:30 PM

Answers

  • 1st way. Mostly because it is immune to SQL Injections:
    xkcd: Exploits of a Mom

    Building any DML query per String Connaction is just asking for trouble. Outside of very early learning you should not do it anymore.

    You can add extra abstaction layers like stored procedures or views to the mix. But SQL Parameters is realy the bigger thing.


    Remember to mark helpfull answers as helpfull and close threads by marking answers.

    • Proposed as answer by Konrad Neitzel Tuesday, September 27, 2016 10:27 PM
    • Marked as answer by Sabah ShariqMVP Thursday, October 6, 2016 10:48 AM
    Tuesday, September 27, 2016 4:51 PM
  • Hello,

    See the following for a working example. A part of the class for doing the insert where it returns the new record's primary key.

    namespace WindowsFormsApplication1
    {
        using System;
        using System.Data;
        using System.Data.SqlClient;
    
        public class DataOperations
        {
            private string InsertStatement = "INSERT INTO [Customer] (CompanyName,ContactName,ContactTitle) VALUES (@CompanyName,@ContactName,@ContactTitle); SELECT CAST(scope_identity() AS int);";
            /// <summary>
            /// Called from AddCustomers to add a single new record
            /// </summary>
            /// <param name="CompanyName"></param>
            /// <param name="ContactName"></param>
            /// <param name="ContactTitle"></param>
            /// <param name="NewIdentifier"></param>
            /// <returns></returns>
            /// <remarks></remarks>
            public bool AddNewCustomer(string CompanyName, string ContactName, string ContactTitle, ref int NewIdentifier)
            {
                using (SqlConnection cn = new SqlConnection { ConnectionString = Properties.Settings.Default.ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand { Connection = cn })
                    {
                        cmd.CommandText = InsertStatement;
                        cmd.Parameters.AddWithValue("@CompanyName", CompanyName);
                        cmd.Parameters.AddWithValue("@ContactName", ContactTitle);
                        cmd.Parameters.AddWithValue("@ContactTitle", ContactTitle);
                        cn.Open();
                        try
                        {
                            NewIdentifier = Convert.ToInt32(cmd.ExecuteScalar());
                            return true;
                        }
                        catch (Exception)
                        {
                            return false;
                        }
                    }
                }
            }
        }
    }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Proposed as answer by Konrad Neitzel Tuesday, September 27, 2016 10:28 PM
    • Marked as answer by Sabah ShariqMVP Thursday, October 6, 2016 10:48 AM
    Tuesday, September 27, 2016 4:58 PM
  • >>what is the best way to use INSERT INTO in C# and why?

    1. way for sure. You should always use parameters when executing parameterized queries in order to avoid SQL injections. Please refer to the following threads for more information about this:

    http://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements
    http://stackoverflow.com/questions/4892166/how-does-sqlparameter-prevent-sql-injection
    http://stackoverflow.com/questions/3216233/what-is-passing-parameters-to-sql-and-why-do-i-need-it

    Hope that helps.

    Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    • Proposed as answer by Konrad Neitzel Tuesday, September 27, 2016 10:28 PM
    • Marked as answer by Sabah ShariqMVP Thursday, October 6, 2016 10:48 AM
    Tuesday, September 27, 2016 8:28 PM

All replies

  • In my opinion the best way is to have a stored procedure doing the insert and call it from C#, for better separation of concern.

    If you really want to have your C# app holding SQL details and keep them tightly coupled, than this is one way of doing it:

    *Please modify this to fit your needs.

               SqlConnection Con = new SqlConnection("MyConnectionString");
                // create command object with SQL query and link to connection object
                SqlCommand Cmd = new SqlCommand("INSERT INTO Registrations " +
    		"(CustomerID, ProductCode, RegistrationDate) " +
                    "VALUES(@CustomerID, @ProductCode, @RegistrationDate)", 
    		Con);
    
                // create your parameters
                Cmd.Parameters.Add("@CustomerID", System.Data.SqlDbType.Int);
                Cmd.Parameters.Add("@ProductCode", System.Data.SqlDbType.Int);
                Cmd.Parameters.Add("@RegistrationDate", System.Data.SqlDbType.DateTime);
    
                // set values to parameters from textboxes
                Cmd.Parameters["@CustomerID"].Value = Convert.ToInt32(textbox1.Text);
                Cmd.Parameters["@ProductCode"].Value = Convert.ToInt32(textbox2.Text);
                Cmd.Parameters["@RegistrationDate"].Value = DateTime.Now;
    
                // open sql connection
                Con.Open();
    
                // execute the query and return number of rows affected, should be one
                int RowsAffected = Cmd.ExecuteNonQuery();
    
                // close connection when done
                Con.Close();


    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com

    Tuesday, September 27, 2016 4:39 PM
  • 1st way. Mostly because it is immune to SQL Injections:
    xkcd: Exploits of a Mom

    Building any DML query per String Connaction is just asking for trouble. Outside of very early learning you should not do it anymore.

    You can add extra abstaction layers like stored procedures or views to the mix. But SQL Parameters is realy the bigger thing.


    Remember to mark helpfull answers as helpfull and close threads by marking answers.

    • Proposed as answer by Konrad Neitzel Tuesday, September 27, 2016 10:27 PM
    • Marked as answer by Sabah ShariqMVP Thursday, October 6, 2016 10:48 AM
    Tuesday, September 27, 2016 4:51 PM
  • Hello,

    See the following for a working example. A part of the class for doing the insert where it returns the new record's primary key.

    namespace WindowsFormsApplication1
    {
        using System;
        using System.Data;
        using System.Data.SqlClient;
    
        public class DataOperations
        {
            private string InsertStatement = "INSERT INTO [Customer] (CompanyName,ContactName,ContactTitle) VALUES (@CompanyName,@ContactName,@ContactTitle); SELECT CAST(scope_identity() AS int);";
            /// <summary>
            /// Called from AddCustomers to add a single new record
            /// </summary>
            /// <param name="CompanyName"></param>
            /// <param name="ContactName"></param>
            /// <param name="ContactTitle"></param>
            /// <param name="NewIdentifier"></param>
            /// <returns></returns>
            /// <remarks></remarks>
            public bool AddNewCustomer(string CompanyName, string ContactName, string ContactTitle, ref int NewIdentifier)
            {
                using (SqlConnection cn = new SqlConnection { ConnectionString = Properties.Settings.Default.ConnectionString })
                {
                    using (SqlCommand cmd = new SqlCommand { Connection = cn })
                    {
                        cmd.CommandText = InsertStatement;
                        cmd.Parameters.AddWithValue("@CompanyName", CompanyName);
                        cmd.Parameters.AddWithValue("@ContactName", ContactTitle);
                        cmd.Parameters.AddWithValue("@ContactTitle", ContactTitle);
                        cn.Open();
                        try
                        {
                            NewIdentifier = Convert.ToInt32(cmd.ExecuteScalar());
                            return true;
                        }
                        catch (Exception)
                        {
                            return false;
                        }
                    }
                }
            }
        }
    }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Proposed as answer by Konrad Neitzel Tuesday, September 27, 2016 10:28 PM
    • Marked as answer by Sabah ShariqMVP Thursday, October 6, 2016 10:48 AM
    Tuesday, September 27, 2016 4:58 PM
  • >>what is the best way to use INSERT INTO in C# and why?

    1. way for sure. You should always use parameters when executing parameterized queries in order to avoid SQL injections. Please refer to the following threads for more information about this:

    http://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements
    http://stackoverflow.com/questions/4892166/how-does-sqlparameter-prevent-sql-injection
    http://stackoverflow.com/questions/3216233/what-is-passing-parameters-to-sql-and-why-do-i-need-it

    Hope that helps.

    Please remember to close your threads by marking helpful posts as answer and then start a new thread if you have a new question. Please don't ask several questions in the same thread.

    • Proposed as answer by Konrad Neitzel Tuesday, September 27, 2016 10:28 PM
    • Marked as answer by Sabah ShariqMVP Thursday, October 6, 2016 10:48 AM
    Tuesday, September 27, 2016 8:28 PM
  • Hi Takeshikitano,

    If your issue is solved please Mark as answer or Vote as helpful post to the appropriate answer so that it will help members if they faces similar issue.

    Thanks,

    Sabah Shariq

    Wednesday, October 5, 2016 10:53 AM