using System; using System.Data.SqlClient; using System.Data.OleDb; using System.Data; class Class1 { static void Main(string[] args) { SqlConnection cnnUserMan; SqlCommand cmmUser; object objNumUsers; // Instantiate and open the connection cnnUserMan = new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"); cnnUserMan.Open(); // Instantiate and initialize command cmmUser = new SqlCommand("SimpleStoredProcedure", cnnUserMan); cmmUser.CommandType = CommandType.StoredProcedure; objNumUsers = cmmUser.ExecuteScalar(); Console.WriteLine(objNumUsers.ToString()); } }
Author: coder
Get Return from SQL Server store procedure
using System; using System.Data; using System.Data.SqlClient; class Test { public static void Main() { SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"); mySqlConnection.Open(); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "EXECUTE @MyProductID = AddProduct3 @MyProductName, " + "@MySupplierID, @MyCategoryID, @MyQuantityPerUnit, " + "@MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, " + "@MyReorderLevel, @MyDiscontinued"; mySqlCommand.Parameters.Add("@MyProductID", SqlDbType.Int); mySqlCommand.Parameters["@MyProductID"].Direction = ParameterDirection.Output; mySqlCommand.Parameters.Add("@MyProductName", SqlDbType.NVarChar, 40).Value = "Widget"; mySqlCommand.Parameters.Add("@MySupplierID", SqlDbType.Int).Value = 1; mySqlCommand.Parameters.Add("@MyCategoryID", SqlDbType.Int).Value = 1; mySqlCommand.Parameters.Add("@MyQuantityPerUnit", SqlDbType.NVarChar, 20).Value = "1 per box"; mySqlCommand.Parameters.Add("@MyUnitPrice", SqlDbType.Money).Value = 5.99; mySqlCommand.Parameters.Add("@MyUnitsInStock", SqlDbType.SmallInt).Value = 10; mySqlCommand.Parameters.Add("@MyUnitsOnOrder", SqlDbType.SmallInt).Value = 5; mySqlCommand.Parameters.Add("@MyReorderLevel", SqlDbType.SmallInt).Value = 5; mySqlCommand.Parameters.Add("@MyDiscontinued", SqlDbType.Bit).Value = 1; SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); while (mySqlDataReader.Read()) { Console.WriteLine("mySqlDataReader[" ProductName"] = " + mySqlDataReader["ProductName"]); Console.WriteLine("mySqlDataReader[" UnitPrice"] = " + mySqlDataReader["UnitPrice"]); } mySqlDataReader.Close(); Console.WriteLine("New ProductID = " + mySqlCommand.Parameters["@MyProductID"].Value); mySqlConnection.Close(); } }
Call the SQL Server AddProduct() store procedure
using System; using System.Data; using System.Data.SqlClient; class Test { public static void Main() { SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"); mySqlConnection.Open(); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "EXECUTE AddProduct @MyProductID OUTPUT, @MyProductName, " + "@MySupplierID, @MyCategoryID, @MyQuantityPerUnit, " + "@MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, " + "@MyReorderLevel, @MyDiscontinued"; mySqlCommand.Parameters.Add("@MyProductID", SqlDbType.Int); mySqlCommand.Parameters["@MyProductID"].Direction = ParameterDirection.Output; mySqlCommand.Parameters.Add( "@MyProductName", SqlDbType.NVarChar, 40).Value = "Widget"; mySqlCommand.Parameters.Add( "@MySupplierID", SqlDbType.Int).Value = 1; mySqlCommand.Parameters.Add( "@MyCategoryID", SqlDbType.Int).Value = 1; mySqlCommand.Parameters.Add( "@MyQuantityPerUnit", SqlDbType.NVarChar, 20).Value = "1 per box"; mySqlCommand.Parameters.Add( "@MyUnitPrice", SqlDbType.Money).Value = 5.99; mySqlCommand.Parameters.Add( "@MyUnitsInStock", SqlDbType.SmallInt).Value = 10; mySqlCommand.Parameters.Add( "@MyUnitsOnOrder", SqlDbType.SmallInt).Value = 5; mySqlCommand.Parameters.Add( "@MyReorderLevel", SqlDbType.SmallInt).Value = 5; mySqlCommand.Parameters.Add( "@MyDiscontinued", SqlDbType.Bit).Value = 1; mySqlCommand.ExecuteNonQuery(); Console.WriteLine("New ProductID = " + mySqlCommand.Parameters["@MyProductID"].Value); mySqlConnection.Close(); } }
Populate a DataSet object using a store procedure
using System; using System.Data; using System.Data.SqlClient; class PopulateDataSetUsingProcedure { public static void Main() { SqlConnection mySqlConnection =new SqlConnection("server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;"); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "EXECUTE CustOrderHist @CustomerID"; mySqlCommand.Parameters.Add("@CustomerID", SqlDbType.NVarChar, 5).Value = "ALFKI"; SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter(); mySqlDataAdapter.SelectCommand = mySqlCommand; DataSet myDataSet = new DataSet(); mySqlConnection.Open(); Console.WriteLine("Retrieving rows from the CustOrderHist() Procedure"); int numberOfRows = mySqlDataAdapter.Fill(myDataSet, "CustOrderHist"); Console.WriteLine("numberOfRows = " + numberOfRows); mySqlConnection.Close(); DataTable myDataTable = myDataSet.Tables["CustOrderHist"]; foreach (DataRow myDataRow in myDataTable.Rows) { Console.WriteLine("ProductName = " + myDataRow["ProductName"]); Console.WriteLine("Total = " + myDataRow["Total"]); } } }
Illustrates simple stored procedures with unnamed parameters in the query
using System; using System.Data; using System.Data.OleDb; public class Prepare { public static void Main () { String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.Employee.mdb"; OleDbConnection con = new OleDbConnection(connect); con.Open(); Console.WriteLine("Made the connection to the database"); OleDbCommand cmd = con.CreateCommand(); cmd.CommandText ="SELECT First_name FROM Employee WHERE ID = ?"; OleDbParameter param = new OleDbParameter(); cmd.Parameters.Add(param); param.Value = "01"; OleDbDataReader reader = cmd.ExecuteReader(); Console.WriteLine("Using a stored procedure"); while(reader.Read()) Console.WriteLine("{0}", reader.GetString(0)); reader.Close(); param.Value = "01"; reader = cmd.ExecuteReader(); Console.WriteLine("Using a stored procedure"); while(reader.Read()) Console.WriteLine("{0}", reader.GetString(0)); reader.Close(); con.Close(); } }
Call the SQL Server AddProduct() stored procedure with SqlCommand
using System; using System.Data; using System.Data.SqlClient; class ExecuteAddProduct { public static void Main() { SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa"); mySqlConnection.Open(); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "EXECUTE AddProduct @MyProductID OUTPUT, @MyProductName, " + "@MySupplierID, @MyCategoryID, @MyQuantityPerUnit, " + "@MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, " + "@MyReorderLevel, @MyDiscontinued"; mySqlCommand.Parameters.Add("@MyProductID", SqlDbType.Int); mySqlCommand.Parameters["@MyProductID"].Direction = ParameterDirection.Output; mySqlCommand.Parameters.Add("@MyProductName", SqlDbType.NVarChar, 40).Value = "Widget"; mySqlCommand.Parameters.Add("@MySupplierID", SqlDbType.Int).Value = 1; mySqlCommand.Parameters.Add("@MyCategoryID", SqlDbType.Int).Value = 1; mySqlCommand.Parameters.Add("@MyQuantityPerUnit", SqlDbType.NVarChar, 20).Value = "1 per box"; mySqlCommand.Parameters.Add("@MyUnitPrice", SqlDbType.Money).Value = 5.99; mySqlCommand.Parameters.Add("@MyUnitsInStock", SqlDbType.SmallInt).Value = 10; mySqlCommand.Parameters.Add("@MyUnitsOnOrder", SqlDbType.SmallInt).Value = 5; mySqlCommand.Parameters.Add("@MyReorderLevel", SqlDbType.SmallInt).Value = 5; mySqlCommand.Parameters.Add("@MyDiscontinued", SqlDbType.Bit).Value = 1; mySqlCommand.ExecuteNonQuery(); Console.WriteLine("New ProductID = " + mySqlCommand.Parameters["@MyProductID"].Value); mySqlConnection.Close(); } }
Add parameters to SqlCommand to call stored procedure
using System; using System.Data; using System.Data.SqlClient; class ExecuteAddProduct3 { public static void Main() { SqlConnection mySqlConnection = new SqlConnection("server=localhost;database=Northwind;uid=sa;pwd=sa"); mySqlConnection.Open(); SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); mySqlCommand.CommandText = "EXECUTE @MyProductID = AddProduct @MyProductName, " + "@MySupplierID, @MyCategoryID, @MyQuantityPerUnit, " + "@MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, " + "@MyReorderLevel, @MyDiscontinued"; mySqlCommand.Parameters.Add("@MyProductID", SqlDbType.Int); mySqlCommand.Parameters["@MyProductID"].Direction = ParameterDirection.Output; mySqlCommand.Parameters.Add("@MyProductName", SqlDbType.NVarChar, 40).Value = "Widget"; mySqlCommand.Parameters.Add("@MySupplierID", SqlDbType.Int).Value = 1; mySqlCommand.Parameters.Add("@MyCategoryID", SqlDbType.Int).Value = 1; mySqlCommand.Parameters.Add("@MyQuantityPerUnit", SqlDbType.NVarChar, 20).Value = "1 per box"; mySqlCommand.Parameters.Add("@MyUnitPrice", SqlDbType.Money).Value = 5.99; mySqlCommand.Parameters.Add("@MyUnitsInStock", SqlDbType.SmallInt).Value = 10; mySqlCommand.Parameters.Add("@MyUnitsOnOrder", SqlDbType.SmallInt).Value = 5; mySqlCommand.Parameters.Add("@MyReorderLevel", SqlDbType.SmallInt).Value = 5; mySqlCommand.Parameters.Add("@MyDiscontinued", SqlDbType.Bit).Value = 1; SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(); while (mySqlDataReader.Read()) { Console.WriteLine("mySqlDataReader[" ProductName"] = " + mySqlDataReader["ProductName"]); Console.WriteLine("mySqlDataReader[" UnitPrice"] = " + mySqlDataReader["UnitPrice"]); } mySqlDataReader.Close(); Console.WriteLine("New ProductID = " + mySqlCommand.Parameters["@MyProductID"].Value); mySqlConnection.Close(); } }