?????: ??????? ????? ???? ?????? ???????? ADO.NET ? Visual C#.NET

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

• ??????? ???? DataSet ??? ????? ?????? ???? ?? ??????? ?????? ?? ??? ?????? ???????? ??? ????? ??????? ????????? ???????.
• ??????? ???? DataReader ???? ?????? ???? ?? ???????? ???????? ?? ???? ??? ??????? ?? ??? ????? ????? ??????? ????????? ???????.
• ?????? ??????? ExecuteScalar ?????? ?????? ?? ?????? ????? ?? ???? ????? ??????? ?? ????? ??????? ????????? ???????. ???? ??? ?????? ???????? ??????? ?????????.
• ?????? ??????? ExecuteNonQuery ?????? ????? ??????? ????????? ???????. ??? ????? ?? ?????? ???? ?? ???????. ??? ???? ???? ?????? ??????????? ?????????.

????? ??? ??????? ???? ???????? ??????? ??????? ??????? ?? ?? SqlCommand ????????? OleDbCommand . ???? ?? ?? ???? ???? ????????? ???????? ??? ?????? ?????? ???? ???????. ??? ?? ??? ??????? ?? ?????? ?????? ???? ??? ????????? ?? ?????? ???? Microsoft Developer Network ??? ??? ??????:?? ?? ?? ??????? ?? ??? ???????? ??? ????? ???????? ??? ?????? ???????? ????? ????? . ??? ??????? ???? SqlCommand ? ??? ?? ????? ???????? ?? ?? ????? ????? ???? ??? ?? ???? ???????? ??? ????? ??????. ??? ??????? ???? OleDbCommand ? ??? ????? ???????? ???????? ??????? ??? ????? ??????? ???????? ??? ?????.

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

????? ??????? ?????? DataReader ?????? ??? ?????? ??????? ??? ?? ?????? ???. ???? ?? ???? ????????? ???? ??????? DataReader ?? ????? ????. ?????? ??? ?????? ?????? DataReader ?????? ????? ???? ???? ????? ??? ????? ????? ?????? ?????? ?? ???? ??????? ???? ?? ??????? ???? ?????? ???????.

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

   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. ????? ???? Visual C#.????? ????? Windows NET.
3. ??????? ????? ????????????? ??????? System.Data ??? ?? ????? ??? ????? ????????? ??????? ?????? ?? ????????? ????????. ????? ??? ????? ??? ???? ?????? ??????? ????????? ???????? ???????. ???? ?? ??? ????????? ???????? ??? ?????? ???? ??????.???? SQL

   using System.Data.SqlClient;
   					

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

   using System.Data.OleDb;
   					

