Adodotnet Stored Procedure Mine

************************************************************************* ******************************* In These Examples Student and Grade Tables have The Below Structure (or Close to That) Student(sid,fname,lname,phone) Grade(gid,ggrade,gStudentId) ************************************************************************* *******************************

-------------------------------------------------------------------------------------------------

Stored Procedure Sample:

CREATE PROCEDURE spIsCidRep @courseId nvarchar(50) AS BEGIN Select * From Course where cid = @courseId If @@RowCount = 0 Return 1 Else Return -1 END GO

------------------------------------------------------------------------------------------------Stored Procedure Sample With Output Variable:

CREATE PROCEDURE ShowSuppliers ( @txt varchar(50), @Name varchar (50) output, @Company varchar (50) output, @Country varchar (50) output ) AS BEGIN Select @Name = ContactName, @Company = CompanyName, @Country = Country from Suppliers Where Country like "%" + @txt + "%" END GO

------------------------------------------------------------------------------------------------RowCount Function in Stored Procedure;

Crete Procedure spStudentGrade( @stNum int, @fulname varchar(101) output, @grade int output ) As Begin Select @fulname = s.lname + "," + s.lname, @grade = g.grade from studend s, grade g where s.sid = @stNum and s.sid = g.studentId

if @@rowcount = 0 return -1

else return 1 End Go

------------------------------------------------------------------------------------------------Declare Variables in Stored Procedures:

Create Procedure spAvrage( @a int, @b int ) Ag Beging Declare @avg = (@a + @b)/2 End Go

------------------------------------------------------------------------------------------------Open a connection:

1. SqlConnection sqlConn = new SqlConnection(); sqlConn.ConnectionString = "server=localhost;database=myTestDb;uid=sa;pwd=''"; try { sqlConn.open();

} Catch(SqlException ex) { Console.WriteLine(ex.Message); } finally { sqlConn.close(); }

2.

SqlConnection sqlConn = new SqlConnection("server=localhost;database=myTestDb;uid=sa;pwd=''"); try { sqlConn.open(); } Catch(SqlException ex) { Console.WriteLine(ex.Message); } finally { sqlConn.close(); }

3. connStr = "server=localhost;database=myTestDb;uid=sa;pwd=''"; SqlConnection sqlConn = new SqlConnection(connStr);

try { sqlConn.open(); } Catch(SqlException ex) { Console.WriteLine(ex.Message); } finally { sqlConn.close(); }

------------------------------------------------------------------------------------------------Create a Command Object:

1.

