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

how to stored procedure sql server 2008 and c#

Answers (4)