???????? ????????? ADO.NET ?? Visual Basic .NET ?? ????? ?? ???? Parameterized ??? ???? ????

??? ???????? ????????? ?? ??? ???? ?? ??? ?? ??? ????? ??? ??????? ???? ?? ???? ?? ??? ???????? ???? ???? ADO.NET ?? ????? ???? ?? ??? ?? ????? ???:

• ????? ???DataSet???????? ??? ????????? ?? ??????? ???? ?? ??? ?? ?? ?? ????????? ?? ????? ????? ??? ?? ????? ???????? ?? ??? ????? ???? ?? ????
• ????? ???DataReader???????? ??, ?? ????????? ?? ?????? ?? ?? ???? ?? ??? ?? ???? ???? ????????? ????? ??? ?? ??????? ?? ???????? ?? ???? ???? ?? ??????? ???? ?? ??? ???
• ?????ExecuteScalar??? ?? ????? ??? ?? ????? ???????? ?? ??? ???? ?????? ???????? ?? ???? ????? ?? ????? ?? ??? ?????? ??? ?? aggregate ?????? ?? ??? ???? ?????? ???
• ?????ExecuteNonQuery???? ???? ????? ???????? ?? ????? ??? ???? ???? ??? ???? ?? ??? ????????? ??? ??? ???? ?? ?????? ?????? ?? ????????? ???? ?? ??? ???? ?????? ???

?? ???? ????? ??? ??????? ????????? ???? ?? ?? ?? ????? ?? ????? ???? ??SqlCommand??OleDbCommand???????? ??? ????????? ???? ?? ?? ?? ????? ?? ??? ??? ?? ???????? ??????? ?? ??? ?? ???? ??? ?? ????????? ?????? ??? ?? ??????? ???? ??? ?? ???????? ??????? ?? ????? ???? ?????, ?? ????? Microsoft ?????? ??????? ??? ???? ?? ????:??? ?? ???????? ?? ???? ??? ?????, ??? ???????? ???? ???? ????????????? ?????????????????? ??? ?? ?? ????? ????SqlCommand????????, ?? ?? ??? ???? ???? ???????? ??? ????? ???? ???, ????? ?????????? ??? ??? ???? ?????? ?? ?? ????? ????OleDbCommand????????, ???? ???????? ??? ???? ??? ?????? ?????? ??, ?? ?? ??? ?? ???? ???????? ?? ????? ???? ???? ?? ?????

????????? ?? ???????? ???? ???? ?? ??? DataReader ?? ????? ????

?? ????? ?? ???? ???DataReader???? ?? ??? ' ????-????? ?? ???, ???? ???????? ??????? ?? ???? ???? ?? ??? ???????? ??? ??????? ??DataReader????? ??? ???????? ????????? ?? ??? ?? ???? ???? ?? ?????? ?? ????? ???? ??DataReader????? ?????????? ?? ????? ?? ??? ??? ??????? ?? ?????? ?? ??? ????? ?? ?????? ?? ?? ???????? ??, ?? ???? ??? ?? ???? ?? ?? ???? ???????? ????????? ?? ????? ?? ??? ???????? ???

1. Microsoft SQL ????? ?? ??? ?? ?? ????? ?? ???????? ????? ????????? ?? ?????:

   Create Procedure TestProcedure
     (
        @au_idIN varchar (11),
        @numTitlesOUT Integer OUTPUT
     )
   As
   
   select A.au_fname, A.au_lname, T.title 
   from authors as A join titleauthor as TA on
   A.au_id=TA.au_id
   join titles as T
   on T.title_id=TA.title_id
   where A.au_id=@au_idIN
   set @numTitlesOUT = @@Rowcount
   return (5) 
   					

2. ?????Imports?? ???????? ???????????System.Data???????? ???? ?? ???? ??? ??? ??? ??? ?? ???????? ??? declarations ????? ???? ??? ????? ?? ????? ????Imports???? declarations ???? ?? ???? ???? ???? ????????? ????? ?? ??? ????????? ???? ?? ???? ?????? ???? ?? ??????? ?? ??? ????SQL ???????

   Imports System.Data.SqlClient					

   OLE DB ???? ???????

   Imports System.Data.OleDb					

