how to stored procedure sql server 2008 and c#

Question

Kunal Vaishya · Answer

Dear Create the StoreProcedures in Sql Server and Create SqlDataCommand and assign that Procedures into that after you can Execute the Command or Fill the Command In Sql -------------------------- Create Procedures PrcEmployeeInsert As Insert Statement ------------------------------- In C# System.Data.SqlClient.SqlCommand ObjCmd = new System.Data.SqlClient.SqlCommand(); ObjCmd.CommandType = System.Data.CommandType.StoredProcedure; ObjCmd.CommandText = PrcEmployeeInsert; ObjCmd.Connection.ConnectionString = ConnectionString; ObjCmd.ExecuteNonQuery();

Shivanand Arur · Answer

A stored Procedure is a Group of SQL Statements. It basically contains parameters and your logic in SQL Query format. You can create a Stored Procedure to do various things like Insertion Deletion Updation which are the 3 Basic funda's of SQL. Now lets assume a situation where you want to insert some data regarding a User in your database. So for that case I'll write a Stored Procedure to Insert the data as shown below. INSERTING DATA USING STORED PROCEDURE Stored Procedure to Insert Users Information - Create Procedure Insert_Users ( @sUsr_Firstname varchar(50) // Parameter to accept First Name @sUsr_Middlename varchar(50) // Parameter to accept Middle Name @sUsr_Lastname varchar(50) // Parameter to accept Last Name @sUsr_Password varchar(50) // Parameter to accept Password ) AS BEGIN INSERT INTO TABLENAME(FirstName MiddleName LastName Password) VALUES@sUsr_Firstname @sUsr_Middlename @sUsr_Lastname @sUsr_Password) END C# Code to Interact with the Stored Procedure using System; using System.Data; using System.Data.SqlClient; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace InsertUsers { Class Cls_InsertUserInfo { try { SqlConnection con = new SqlConnection(Connection String); // Create Connection Object. SqlCommand cmd = new SqlCommand(Insert_Users con);// Create Command Object and pass St Proc Name and connection Object con.Open(); // Open the Connection cmd.CommandType = CommandType.StoredProcedure; // Set the Command Type cmd.Parameters.Add(@sUsr_Firstname SqlDbType.VarChar 50).Value = txtFName.Text; // Accept Parameter cmd.Parameters.Add(@sUsr_Middlename SqlDbType.VarChar 50).Value = txtMName.Text; cmd.Parameters.Add(@sUsr_Lastname SqlDbType.VarChar 50).Value = txtLName.Text; cmd.Parameters.Add(@sUsr_Password SqlDbType.VarChar 50).Value = txtPassword.Text; cmd.ExecuteNonQuery(); // Execute the St Proc Query con.Close(); // Close the Connection. } catch (Exception ex) { throw; } } } SELECTING / GETTING DATA USING STORED PROCEDURE FROM DATABASE Create Procedure Select_Users ( @sUsr_Firstname varchar(50) // Parameter to accept First Name ) AS BEGIN Select from TableName where FirstName = '@sUsr_Firstname' END C# Code to Interact with the Stored Procedure using System; using System.Data; using System.Data.SqlClient; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; namespace InsertUsers { Class Cls_GetUserInfo { try { SqlConnection con = new SqlConnection(Connection String); // Create Connection Object. SqlCommand cmd = new SqlCommand(Select_Users con); // Create Command Object and pass St Proc Name and connection Object SqlDataReader dr = null; // Create Datareader to read data. con.Open(); // Open the Connection cmd.CommandType = CommandType.StoredProcedure; // Set the Command Type cmd.Parameters.Add(@sUsr_Firstname SqlDbType.VarChar 50).Value = txtFName.Text; // Accept Parameter dr = cmd.ExecuteReader(); // Execute the St Proc Query if (dr.HasRows) // Check if Dr has got any data. { while (dr.Read()) //Read data from Reader Obj if it has any data. { Response.Write(dr[0].ToString()); } } con.Close(); // Close the Connection. } catch (Exception ex) { throw; } } } These were some of the examples which will help you to understand how to use STored Procedures in C# Website or an Application. Please feel free to ask questions if you have any Doubt. PLEASE MARK THIS ANSWER AS CORRECT IF IT HELPED YOU!!!

amirh karimi · Answer

tanx

amirh karimi · Answer

tanx