4. ??????? ????? ?? ????? Form_Load ?????? ????????? ???????? ???????:???? SQL

   SqlConnection PubsConn = new SqlConnection 
   ("Data Source=server;integrated " + 
   "Security=sspi;initial catalog=pubs;");
   SqlCommand testCMD = new SqlCommand 
   ("TestProcedure", PubsConn);
   
   testCMD.CommandType = CommandType.StoredProcedure;
   
   SqlParameter RetVal = testCMD.Parameters.Add 
      ("RetVal", SqlDbType.Int);
   RetVal.Direction = ParameterDirection.ReturnValue;
   SqlParameter IdIn = testCMD.Parameters.Add 
     ("@au_idIN", SqlDbType.VarChar, 11);
   IdIn.Direction = ParameterDirection.Input;
   SqlParameter NumTitles = testCMD.Parameters.Add 
      ("@numtitlesout", SqlDbType.VarChar, 11);
   NumTitles.Direction = ParameterDirection.Output ;
           
   IdIn.Value = "213-46-8915";
   PubsConn.Open();
   
   SqlDataReader myReader = testCMD.ExecuteReader();
   Console.WriteLine ("Book Titles for this Author:");
   while (myReader.Read()) 
      {
        Console.WriteLine ("{0}", myReader.GetString (2));
      };
   myReader.Close() ;
   Console.WriteLine("Number of Rows: " + NumTitles.Value );
   Console.WriteLine("Return Value: " + RetVal.Value);
   					

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

   OleDbConnection PubsConn = new OleDbConnection 
      ("Provider=SQLOLEDB;Data Source=server;" + 
      "integrated Security=sspi;initial catalog=pubs;");
   OleDbCommand testCMD = new OleDbCommand 
      ("TestProcedure", PubsConn);
   
   testCMD.CommandType = CommandType.StoredProcedure;
   
   OleDbParameter RetVal = testCMD.Parameters.Add 
      ("RetVal", OleDbType.Integer);RetVal.Direction = ParameterDirection.ReturnValue;
   OleDbParameter IdIn = testCMD.Parameters.Add 
      ("@au_idIN", OleDbType.VarChar, 11);
   IdIn.Direction = ParameterDirection.Input;
   OleDbParameter NumTitles = testCMD.Parameters.Add 
      ("@numtitlesout", OleDbType.VarChar, 11);
   NumTitles.Direction = ParameterDirection.Output;
           
   IdIn.Value = "213-46-8915";
   
   PubsConn.Open();
   
   OleDbDataReader myReader = testCMD.ExecuteReader();
   Console.WriteLine ("Book Titles for this Author:");
   while (myReader.Read()) 
      {
        Console.WriteLine ("{0}", myReader.GetString (2));
      };
   myReader.Close() ;
   Console.WriteLine("Number of Rows: " + NumTitles.Value );
   Console.WriteLine("Return Value: " + RetVal.Value);
   					

5. ?? ?????? ????? ??????? ????? ????? ??????? ??? ????????? ???? ?????? SQL Server.
6. ????? ????????? ????????. ???? DataReader ???? ???????? ??????? ??? ?? ????? ??? ????????. ????? ??????? ??????? ????? ?????? DataReader ?????? ???? ??????? ???? ?? ???????.
   
   ???? ???? ??????? ?????? ????? ????? ???? ??????? ? 5 ?????? ???????? ???? ????? ??? ????? ?? ??????? (2). ???? ??? ??? ????? DataReader ?? ????????? ???????? ??????? ??? ????????. ???????? ??? ???? ???? ??? ?? ???? ?????? ???? ???? ??????? ????? ?????? ??????? ??? ?? ????? DataReader .

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

????? ??????? ??????? ExecuteScalar ????? ??????? ??????? ??? ????????. ???????? ??? ???? ExecuteScalar ?????? ?????? ????? ?? ???? ????? ?? ??????? ??????. ??? ???? ???? ??????? ????????? ??? ?? ?????? ??????.

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

   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. ????? ???? Visual C#.????? ????? Windows NET.
3. ??????? ????? ????????????? ??????? System.Data ??? ?? ????? ??? ????? ????????? ??????? ?????? ?? ????????? ????????. ????? ??? ????? ??? ???? ?????? ??????? ????????? ???????? ???????. ???? ?? ?? ???? ???? ????????? ???????? ??? ????? ???? ??????.???? SQL

   using System.Data.SqlClient;
   					

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

   using System.Data.OleDb;
   					

4. ????? ???????? ???????? ??????? ??? ????? Form_Load :???? SQL

   string strCount;
   SqlConnection PubsConn = new SqlConnection 
      ("Data Source=server;integrated " + 
      "Security=sspi;initial catalog=pubs;");
   SqlCommand testCMD = new SqlCommand 
      ("TestProcedure2", PubsConn);
   
   testCMD.CommandType = CommandType.StoredProcedure;
   
   SqlParameter RetVal = testCMD.Parameters.Add 
      ("RetVal", SqlDbType.Int);
   RetVal.Direction = ParameterDirection.ReturnValue;
   SqlParameter IdIn = testCMD.Parameters.Add 
      ("@au_idIN", SqlDbType.VarChar, 11);
   IdIn.Direction = ParameterDirection.Input;
           
   IdIn.Value = "213-46-8915";
   
   PubsConn.Open();
   
   strCount =testCMD.ExecuteScalar ().ToString() ;
   
   Console.WriteLine("Number of Rows: " + strCount );
   Console.WriteLine("Return Value: " + RetVal.Value);
   					

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

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