3. ????? ??? ??????Form_Load?????:SQL ???????

   Dim PubsConn As SqlConnection = New SqlConnection & _
       ("Data Source=server;integrated security=sspi;" & _
       "initial Catalog=pubs;")
   
   Dim testCMD As SqlCommand = New SqlCommand & _
       ("TestProcedure", PubsConn)
   
   testCMD.CommandType = CommandType.StoredProcedure
   
   Dim RetValue As SqlParameter = testCMD.Parameters.Add & _
       ("RetValue", SqlDbType.Int)
   RetValue.Direction = ParameterDirection.ReturnValue
   Dim auIDIN As SqlParameter = testCMD.Parameters.Add & _
       ("@au_idIN", SqlDbType.VarChar, 11)
   auIDIN.Direction = ParameterDirection.Input
   Dim NumTitles As SqlParameter = testCMD.Parameters.Add & _
       ("@numtitlesout", SqlDbType.Int)
   NumTitles.Direction = ParameterDirection.Output
   
   auIDIN.Value = "213-46-8915"
   PubsConn.Open()
   
   Dim myReader As SqlDataReader = testCMD.ExecuteReader()
   Console.WriteLine("Book Titles for this Author:")
   Do While myReader.Read
       Console.WriteLine("{0}", myReader.GetString(2))
   Loop
   myReader.Close()
           
   Console.WriteLine("Return Value: " & (RetValue.Value))
   Console.WriteLine("Number of Records: " & (NumTitles.Value))
   					

   OLE DB ???? ???????

   Dim PubsConn As OleDbConnection = New OleDbConnection & _
       ("Provider=sqloledb;Data Source=server;" & _
       "integrated security=sspi;initial Catalog=pubs;")
   
   Dim testCMD As OleDbCommand = New OleDbCommand & _
       ("TestProcedure", PubsConn)
   
   testCMD.CommandType = CommandType.StoredProcedure
   
   Dim RetValue As OleDbParameter = testCMD.Parameters.Add & _
       ("RetValue", OleDbType.Integer)
   RetValue.Direction = ParameterDirection.ReturnValue
   Dim auIDIN As OleDbParameter = testCMD.Parameters.Add & _
       ("@au_idIN", OleDbType.VarChar, 11)
   auIDIN.Direction = ParameterDirection.Input
   Dim NumTitles As OleDbParameter = testCMD.Parameters.Add & _
       ("@numtitlesout", OleDbType.Integer)
   NumTitles.Direction = ParameterDirection.Output
   
   auIDIN.Value = "213-46-8915"
   PubsConn.Open()
   
   Dim myReader As OleDbDataReader = testCMD.ExecuteReader()
   Console.WriteLine("Book Titles for this Author:")
   Do While myReader.Read
       Console.WriteLine("{0}", myReader.GetString(2))
   Loop
   myReader.Close()
   
   Console.WriteLine("Return Value: " & (RetValue.Value))
   Console.WriteLine("Number of Records: " & (NumTitles.Value))
   					

4. ??????? ???????? ?? ??? ??????? ???????????SQL ?? ??? ?? ?? ????? ?? ????? ???? ?? ??? ???????? ??????
5. ??? ?? ?????? ?? ?????DataReader?????? retrieves, ?? ???? ??? ???????? ??? ???? ??? ?? ????? ?? ???? ????????? ??? ????DataReader???????? ??? ??????? ?? ?????? ?? ?? ???? ?? ??? ???
   
   ?????? ????? ???????? ?? ?? ????????, 5 ?? ?????? ????????, ?????? ?????? (2) ?? ?????? ?? ????? ??? ?????? ??? ????? ??? ???? ????DataReader???????? ??? ????? ?? ??? ??? ???? ??? ??, ??? ???? ?? ???? ??? ???? ?? ??? ????? ?????????? ?? ????? ?? ??? ??? ???????? ?? ?????? ?? ?? ???? ?? ???DataReader??? ???

???? ???????? ?? ExecuteScalar ???? ?? ????? ????

?? ????? ?? ???? ???ExecuteScalar?? ??? ???????????????? ??? ??????? ???? ?? ??? ???????? ??? ??? ??,ExecuteScalar???????? ????????? ?? ???? ?????? ?? ???? ????? ???? ??? ?? ????? ?????? ??? ?? ??? ??? aggregate ?????? ?? ??? ???? ?????? ???

