Howto. Execute MS SQL Stored Procedure With C#
At the moment I’m coding a small project for my university. I have to create a simple system on C# and Microsoft SQL Server 2005. So, I need a way (and code) to execute MS SQL Server’s Stored Procedures from my C# program. How to do that?
Suppose we have a Stored procedure:
set QUOTED_IDENTIFIER ON
GO
– =============================================
– Author: Ernestas Kardzys
– Create date: 2010.03.16
– Description: Inserts new client into Client table
– =============================================
ALTER PROCEDURE [dbo].[client_insert]
@Name nvarchar(50) = null,
@Address nvarchar(50) = null,
@PhoneNumber nvarchar(50) = null,
@SecurityAccountID int = 0
AS
BEGIN
– SET NOCOUNT ON added to prevent extra result sets from
– interfering with SELECT statements.
SET NOCOUNT ON;
— Insert statements for procedure here
INSERT INTO Client (Name, Address, PhoneNumber, SecurityAccountID) VALUES (@Name, @Address, @PhoneNumber, @SecurityAccountID);
END
Then we need a C# method:
“password=sa;server=ERNESTAS-PC\\SQLEXPRESS;” +
“Trusted_Connection=yes;” +
“database=Investments; ” +
“connection timeout=30″);
public void Connect()
{
sqlConnection.Open();
}
///
/// Description: Executes a Stored procedure
///
/// First parameter: The name of Stored procedure
/// The second parameter: The parameters pf Stored Procedure
/// Throws: SQLException
/// Returns: DataTable with results
public static DataTable ExecuteProcedure(string query, List<KeyValuePair> parameters)
{
SqlDataAdapter dataAdapter = new SqlDataAdapter();
DataTable dataTable = new DataTable();
SqlCommand sqlCommand = new SqlCommand(query, sqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;
dataAdapter.SelectCommand = sqlCommand;
if (parameters != null)
{
foreach (KeyValuePair cmd in parameters)
{
sqlCommand.Parameters.Add(new SqlParameter(cmd.Key, cmd.Value));
}
}
dataAdapter.Fill(dataTable);
return dataTable;
}
And use it:
dbRecord.Add(new KeyValuePair(“@SecurityAccountID”, comboBoxSecurityAccount.SelectedIndex.ToString()));
dbRecord.Add(new KeyValuePair(“@Address”, textBoxAddress.Text.Trim()));
dbRecord.Add(new KeyValuePair(“@Name”, textBoxName.Text.Trim()));
dbRecord.Add(new KeyValuePair(“@PhoneNumber”, textBoxPhoneNumber.Text.Trim()));
SQLClient.ExecuteProcedure(“client_insert”, dbRecord);
Good luck!
Loading ...
