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 ANSI_NULLS ON 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: private static SqlConnection sqlConnection = new SqlConnection("user id=sa;" + "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: List<KeyValuePair> dbRecord = new List<KeyValuePair>(); 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!

Komentarai

Ernestai, kaip butu galima susisiekti su tavimi?

Sveiki :)

Turbūt būtų geriausia el. paštu: ernestaslt eta gmail taškas com :)
Iki susirašymo ;)

Komentuoti