1. SQL ????? ?? ??? ?? ?? ????? ?? ???????? ????? ????????? ?? ?????:

   Create Procedure TestProcedure2
    (
      @au_idIN varchar (11)
     )
   As
   /* set nocount on */ 
   select count (T.title) 
   from authors as A join titleauthor as TA on
   A.au_id=TA.au_id
   join titles as T
   on T.title_id=TA.title_id
   where A.au_id=@au_idIN
   Return(5)
   					

2. ?????Imports?? ???????? ???????????System.Data???????? ???? ?? ???? ??? ??? ??? ??? ?? ???????? ??? declarations ????? ???? ??? ????? ?? ????? ????Imports???? declarations ???? ?? ???? ???? ????????? ???? ?? ???? ?????? ???? ?? ??????? ?? ??? ?? ???? ??? ?? ????????? ??????SQL ???????

   Imports System.Data.SqlClient					

   OLE DB ???? ???????

   Imports System.Data.OleDb					

3. ????? ??? ??????Form_Load?????:SQL ???????

   Dim PubsConn As SqlConnection = New SqlConnection & _
       ("Data Source=server;integrated security=sspi;" & _
       "initial Catalog=pubs;")
   
   Dim testCMD As SqlCommand = New SqlCommand & _
       ("TestProcedure2", PubsConn)
   
   testCMD.CommandType = CommandType.StoredProcedure
   
   Dim RetValue As SqlParameter = testCMD.Parameters.Add & _
       ("RetValue", SqlDbType.Int)
   RetValue.Direction = ParameterDirection.ReturnValue
   Dim auIDIN As SqlParameter = testCMD.Parameters.Add & _
       ("@au_idIN", SqlDbType.VarChar, 11)
   auIDIN.Direction = ParameterDirection.Input
   
   auIDIN.Value = "213-46-8915"
   PubsConn.Open()
   
   Dim intCount As Integer = testCMD.ExecuteScalar
   Console.WriteLine(intCount)
   Console.WriteLine("Return Value: " & (RetValue.Value))
   					

   OLE DB ???? ???????

   Dim PubsConn As OleDbConnection = New OleDbConnection & _
       ("Provider=SQLOLEDB;Data Source=server;" & _
       "integrated Security=sspi;initial catalog=pubs;")
   
   Dim testCMD As OleDbCommand = New OleDbCommand & _
       ("TestProcedure2", PubsConn)
   
   testCMD.CommandType = CommandType.StoredProcedure
   
   Dim RetVal As OleDbParameter = testCMD.Parameters.Add & _
       ("RetVal", OleDbType.Integer)
   RetVal.Direction = ParameterDirection.ReturnValue
   Dim IdIn As OleDbParameter = testCMD.Parameters.Add & _
       ("@au_idIN", OleDbType.VarChar, 11)
   IdIn.Direction = ParameterDirection.Input
   
   IdIn.Value = "213-46-8915"
           
   PubsConn.Open()
   
   Dim intCount As Integer = testCMD.ExecuteScalar
   
   Console.WriteLine("Number of Rows: " & intCount)
   Console.WriteLine(RetVal.Value)
   					

4. ??????? ???????? ?? ??? ??????? ???????????SQL ?? ??? ?? ?? ????? ?? ????? ???? ?? ??? ???????? ??????
5. ??? ?? ?????? ?? ?????ExecuteScalar?? ??? ???????????????? ?????????? ?? ???? ???ExecuteScalaralso returns the value of column 1, row 1 of the returned rowset. Thus, the value ofintCountis the result of the count function from the stored procedure.

???? ???????? ?? ExecuteNonQuery ???? ?? ????? ????

?? ????? ?? ????? ???? ??ExecuteNonQuerymethod to run the query and to return the parameter values.ExecuteNonQueryalso returns the number of records that are affected after the query runs. ???????,ExecuteNonQuerydoes not return any rows or columns from the stored procedure.

TheExecuteNonQuerymethod is most useful when you use INSERT, UPDATE, or DELETE statements if you only have to know how many rows are changed. ??? ???????? ????????? ?????? ?? ???? ?? ?? ??? ???? ??? ????? ??? ?? ???, ??? ???? -1 ??????? ??? ????????? ?? ?????? ?? ???????? ??? ????

