Ado. NET's stored procedure call

To execute a stored procedure, you must connect to the database

The code is as follows:

            string " server= (local) \\sqlexpress;integrated security=sspi;database=student " ;             New SqlConnection (source); // Establish a connection            Con. Open (); // Open Database            if (Con. state = = ConnectionState.Open)//ConnectionState is an enumeration value that describes the connection status of the data source as open                Console.WriteLine ( " Database Connection Successful! ");

1. Call updates for records of stored procedures that do not return a value

SqlCommand com =NewSqlCommand ("studentupdate", con);//Create a command that invokes a command to execute a stored procedure that is defined in SQL 2008//then start calling the stored procedure because there are two input parameters in the stored procedure, so you need to build two parameters, set their values, and add them to the parameters (parameter collection)Com.commandtype = CommandType.StoredProcedure;//com. CommandType: Gets or sets a value that indicates how to interpret the CommandText property, CommandText property: A Transact-SQL statement, table name, or stored procedure that executes on a data source CommandType enumeration specifies how the command string is interpreted Com. Parameters.addwithvalue ("@id",1);//represents adding a value to the end of SqlParameterCollection, SqlParameterCollection represents a collection of parameters, which can also be used with COM. Parameters.Add (New SqlParameter ("@id", "id")) insteadCom. Parameters.addwithvalue ("@name","Bob"); intx = com. ExecuteNonQuery ();//call the stored procedureConsole.WriteLine ("number of rows affected: {0}", x);

--a well-written stored procedure must be executed or saved before the SqlCommand class can be created successfully UseStudentGoCreate procedureStudentupdate (@id int,@name Char(Ten)) asSetNocountoff--when SET NOCOUNT is on, no count is returned (indicating the number of rows affected by the Transact-SQL statement). When SET NOCOUNT is OFF, the count is returned. UpdateCustomersSetName=@namewhereId=@id;

2. Call delete of records for stored procedures that do not return a value

SqlCommand COM2 =NewSqlCommand ("Studentdelete", con); Com2.commandtype=CommandType.StoredProcedure; SqlParameter SP=NewSqlParameter ("@id", SqlDbType.Int,0,"ID"); COM2. UpdatedRowSource= Updaterowsource.none;//UpdateRowSource Enumeration: Specifies how query command results are applied to the row being updated, none means that any returned parameters or rows are ignoredCOM2. Parameters.Add (SP);//adds the specified SqlParameter object to the SqlParameterCollection, and the parameter is not assigned a value, but COM. Parameters.addwithvalue () has been assigned a valueSp. Value =1;//or use cmd. parameters["@id"].value=1COM2.            ExecuteNonQuery (); Console.WriteLine ("Delete Success! "); Con. Close ();

 Use Student Drop procedure Studentdelete; Go Create procedure studentdelete (@idint)assetoff Delete  from Customers where id=@id

3. Call does not return a query that is worth the stored procedure

SqlCommand COM5 =NewSqlCommand ("selectstudent", con); Com5.commandtype=CommandType.StoredProcedure; COM5. Parameters.Add ("@id",5); SqlDataReader Data=COM5.            ExecuteReader ();  while(data. Read ()) {Console.WriteLine ("name:{0} id:{1}", data[0], data[1]); }

 Use Student Drop procedure selectstudent Go Create procedure selectstudent (@idint)asselect*  from Customers where ID<=@id

4. Insert that invokes the record of a stored procedure with a return value

SqlCommand COM3 =NewSqlCommand ("Studentinsert", con); Com3.commandtype=CommandType.StoredProcedure; COM3. Parameters.Add (NewSqlParameter ("@name","John Doe")); COM3. Parameters.Add (NewSqlParameter ("@id", SqlDbType.Int,0, ParameterDirection.Output,false,0,0,"ID", Datarowversion.default,0)); COM3. UpdatedRowSource= Updaterowsource.outputparameters;//UpdateRowSource Enumeration: Specifies how query command results are applied to the row being updated, outputparameters indicates that the output parameter is mapped to a row that has changed in the datasetCOM3.            ExecuteNonQuery (); intx = (int) COM3. parameters["@id"]. value;//the way to get the output parameters Console.WriteLine ("output parameter (next ID number) is: {0}", x);

 UseStudentDrop procedureStudentinsertGoCreate procedureStudentinsert (@name Char( -),@id intoutput) asSetNocountoffSelect @id=MAX(ID)+1 fromCustomersInsert  intoCustomersValues(@name,@id);

5. Call has return worth stored procedure: Returns a set of records

SqlCommand COM4 =NewSqlCommand ("returnstudent", con); Com4.commandtype=CommandType.StoredProcedure; COM4. Parameters.Add (NewSqlParameter ("@id",3)); COM4. Parameters.Add (NewSqlParameter ("@rid", SqlDbType.Int,0, ParameterDirection.Output,false,0,0,"ID", Datarowversion.default,0)); COM4. Parameters.Add ("@rname", SqlDbType.Char, -). Direction= ParameterDirection.Output;//This method can omit unnecessary parameters.COM4.            ExecuteNonQuery (); intx = (int) COM4. parameters["@id"].            Value; stringY=COM4. parameters["@rname"]. Value.tostring ().            Trim (); Console.WriteLine ("id:{0} name:{1}", x, y);

 UseStudentDrop procedurereturnstudentGoCreate procedureReturnstudent (@id int,@rid intOutput@rname Char( -) output) asSelect @rid=Id@rname=Name fromCustomerswhereId=@id;