none
DataAdapter SQL InsertCommand using a stored procedure? RRS feed

  • Question

  • Hi,

    I'm looking for an example or tutorial on how to code an SQL DataAdapter in C# to pass parameters to a stored procedure that will insert a record into a table in the database.

    I have the stored procedure and it works when I run it directly from SQL Management Studio.  But my attempts to code it from an app aren't working.  Not getting any error messages but it just doesn't insert anything so I must be doing something wrong in my code.

    I have been trying to use the update method of the DataAdapter, but that takes a table as a parameter and just trying to take input from text boxes on a form.  Or do I need to insert the data from the text boxes into a local DataTable just to make this work?

    I can post relevant code examples of what I've tried if you'd like but I'm thinking there are examples out there, I just haven't found them yet.

    Any thoughts?

    Thanks in advance!

    Saturday, April 20, 2019 9:37 PM

Answers

  • Hello,

    Usually for a INSERT (such as shown below) in a stored procedure is done using a connection and command object for single inserts not a DataAdapter and returns the new primary key. There is zero reasons to use a DataTable.

    So to summarize, for adding one record, use a connection and command object and set the command type to stored procedure.

    CREATE PROCEDURE [dbo].[InsertCustomer] 
    	@CompanyName nvarchar(200),
    	@ContactName nvarchar(200),
    	@ContactTitle nvarchar(200),
    	@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,ContactTitle) 
    	VALUES(@CompanyName,@ContactName,@ContactTitle)
    
    SET @Identity = SCOPE_IDENTITY()
    
    END

    Code sample

    public int AddCustomer(string CompanyName, string ContactName, string ContactTitle)
    {
    	try
    	{
    		using (SqlConnection cn = new SqlConnection { ConnectionString = this.ConnectionString })
    		{
    			using (SqlCommand cmd = new SqlCommand { Connection = cn, CommandType = CommandType.StoredProcedure, CommandText = "dbo.InsertCustomer" })
    			{
    				cmd.Parameters.Add(new SqlParameter { ParameterName = "@CompanyName", SqlDbType = SqlDbType.NVarChar });
    				cmd.Parameters.Add(new SqlParameter { ParameterName = "@ContactName", SqlDbType = SqlDbType.NVarChar });
    				cmd.Parameters.Add(new SqlParameter { ParameterName = "@ContactTitle", SqlDbType = SqlDbType.NVarChar });
    				cmd.Parameters.Add(new SqlParameter { ParameterName = "@Identity", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output });
    
    				cmd.Parameters["@CompanyName"].Value = CompanyName;
    				cmd.Parameters["@ContactName"].Value = ContactName;
    				cmd.Parameters["@ContactTitle"].Value = ContactTitle;
    				cn.Open();
    				var affected = cmd.ExecuteScalar();
    
    				this.Exception = null;
    				return Convert.ToInt32(cmd.Parameters["@Identity"].Value);
    			}
    		}
    	}
    	catch (Exception ex)
    	{
    		this.Exception = ex;
    		return -1;
    	}
    }


    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

    Sunday, April 21, 2019 1:50 AM
    Moderator
  • Hi Karen,

    So it is Ok to mix methods of connecting back to the database?  The rest of my program uses the DataAdapter disconnected model.

    Thanks!

    Absolutely, a DataAdapter is a wrapper over object I've shown, connection and command and my code. Both are indeed disconnected.



    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

    Monday, April 22, 2019 10:48 PM
    Moderator

All replies

  • Hello,

    Usually for a INSERT (such as shown below) in a stored procedure is done using a connection and command object for single inserts not a DataAdapter and returns the new primary key. There is zero reasons to use a DataTable.

    So to summarize, for adding one record, use a connection and command object and set the command type to stored procedure.

    CREATE PROCEDURE [dbo].[InsertCustomer] 
    	@CompanyName nvarchar(200),
    	@ContactName nvarchar(200),
    	@ContactTitle nvarchar(200),
    	@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,ContactTitle) 
    	VALUES(@CompanyName,@ContactName,@ContactTitle)
    
    SET @Identity = SCOPE_IDENTITY()
    
    END

    Code sample

    public int AddCustomer(string CompanyName, string ContactName, string ContactTitle)
    {
    	try
    	{
    		using (SqlConnection cn = new SqlConnection { ConnectionString = this.ConnectionString })
    		{
    			using (SqlCommand cmd = new SqlCommand { Connection = cn, CommandType = CommandType.StoredProcedure, CommandText = "dbo.InsertCustomer" })
    			{
    				cmd.Parameters.Add(new SqlParameter { ParameterName = "@CompanyName", SqlDbType = SqlDbType.NVarChar });
    				cmd.Parameters.Add(new SqlParameter { ParameterName = "@ContactName", SqlDbType = SqlDbType.NVarChar });
    				cmd.Parameters.Add(new SqlParameter { ParameterName = "@ContactTitle", SqlDbType = SqlDbType.NVarChar });
    				cmd.Parameters.Add(new SqlParameter { ParameterName = "@Identity", SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output });
    
    				cmd.Parameters["@CompanyName"].Value = CompanyName;
    				cmd.Parameters["@ContactName"].Value = ContactName;
    				cmd.Parameters["@ContactTitle"].Value = ContactTitle;
    				cn.Open();
    				var affected = cmd.ExecuteScalar();
    
    				this.Exception = null;
    				return Convert.ToInt32(cmd.Parameters["@Identity"].Value);
    			}
    		}
    	}
    	catch (Exception ex)
    	{
    		this.Exception = ex;
    		return -1;
    	}
    }


    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

    Sunday, April 21, 2019 1:50 AM
    Moderator
  • Hi Karen,

    So it is Ok to mix methods of connecting back to the database?  The rest of my program uses the DataAdapter disconnected model.

    Thanks!

    Monday, April 22, 2019 9:14 PM
  • Hi Karen,

    So it is Ok to mix methods of connecting back to the database?  The rest of my program uses the DataAdapter disconnected model.

    Thanks!

    Absolutely, a DataAdapter is a wrapper over object I've shown, connection and command and my code. Both are indeed disconnected.



    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

    Monday, April 22, 2019 10:48 PM
    Moderator