1. SQL ????? ?? ??? ?? ?? ????? ?? ???????? ????? ????????? ?? ?????:

   Create Procedure TestProcedure3
     (
   	@au_idIN varchar (11),
   	@au_fnam varchar (30)
     )
   
   As
   /* set nocount on */ 
   Update authors set au_fname = @au_fnam
   where au_id = @au_idin	
   return (5)
   					

2. ?????Imports?? ???????? ???????????System.Data???????? ???? ?? ???? ??? ??? ??? ??? ?? ???????? ??? declarations ????? ???? ??? ????? ?? ????? ????Imports???? declarations ???? ?? ???? ???? ????????? ???? ?? ???? ?????? ???? ?? ??????? ?? ??? ?? ???? ??? ?? ????????? ??????SQL ???????

   Imports System.Data.SqlClient					

   OLE DB ???? ???????

   Imports System.Data.OleDb					

3. ????? ??? ??????Form_Load?????:SQL ???????

   Dim PubsConn As SqlConnection = New SqlConnection & _
       ("Data Source=server;integrated security=sspi;" & _
       "initial Catalog=pubs;")
   
   Dim testCMD As SqlCommand = New SqlCommand & _
       ("TestProcedure3", PubsConn)
   
   testCMD.CommandType = CommandType.StoredProcedure
   
   Dim RetValue As SqlParameter = testCMD.Parameters.Add & _
       ("RetValue", SqlDbType.Int)
   RetValue.Direction = ParameterDirection.ReturnValue
   Dim auIDIN As SqlParameter = testCMD.Parameters.Add & _
       ("@au_idIN", SqlDbType.VarChar, 11)
   auIDIN.Direction = ParameterDirection.Input
   Dim auFname As SqlParameter = testCMD.Parameters.Add & _
       ("@au_fnam", SqlDbType.VarChar, 30)
   auFname.Direction = ParameterDirection.Input
   
   auIDIN.Value = "213-46-8915"
   auFname.Value = "Marjorie"
   PubsConn.Open()
   
   Dim rvRows As Integer = testCMD.ExecuteNonQuery
   Console.WriteLine(rvRows)
   Console.WriteLine(RetValue.Value)
   					

   OLE DB ???? ???????

   Dim PubsConn As OleDbConnection = New OleDbConnection & _
       ("Provider=SQLOLEDB;Data Source=server;" & _
       "integrated Security=sspi;initial catalog=pubs;")
   
   Dim testCMD As OleDbCommand = New OleDbCommand & _
       ("TestProcedure3", PubsConn)
   
   testCMD.CommandType = CommandType.StoredProcedure
   
   Dim RetVal As OleDbParameter = testCMD.Parameters.Add & _
       ("RetVal", OleDbType.Integer)
   RetVal.Direction = ParameterDirection.ReturnValue
   Dim IdIn As OleDbParameter = testCMD.Parameters.Add & _
       ("@au_idIN", OleDbType.VarChar, 11)
   IdIn.Direction = ParameterDirection.Input
   Dim FnameIn As OleDbParameter = testCMD.Parameters.Add & _
       ("@au_fname", OleDbType.VarChar, 30)
   FnameIn.Direction = ParameterDirection.Input
   
   IdIn.Value = "213-46-8915"
   FnameIn.Value = "Marjorie"
   
   PubsConn.Open()
   
   Dim intRowAffected As Integer = testCMD.ExecuteNonQuery
   
   Console.WriteLine("Number of Rows affected: " & intRowAffected)
   Console.WriteLine(RetVal.Value)
   					

4. ??????? ???????? ?? ??? ??????? ???????????SQL ?? ??? ?? ?? ????? ?? ????? ???? ?? ??? ???????? ??????
5. ??? ?? ?????? ?????? ????? ????????? ???? ?? (?? ???????? ????????? ?? ??????intRowAffect) ?? ?? ????? ???????? ????

???????? ??????? ?? ???, ????? MSDN ??? ???? ?? ????:ADO.NET ?? Visual Basic .NET ?? ???? ??? ???? ??????? ??????? ?? ??? ????? MSDN ?????? ?????? ?? ????:???? ??????? ?? ???, ????? Microsoft ????????? ?? ??????? ????????? ?????: