How To Call Stored Procedure In C# That Has Output Parameters

TODO:

Have you ever wanted to call a stored procedure in C# that has output parameters?

SOLUTIONS:

System.Data.Objects.ObjectParameter param1 = new ObjectParameter("paraminsp1", typeof(bool));
System.Data.Objects.ObjectParameter param2 = new ObjectParameter("paraminsp2", 0.000);           //default so EF works

using (MyEntities MyDatabase = new MyEntities())
{
     MyDatabase.sp_name(x, 2, param1, param2);
}

NOTES:

There are no notes on this topic

How To Pass A DataTable To A Stored Procedure Using C# and SQL Server

TODO:

Have you ever wanted to pass a DataTable from your C# application to a Stored Procedure in SQL Server?

SOLUTION:

Step 1:  Create the User-Definied Table Type in SQL Server

CREATE TYPE [dbo].[MyUserDefinedType] AS TABLE(
	[Id] [int] NULL,
	[FieldOne] [int] NULL,
	[FieldTwo] [int] NULL
)

Step 2:  Create the DataTable and call the Stored Procedure

using (DataTable myDataTable = new DataTable())
{
     myDataTable.Columns.Add(“FieldOne”, System.Type.GetType("System.Int32"));
     myDataTable.Columns.Add(“FieldTwo, System.Type.GetType("System.Int32"));
     myDataTable.Columns.Add(“FieldThree, System.Type.GetType("System.Int32"));
                
     DataRow dataRow = null;
                
     for (int i = 0; i < 100; i++)
     {
          dataRow = myDataTable.NewRow();  
          dataRow[“FieldOne] = 1;
          dataRow[“FieldTwo”] = 2;
          dataRow[“FieldThree] = 3;
          myDataTable.Rows.Add(dataRow);           
     }

     using (SqlConnection connection = new SqlConnection(“connectionstring”))
     {
          
          using (SqlCommand command = connection.CreateCommand())
          {
               connection.Open();
               command.CommandType = CommandType.StoredProcedure;
               command.CommandTimeout = 90;
               command.CommandText = “MyStoredProcedureName;
               command.Parameters.AddWithValue(“@IncomingDataTable”, myDataTable);
               command.ExecuteNonQuery();
          }
     }
}

Step 3:  Create the Table

CREATE TABLE MyTable(FieldOne int NULL, FieldTwo NULL, FieldThree NULL)

Step 4:  Create the Stored Procedure

CREATE PROCEDURE [dbo].[MyStoredProcedure]
@MyUserDefinedType MyUserDefinedType READONLY

AS
BEGIN 

    INSERT INTO dbo.MyTable
	([FieldOne], [FieldTwo], [FieldThree])
	
	SELECT [FieldOne], [FieldTwo], [FieldThree] FROM @MyUserDefinedType

END

NOTES:

Be sure that the DataTable columns are added in the SAME order as they are created in the SQL Server User Type.

How To Print Messages In a SQL Server Stored Procedure

TODO:

Have you ever wanted to print output messages in a stored procedure?

SOLUTION:

PRINT 'some string'

PRINT 'some string' + @SomeVariable

NOTES:

Note, if you are concatenating a numeric variable you will need to cast it to Varchar

How To Check @@ERROR to Determine If An Error Occurred In A Stored Procedure

TODO:

Have you ever wanted to check to see if an update or other error occurred in your stored procedure.  To do so check the @@ERROR variable, if it is 0, then no errors occurred.

SOLUTION:

IF @@ERROR = 0
BEGIN
     --return the id of the row inserted
     SELECT @@ROWCOUNT As RowsAffected
END
ELSE
BEGIN
     SELECT -1 As RowsAffected
END

NOTES:

In the example above, I did not care about the error code, which is why i chose to return -1 in the ELSE.

How To Find Text That Exists In A Stored Procedure, Function Or Trigger

TODO:

Have you ever wanted to find text that exists in a stored procedure, function or trigger using T-SQL?

SOLUTION:

SELECT DISTINCT *
FROM sys.sql_modules m 
INNER JOIN sys.objects  o ON m.object_id=o.object_id
WHERE m.definition Like '%enter your search criteria here%'
ORDER BY 2,1

NOTES:

There are no notes on this topic.

How To Call A SQL Server Stored Procedure And Use Output Paramaters In C#

TODO:

Have you ever wanted to call a stored procedure, and use an output parameter in C#?

SOLUTION:

using (SqlConnection myConnection = new SqlConnection(<YOUR CONNECTION STRING>))
{
    string _Number = "D3R42ed"; //test number to lookup
    long _ReturnId = -1;  //test value for the output param

    //do the command stuff now
    using (SqlCommand UserInfoCmd = new SqlCommand())
    {
        UserInfoCmd.CommandType = CommandType.StoredProcedure;
        UserInfoCmd.CommandTimeout = 30;
                       
        UserInfoCmd.CommandText = "usp_myStoredProcedure";  //your stored procedure name here
        UserInfoCmd.Parameters.Add("@Number", System.Data.SqlDbType.VarChar, 25).Value = _Number;  //set the number parameter
        
        SqlParameter outparm = UserInfoCmd.Parameters.Add("@ReturnValue", System.Data.SqlDbType.BigInt, 0); //Add the parameter
        outparm.Direction = ParameterDirection.Output;  //tell it, it is an OUTPUT param
        
        myConnection.Open();  //open the connection
        UserInfoCmd.Connection = myConnection;  //set the connection

        //harvest abstract here
        using (SqlDataReader myReader = UserInfoCmd.ExecuteReader())  //now execute the command
        {
            //see if we can read
            if (myReader.Read())
            {
                string SomeName = myReader.IsDBNull(0) ? "" : myReader.GetSqlString(0).Value.Trim();  //get some test value from the SP select statement

                myReader.Close();  //VERY IMPORTANT, CLOSE BEFORE ACCESSING OUTPUT PARAMS
                _ReturnId = Convert.ToInt64(UserInfoCmd.Parameters["@ReturnValue"].Value);  //now get the output param value            
            }
        }
    }
}

NOTES:

Be sure to close the SqlDataReader before accessing the output parameters!