5. ?? ?????? ????? ??????? ????? ????? ??????? ??? ????????? ???? ?????? SQL Server.
6. ????? ????????? ????????. ???? ?? ??????? ExecuteScalar ????? ????? ?????? ????????. ExecuteScalar ???? ?????? ???? ?????? 1? ???? ????? ?? ?????? ?????? ???? ?? ???????. ?????? ???? intCount ?? ????? ?????? ??? ?? ??????? ??????.

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

?????? ??? ??????? ??????? ExecuteNonQuery ?????? ????????? ?????? ??? ????????. ExecuteNonQuery ???? ?????? ??? ??????? ???? ????? ??? ????? ?????????. ??? ????? ?? ???? ExecuteNonQuery ?? ???? ??? ???? ?? ????? ?? ??????? ??????.

??????? ExecuteNonQuery ?????? ???? ??? ??????? ?????? INSERT ?? ??????? ?? ????? ??? ??? ???? ????? ??? ?????? ???? ?? ??????? ???. ?? ????? ???? ???? ??? ?????? ????? SELECT? ?????-1 ??? ????? ??? ???? ??? ?????????.

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

   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. ????? ???? Visual C#.????? ????? Windows NET.
3. ??????? ????? ????????????? ??????? System.Data ??? ?? ????? ??? ????? ????????? ??????? ?????? ?? ????????? ????????. ????? ??? ????? ??? ???? ?????? ??????? ????????? ???????? ???????. ???? ?? ?? ???? ???? ????????? ???????? ??? ????? ???? ??????.???? SQL

   using System.Data.SqlClient;
   					

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

   using System.Data.OleDb;
   					

4. ??????? ????? ????? ????? Form1_Load ???? ?? ?????? ??????? ????????? ???????? Form1 ???????? ???????? ???????:???? SQL

   string strRowAffect;
   SqlConnection PubsConn = new SqlConnection 
      ("Data Source=server;integrated Security=sspi;" + 
      "initial catalog=pubs;");
   SqlCommand testCMD = new SqlCommand 
      ("TestProcedure3", PubsConn);
   
   testCMD.CommandType = CommandType.StoredProcedure;
   
   SqlParameter RetVal = testCMD.Parameters.Add 
      ("RetVal", SqlDbType.Int);
   RetVal.Direction = ParameterDirection.ReturnValue;
   SqlParameter IdIn = testCMD.Parameters.Add 
      ("@au_idIN", SqlDbType.VarChar, 11);
   IdIn.Direction = ParameterDirection.Input;
   SqlParameter FnameIn = testCMD.Parameters.Add 
      ("@au_fnam", SqlDbType.VarChar, 30);
   FnameIn.Direction = ParameterDirection.Input;
   
   IdIn.Value = "213-46-8915";
   FnameIn.Value = "Marjorie";
   
   PubsConn.Open();
   
   strRowAffect =testCMD.ExecuteNonQuery ().ToString() ;
   
   Console.WriteLine("Number of Rows: " + strRowAffect );
   Console.WriteLine("Return Value: " + RetVal.Value);
   					

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

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

5. ?? ?????? ????? ??????? ????? ????? ??????? ??? ????????? ???? ?????? SQL Server.
6. ????? ????????? ????????. ???? ???? ??????? ??? ?????? ???????? (????????????) ????? ??????? ???????.

????? ?? ?????????? ?? ?????? ????? "msdn ??? ???" ???????: