locked
call stored procedure with parameter to insert entered data RRS feed

  • Question

  • Hi All.

    On online I found information related to my question. But I cannot understand what is dc in that line:

    using (SqlConnection con = new SqlConnection(dc.Con)) {

    Does that insert data to the table according specified stored procedure? Is that correct?

    private void button1_Click(object sender, EventArgs e) {
      using (SqlConnection con = new SqlConnection(dc.Con)) {
        using (SqlCommand cmd = new SqlCommand("sp_Add_contact", con)) {
          cmd.CommandType = CommandType.StoredProcedure;
    
          cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text;
          cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = txtLastName.Text;
    
          con.Open();
          cmd.ExecuteNonQuery();
        }
      }
    }

    Thanks.



    • Edited by zleug Thursday, March 26, 2020 6:40 PM
    Thursday, March 26, 2020 6:39 PM

Answers

  • Hi Zleug, As Naomi N answered you dc.con refers to has some class in that project to retrieving a connection string ! for better understanding of SqlConnection  is class and has many constructor with in your example it takes only one parameter of string so you can put your connection string without dc.con

     public SqlConnection (string connectionString);
    Example
     using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks;
                Integrated Security=SSPI;"))
            {
                
    
    //Some code goes here
    
    
            }



    Please remember to mark the replies as answers if they helped you :) ~


    • Edited by Rebin Qadir Thursday, March 26, 2020 7:38 PM correction text
    • Marked as answer by zleug Thursday, March 26, 2020 9:38 PM
    Thursday, March 26, 2020 7:37 PM
  • This is how it is done in a form

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp2
    {
        public partial class Form1 : Form
        {
            private string ConnectionString =
                "Data Source=.\\SQLEXPRESS;" +
                "Initial Catalog=NorthWindAzure;" +
                "Integrated Security=True";
    
            public Form1()
            {
                InitializeComponent();
            }
            private void button1_Click(object sender, EventArgs e)
            {
                if (!string.IsNullOrWhiteSpace(firstNameTextBox.Text) && !string.IsNullOrWhiteSpace(lastNameTextBox.Text))
                {
                    using (var cn = new SqlConnection(ConnectionString))
                    {
                        using (var cmd = new SqlCommand() { Connection = cn, CommandType = CommandType.StoredProcedure })
                        {
                            cmd.CommandText = "sp_Add_contact";
                            cn.Open();
                            cmd.Parameters.AddWithValue("@Firstname", firstNameTextBox.Text);
                            cmd.Parameters.AddWithValue("@LastName", lastNameTextBox.Text);
    
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by zleug Thursday, March 26, 2020 9:37 PM
    Thursday, March 26, 2020 8:39 PM

All replies

  • dc is some other class here (May be DatabaseContext where instance is called dc) which has Con property which holds the connection string. For your tests if you don't have other classes holding the connection string you may just put it directly into the 

    using (SqlConnection con = new SqlConnection(dc.Con)) {

    line. E.g. for now just hardcode this string and later find out which class you're using for your database management.

    One more thing about that code - add the size for each varchar parameter as the 3rd parameter in the cmd.Parameters.Add 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi N Thursday, March 26, 2020 6:48 PM
    Thursday, March 26, 2020 6:46 PM
  • Hi Naomi N. Thanks for reply.

    I don't use EF. In my project I'm using DataSet with TableAdapters. To insert data from Form to SQL Server database I create sp_InsertEmployee and EmployeTableAdapter. The EmployeeTableAdapter includes sp_SelectEmployee and sp_InsertEmployee with multiple parameters(FirstName, LastName, EmailAddress, etc). The EmployeeTableAdapter is part of AdminDataSet. In that case how to call stored procedure to insert data to database?

    Thanks


    • Edited by zleug Thursday, March 26, 2020 7:23 PM
    Thursday, March 26, 2020 7:14 PM
  • I don't really use TableAdapters so you may need to do a bit of your own research (should be easy enough).

    Don't use sp_ for your stored procedure prefix. It is reserved for MS procedures, so anything else even spProcedureName, should be Ok.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, March 26, 2020 7:19 PM
  • If you look at SqlConnection constructor you would see this is a string being passed in.

    Here is how I would recommend doing this, in a class.

    public class DataOperations
    {
        private static string ConnectionString =
            "Data Source=.\\SQLEXPRESS;" + 
            "Initial Catalog=NorthWindAzure;" + 
            "Integrated Security=True";
    
        /// <summary>
        /// Insert record via stored procedures
        /// </summary>
        /// <param name="firstname"></param>
        /// <param name="lastName"></param>
        /// <returns>rows affected</returns>
        public static int InsertNewRecordDemo(string firstname, string lastName)
        {
            using (var cn = new SqlConnection(ConnectionString))
            {
                using (var cmd = new SqlCommand() { Connection = cn, CommandType =  CommandType.StoredProcedure})
                {
                    cmd.CommandText = "sp_Add_contact";
                    cn.Open();
                    cmd.Parameters.AddWithValue("@Firstname", firstname);
                    cmd.Parameters.AddWithValue("@LastName", lastName);
                    return cmd.ExecuteNonQuery(); 
                }
            }
        }
    }

    usage

    private void button1_Click(object sender, EventArgs e)
    {
        if (!string.IsNullOrWhiteSpace(firstNameTextBox.Text) && !string.IsNullOrWhiteSpace(lastNameTextBox.Text))
        {
            var result = DataOperations.InsertNewRecordDemo(firstNameTextBox.Text, lastNameTextBox.Text);
        }
    }


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, March 26, 2020 7:29 PM
  • Hi Naomi N. Thanks for reply.

    I don't use EF. In my project I'm using DataSet with TableAdapters. To insert data from Form to SQL Server database I create sp_InsertEmployee and EmployeTableAdapter. The EmployeeTableAdapter includes sp_SelectEmployee and sp_InsertEmployee with multiple parameters(FirstName, LastName, EmailAddress, etc). The EmployeeTableAdapter is part of AdminDataSet. In that case how to call stored procedure to insert data to database?

    Thanks


    With no disrespect, stop using TableAdapter solutions, people are attracted to them because they seem easy which for very small task they are, to get into more than simple one must fully understand the disadvantables more so than advantages.

    But if you want to go down this path anyways see the following

    https://docs.microsoft.com/en-us/aspnet/web-forms/overview/data-access/advanced-data-access-scenarios/using-existing-stored-procedures-for-the-typed-dataset-s-tableadapters-cs#step-2-configuring-the-tableadapter-to-use-an-existing-stored-procedure


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Proposed as answer by Naomi N Thursday, March 26, 2020 7:48 PM
    Thursday, March 26, 2020 7:33 PM
  • Hi Zleug, As Naomi N answered you dc.con refers to has some class in that project to retrieving a connection string ! for better understanding of SqlConnection  is class and has many constructor with in your example it takes only one parameter of string so you can put your connection string without dc.con

     public SqlConnection (string connectionString);
    Example
     using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=AdventureWorks;
                Integrated Security=SSPI;"))
            {
                
    
    //Some code goes here
    
    
            }



    Please remember to mark the replies as answers if they helped you :) ~


    • Edited by Rebin Qadir Thursday, March 26, 2020 7:38 PM correction text
    • Marked as answer by zleug Thursday, March 26, 2020 9:38 PM
    Thursday, March 26, 2020 7:37 PM
  • Hi Karen Payne

    In this line:

    cmd.Parameters.AddWithValue("@Firstname", firstname);

    How to pass data from the Form? For instance,

    cmd.Parameters.AddWithValue("@Firstname", firstname).Value = FirstNameTextBox.Text;

    When I tried the FirstNameTextBox dislike with error red wave:
    An object reference is required for the non-static field, method or property

    Thanks.


    • Edited by zleug Thursday, March 26, 2020 8:05 PM
    Thursday, March 26, 2020 8:00 PM
  • Thanks a lot everyone.

    The working code in my case is:

            private void SaveButton_Click(object sender, RoutedEventArgs e)
            {
                using (SqlConnection con = new SqlConnection("Data Source=local;Initial Catalog=Mydatabase;Integrated Security=True"))
                {
                    using (SqlCommand cmd = new SqlCommand("sp_InsertEmployee", con))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
    
                        cmd.Parameters.Add("@EmployeeId", SqlDbType.NVarChar).Value = EmployeeIdTextBox.Text;
                        cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = FNameTextBox.Text;
                        cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = LNameTextBox.Text;
                        cmd.Parameters.Add("@EmailAddress", SqlDbType.NVarChar).Value = EmailTextBox.Text;
                        cmd.Parameters.Add("@DepartmentName", SqlDbType.VarChar).Value = DepartmentComboBox.Text;
                        //cmd.Parameters.Add("@UnitName", SqlDbType.VarChar).Value = UnitComboBox.Text;
    
                        con.Open();
                        cmd.ExecuteNonQuery();
                    }
                }
    }


    • Edited by zleug Thursday, March 26, 2020 8:39 PM
    Thursday, March 26, 2020 8:38 PM
  • This is how it is done in a form

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp2
    {
        public partial class Form1 : Form
        {
            private string ConnectionString =
                "Data Source=.\\SQLEXPRESS;" +
                "Initial Catalog=NorthWindAzure;" +
                "Integrated Security=True";
    
            public Form1()
            {
                InitializeComponent();
            }
            private void button1_Click(object sender, EventArgs e)
            {
                if (!string.IsNullOrWhiteSpace(firstNameTextBox.Text) && !string.IsNullOrWhiteSpace(lastNameTextBox.Text))
                {
                    using (var cn = new SqlConnection(ConnectionString))
                    {
                        using (var cmd = new SqlCommand() { Connection = cn, CommandType = CommandType.StoredProcedure })
                        {
                            cmd.CommandText = "sp_Add_contact";
                            cn.Open();
                            cmd.Parameters.AddWithValue("@Firstname", firstNameTextBox.Text);
                            cmd.Parameters.AddWithValue("@LastName", lastNameTextBox.Text);
    
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
            }
        }
    }
    


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    • Marked as answer by zleug Thursday, March 26, 2020 9:37 PM
    Thursday, March 26, 2020 8:39 PM
    • Thanks a lot everyone.

      The working code in my case is:

              private void SaveButton_Click(object sender, RoutedEventArgs e)
              {
                  using (SqlConnection con = new SqlConnection("Data Source=local;Initial Catalog=Mydatabase;Integrated Security=True"))
                  {
                      using (SqlCommand cmd = new SqlCommand("sp_InsertEmployee", con))
                      {
                          cmd.CommandType = CommandType.StoredProcedure;
      
                          cmd.Parameters.Add("@EmployeeId", SqlDbType.NVarChar).Value = EmployeeIdTextBox.Text;
                          cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar).Value = FNameTextBox.Text;
                          cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = LNameTextBox.Text;
                          cmd.Parameters.Add("@EmailAddress", SqlDbType.NVarChar).Value = EmailTextBox.Text;
                          cmd.Parameters.Add("@DepartmentName", SqlDbType.VarChar).Value = DepartmentComboBox.Text;
                          //cmd.Parameters.Add("@UnitName", SqlDbType.VarChar).Value = UnitComboBox.Text;
      
                          con.Open();
                          cmd.ExecuteNonQuery();
                      }
                  }
      }


    • There is no reason to use Parameters.Add except in rare circumstances.
    • Always check the value returned from ExecureNonQuery which means you should be returning a value.

    In this case I return the new primary key in this stored procedure

    CREATE PROCEDURE [dbo].[InsertCustomer]  
        @CompanyName NVARCHAR(200), 
        @ContactName NVARCHAR(200), 
        @ContactTypeIdentifier INT, 
        @Identity INT OUT 
    AS 
    BEGIN 
        -- SET NOCOUNT ON added to prevent extra result sets from 
        -- interfering with SELECT statements. 
        SET NOCOUNT ON; 
     
    INSERT INTO Customer(CompanyName,ContactName,ContactTypeIdentifier)  
        VALUES(@CompanyName,@ContactName,@ContactTypeIdentifier) 
     
    SET @Identity = SCOPE_IDENTITY() 
     
    END 
    Code is here to get the new primary key.


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, March 26, 2020 8:44 PM
  • Hi Karen,

    I actually disagree with the statement that anyone should use AddWithValue method. I strongly believe that we should always use more verbatim Add method and put exact SqlDbType and the size matching DB column's size for the character parameters.

    I wanted to backup my understanding (as I know we discussed it before in SQL Server related forums), but surprisingly I was not able to quickly find a link supporting that.

    I found https://stackoverflow.com/questions/21110001/sqlcommand-parameters-add-vs-addwithvalue

    and this one which does support my statement 

    https://forums.asp.net/t/1200255.aspx?Parameters+AddWithValue+vs+Parameters+Add+

    but I wanted something more clear and preferably coming from MS. Perhaps I just didn't search hard enough.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, March 26, 2020 8:57 PM
  • Hi Karen,

    I actually disagree with the statement that anyone should use AddWithValue method. I strongly believe that we should always use more verbatim Add method and put exact SqlDbType and the size matching DB column's size for the character parameters.

    I wanted to backup my understanding (as I know we discussed it before in SQL Server related forums), but surprisingly I was not able to quickly find a link supporting that.

    I found https://stackoverflow.com/questions/21110001/sqlcommand-parameters-add-vs-addwithvalue

    and this one which does support my statement 

    https://forums.asp.net/t/1200255.aspx?Parameters+AddWithValue+vs+Parameters+Add+

    but I wanted something more clear and preferably coming from MS. Perhaps I just didn't search hard enough.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Hi Naomi, the only time since .NET came out that I've used Add is when declaring parameters before performing an iterative operation e.g. reading a delimited text file, declare the params first then in the loop set the values. Otherwise one doing a single insert, update or for a where in a select. Never had an issues and I always write unit test to ensure code works as expected. 

    Not saying I know more than you or others in the links, just what works for me.

    If you look at the last link I provided note in this case I did use Add over AddWithValue as this was to get developers to consider there are options, not use AddWithValue as its common for a new developer to create params in a loop and get an error the param is already declared.

    Now if I was always using what is recommended on the web for common operations I would be in trouble often e.g. the answers I see for Excel automation but that is off topic.

    Any ways thanks for bringing this up :-)


    Please remember to mark the replies as answers if they help and unmarked 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.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Thursday, March 26, 2020 9:19 PM
  • This is answer to Karen and to the OP as well. I just remembered to search by Dan Guzman's name and this way I found the thread I was probably thinking of. Also, David who is the first one who answered this question is a great authority and a very knowledgeable person.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e6dd040f-6bf9-4ecb-aeb1-034608472234/sql-parameters-add-vs-addwithvalue?forum=sqldataaccess

    So, the conclusion is to use Parameters.Add with the type matching database column type and the size matching it as well.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Thursday, March 26, 2020 10:45 PM