connStr = "server=localhost;database=myTestDb;uid=sa;pwd=''"; SqlConnection sqlConn = new SqlConnection(connStr); try { sqlConn.open(); SqlCommand sqlCmd = new SqlCommand(); sqlCmd.CommandText = "Select * From Student"; sql.Connection = sqlConn; SqlDataReader sqlreader = sqlCmd.ExecuteReader() while(sqlReader.read())

Console.WriteLine(sqlReader["sid"];

} Catch(SqlException ex) { Console.WriteLine(ex.Message); } finally { sqlConn.close(); }

2.

connStr = "server=localhost;database=myTestDb;uid=sa;pwd=''"; SqlConnection sqlConn = new SqlConnection(connStr); try { sqlConn.open(); sqlStr = "Select * From Student"; SqlCommand sqlCmd = new SqlCommand(sqlStr, sqlConn); SqlDataReader sqlreader = sqlCmd.ExecuteReader() while(sqlReader.read()) Console.WriteLine(sqlReader["sid"];

} Catch(SqlException ex) { Console.WriteLine(ex.Message);

} finally { sqlConn.close(); }

------------------------------------------------------------------------------------------------Doing a Select Query:

connStr = "server=localhost;database=myTestDb;uid=sa;pwd=''"; SqlConnection sqlConn = new SqlConnection(connStr); try { sqlConn.open(); sqlStr = "Select * From Student"; SqlCommand sqlCmd = new SqlCommand(sqlStr, sqlConn); SqlDataReader sqlreader = sqlCmd.ExecuteReader() while(sqlReader.read()) Console.WriteLine(sqlReader["sid"];

} Catch(SqlException ex) { Console.WriteLine(ex.Message); } finally { sqlConn.close();

}

------------------------------------------------------------------------------------------------Doing a Delete Query:

connStr = "server=localhost;database=myTestDb;uid=sa;pwd=''"; SqlConnection sqlConn = new SqlConnection(connStr); try { sqlConn.open(); sqlStr = "delete From Student where sid=2453202"; SqlCommand sqlCmd = new SqlCommand(sqlStr, sqlConn); sqlCmd.ExecuteNonQuery()

} Catch(SqlException ex) { Console.WriteLine(ex.Message); } finally { sqlConn.close(); }

------------------------------------------------------------------------------------------------Doing an Insert Query:

connStr = "server=localhost;database=myTestDb;uid=sa;pwd=''"; SqlConnection sqlConn = new SqlConnection(connStr); try { sqlConn.open() sqlStr = "Insert Into Student values(2453202, 'hooman', 'shamsborhan', '040094506')"; SqlCommand sqlCmd = new SqlCommand(sqlStr, sqlConn); sqlCmd.ExecuteNonQuery()

} Catch(SqlException ex) { Console.WriteLine(ex.Message); } finally { sqlConn.close(); }

------------------------------------------------------------------------------------------------Doing an Update Query:

connStr = "server=localhost;database=myTestDb;uid=sa;pwd=''"; SqlConnection sqlConn = new SqlConnection(connStr); try { sqlConn.open() sqlStr = "update student set sPhone = '0400945506' where sid=2453202";

SqlCommand sqlCmd = new SqlCommand(sqlStr, sqlConn); sqlCmd.ExecuteNonQuery()

} Catch(SqlException ex) { Console.WriteLine(ex.Message); } finally { sqlConn.close(); }

------------------------------------------------------------------------------------------------Doing a Select Query With Aggregate Functions:

connStr = "server=localhost;database=myTestDb;uid=sa;pwd=''"; SqlConnection sqlConn = new SqlConnection(connStr); try { sqlConn.open(); sqlStr = "Select max(grade) from Grade"; SqlCommand sqlCmd = new SqlCommand(sqlStr, sqlConn); maxGrade = (decimal) sqlCmd.ExecuteScalar()

} Catch(SqlException ex) {

Console.WriteLine(ex.Message); } finally { sqlConn.close(); }

------------------------------------------------------------------------------------------------Having More Than One Query:

connStr = "server=localhost;database=myTestDb;uid=sa;pwd=''"; SqlConnection sqlConn = new SqlConnection(connStr); try { sqlConn.open(); sqlStr = "Select * From Student"; SqlCommand sqlCmd = new SqlCommand(sqlStr, sqlConn); SqlDataReader sqlreader = sqlCmd.ExecuteReader(); while(sqlReader.read()) Console.WriteLine(sqlReader["sid"];

SqlCmd.CommandText = "Insert Into Student values(2453202, 'hooman', 'shamsborhan')"; sqlCmd.ExecuteNonQuery();

sqlCmd.CommandText = "delete from student where sid = 2453202" sqlCmd.ExecuteNonQuery()

. .

.

} Catch(SqlException ex) { Console.WriteLine(ex.Message); } finally { sqlConn.close(); }

------------------------------------------------------------------------------------------------Using Parameters in a Query:

connStr = "server=localhost;database=myTestDb;uid=sa;pwd=''"; SqlConnection sqlConn = new SqlConnection(connStr); try { sqlConn.open(); sqlStr = "Select * From Student where sid = @stNo1 or sid = @stNo2"; SqlCommand sqlCmd = new SqlCommand(sqlStr, sqlConn);

sqlCmd.Parameters.Add("@stNo1", SqlDbType.Int); sqlCmd.Parameters.Add("@stNo2", SqlDbType.Int);

sqlCmd.Parameters["@stNo1"].Value = 2453202; sqlCmd.Parameters["@stNo2"].Value = 2453200;

SqlDataReader sqlreader = sqlCmd.ExecuteReader(); while(sqlReader.read()) Console.WriteLine(sqlReader["sid"];

SqlCmd.CommandText = "Insert Into Student values(2453202, 'hooman', 'shamsborhan')"; sqlCmd.ExecuteNonQuery();

sqlCmd.CommandText = "delete from student where sid = 2453202"; sqlCmd.ExecuteNonQuery()

. . .

} Catch(SqlException ex) { Console.WriteLine(ex.Message); } finally { sqlConn.close(); }

------------------------------------------------------------------------------------------------Having a Transaction:

connStr = "server=localhost;database=myTestDb;uid=sa;pwd=''"; SqlConnection sqlConn = new SqlConnection(connStr);

try { sqlConn.open(); SqlTransaction sqlTrans = sqlConn.BeginTransaction(IsolationLevel.serializable, "myTrans");

sqlStr = "update student set sPhone = '0400945506' where sid=2453202"; SqlCommand sqlCmd = new SqlCommand(sqlStr, sqlConn); sqlCmd.ExecuteNonQuery();

SqlCmd.CommandText = "update grade set grade = grade + (grade*20)/100 where gstudentId = 2453202"; sqlCmd.ExecuteNonQuery(); . . .

sqlTrans.Commit();

} Catch(SqlException ex) { sqlTrans.RollBack(); Console.WriteLine(ex.Message); } finally { sqlConn.close(); }

------------------------------------------------------------------------------------------------Calling a Stored Procedure:

Consider This Stored Procedure in Ms SqlServer:

Create Procedure spUpdateGrades

As Begin update grade set grade = (grade*20)/100 End Go

Now We Want to Call This Stored Procedure Within C#:

1.

connStr = "server=localhost;database=myTestDb;uid=sa;pwd=''"; SqlConnection sqlConn = new SqlConnection(connStr); try { sqlConn.open(); SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Connection = sqlConn sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.CommandText = "spUpdateGrades"

sqlCmd.ExecuteNonQuery();

} Catch(SqlException ex) { Console.WriteLine(ex.Message); } finally { sqlConn.close(); }

2.

connStr = "server=localhost;database=myTestDb;uid=sa;pwd=''"; SqlConnection sqlConn = new SqlConnection(connStr); try { sqlConn.open(); SqlCommand sqlCmd = new SqlCommand("spUpdateGrades",sqlConn); sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.ExecuteNonQuery();

} Catch(SqlException ex) { Console.WriteLine(ex.Message); }

finally { sqlConn.close(); }

------------------------------------------------------------------------------------------------Passing and Getting Parametes From a Stored Procedure:

Consider This Stored Procedure in Ms SqlServer:

Crete Procedure spStudentGrade( @stNum int, @fulname varchar(101) output, @grade int output ) As Begin Select @fulname = s.lname + "," + s.lname, @grade = g.grade from studend s, grade g where s.sid = @stNum and s.sid = g.studentId

if @@rowcount = 0 return -1 else return 1 End Go

Now We Want to Use This Stored Procedure Within C#:

connStr = "server=localhost;database=myTestDb;uid=sa;pwd=''"; SqlConnection sqlConn = new SqlConnection(connStr); try { sqlConn.open(); SqlCommand sqlCmd = new SqlCommand("spStudentGrade", sqlConn); sqlCmd.CommandType = CommandType.StoredProcedure;

SqlParameters sqlParam;

sqlParam = sqlCmd.Parameters.Add("stNum", SqlDbType.Int); sqlParam.Direction = ParameterDirection.Input; sqlParam.value = 2453202;

sqlParam = sqlCmd.Parameters.Add("@fulName", SqlDbType.Varchar, 101); sqlParam.Direction = ParameterDirection.Output;

sqlParam = sqlCmd.Parameters.Add("@grade", SqlDbType.Int); sqlParam.Direction = ParameterDirection.Output;

sqlParam = sqlCmd.Parameters.Add("@retVal", SqlDbType.Int) sqlParam.Direction = ParameterDirection.ReturnValue;

sqlCmd.ExecuteNonQuery();

if (int)sqlCmd.Parameters["@retVal"] = 1

Console.WriteLine("Full Name:" + sqlCmd.Parameters["@fulname"].Value); Console.WriteLine("Grade:" + sqlCmd.Parameters["@grade"].Value.ToString()); else Console.WriteLine("There is No Record");

} Catch(SqlException ex) { Console.WriteLine(ex.Message); } finally { sqlConn.close(); }

------------------------------------------------------------------------------------------------Input Parameter For a Stored Procedure:

For an Input Parameter Instead of Saying:

sqlParam = sqlCmd.Parameters.Add("stNum", SqlDbType.Int); sqlParam.Direction = ParameterDirection.Input; sqlParam.value = 2453202;

We Could Also Say:

sqlCmd.Parameters.Add("@stNo", 2453202);