ÏÐÀÊÒÈ×ÅÑÊÎÅ ðóêîâîäñòâî: Îáíîâëåíèå ðîäèòåëüñêèõ è äî÷åðíèõ äàííûõ ñî ñòîëáöîì èäåíòèôèêàòîðîâ èç ïðèëîæåíèÿ Windows Forms ñ ïîìîùüþ âåá-ñëóæáû â Visual C#.NET

Ýòà ñòàòüÿ ñîäåðæèò ïîøàãîâûå èíñòðóêöèè äëÿ èçâëå÷åíèÿ Íàáîð äàííûõ Îáúåêò, ðàñïîëîæåííûé íà âåá-ñëóæáû, êàê îòïðàâèòü îáíîâëåíèÿ DiffGram Âåá-ñëóæáû, à çàòåì îáúåäèíèòü DiffGram îáíîâëåííûé êëèåíò Íàáîð äàííûõ. Ýòîò ïðîöåññ âêëþ÷àåò ñëåäóþùèå øàãè:

1. Ïîëó÷åíèå êëèåíòñêîãî ïðèëîæåíèÿ Íàáîð äàííûõ îáúåêò ñ äâóìÿ Îáúåêò DataTable îáúåêòû â îòíîøåíèè ðîäèòåëüñêèé äî÷åðíèé âåá-ñëóæáû.  ðîäèòåëè Îáúåêò DataTable ñîäåðæèò ñòîëáåö Identity èëè Autonumber êàê ïåðâè÷íûé êëþ÷.
2.  êëèåíòñêîì ïðèëîæåíèè ïîëüçîâàòåëü ìîæåò äîáàâëÿòü, óäàëÿòü è Èçìåíåíèå ðîäèòåëüñêèõ è äî÷åðíèõ çàïèñåé. Íîâûå çàïèñè ðîäèòåëüñêîãî ïîëó÷àòü âðåìåííûé çíà÷åíèå ïåðâè÷íîãî êëþ÷à, ñîçäàííîãî ëîêàëüíî.
3. Êëèåíòñêîå ïðèëîæåíèå îòïðàâëÿåò èçìåíåíèÿ îáðàòíî â Èíòåðíåòå Ñëóæáà â ôîðìàòå DiffGram.
4. Îáíîâëÿåò áàçó äàííûõ, âåá-ñëóæáû, èçâëåêàåò ôàêòè÷åñêèé ïåðâè÷íûé êëþ÷ çíà÷åíèÿ äëÿ íîâûõ ðîäèòåëüñêèõ çàïèñåé è çàòåì ïåðåäàåò çíà÷åíèÿ êëþ÷åé èçìåíåíèÿ äî÷åðíèõ çàïèñåé.
5. Êëèåíòñêîå ïðèëîæåíèå ïîëó÷àåò îáíîâëåííûå çíà÷åíèÿ èç Âåá-ñëóæáû, à çàòåì îáúåäèíÿåò îáíîâëåííûå çíà÷åíèÿ îáðàòíî â ëîêàëüíûé Íàáîð äàííûõ.

Äëÿ ïîëó÷åíèÿ äîïîëíèòåëüíûõ ñâåäåíèé îá îáíîâëåíèè îäíîé òàáëèöû ñ ïîìîùüþ âåá-ñëóæáû, ùåëêíèòå ñëåäóþùèé íîìåð ñòàòüè äëÿ ïðîñìîòðà ñòàòüè â â Microsoft Knowledge Base:

Òðåáîâàíèÿ

 ñëåäóþùåì ñïèñêå ïðåäñòàâëåíû ðåêîìåíäóåìîãî îáîðóäîâàíèÿ, ïðîãðàììíîãî îáåñïå÷åíèÿ, ñåòåâîé èíôðàñòðóêòóðû è ïàêåòîâ îáíîâëåíèÿ, êîòîðûå íåîáõîäèìû:

• Microsoft Windows 2000 Professional, Windows 2000 Server Windows 2000 Advanced Server èëè Windows NT 4.0 Server
• Microsoft Visual Studio.NET

 äàííîé ñòàòüå ïðåäïîëàãàåòñÿ, ÷òî âû çíàêîìû ñî ñëåäóþùèìè ðàçäåëû:

• Visual Studio.NET
• ADO.NET îñíîâû è ñèíòàêñèñ
• ASP.NET îñíîâû è ñèíòàêñèñ

Ïðèìåðû êîäà â ýòîé ñòàòüå èñïîëüçóþò http://localhost êàê Âåá-ñåðâåð. Êðîìå òîãî, ñ ïîìîùüþ ïðèìåðîâ êîäà «Áîðåé» áàçû äàííûõ â êà÷åñòâå áàçû äàííûõ.  «Áîðåé» Áàçà äàííûõ âõîäèò â ñîñòàâ Microsoft SQL Server.

Ñîçäàíèå âåá-ñëóæáû

1. ×òîáû ñîçäàòü íîâûé Visual C# ASP.NET âåá-ñëóæáû ïðîåêòà âûïîëíèòå ñëåäóþùèå äåéñòâèÿ.
   1. Çàïóñòèòå Visual Studio.NET.
   2. Íà Ôàéë Âûáåðèòå ïóíêò Íîâûé, à çàòåì íàæìèòå êíîïêó Ïðîåêò.
   3. Íàæìèòå êíîïêó Ïðîåêòû Visual C# Èç ñïèñêà Òèïû ïðîåêòîâ, à çàòåì íàæìèòå êíîïêó ASP.NET âåá-ñëóæáû Èç ñïèñêà Øàáëîíû.
   4.  Ðàñïîëîæåíèå ïîëå, ïî óìîë÷àíèþ îòîáðàæàåòñÿ â âèäå http://localhost/WebService1. Ââåäèòå URL-àäðåñ ñåðâåðà (íàïðèìåð, àäðåñ http://localhost çàïóñêàåò âåá-ñëóæáû íà ëîêàëüíîì âåá-ñåðâåðå). Çàìåíà WebService1 ñ CSharpUpdateData. URL-àäðåñ â Ðàñïîëîæåíèå ïîëå äîëæíî âûãëÿäåòü ñëåäóþùèì îáðàçîì:
      http://localhost/CSharpUpdateData
   5. ×òîáû çàêðûòü Íîâûé ïðîåêò äèàëîãîâîå îêíî, íàæìèòå êíîïêó ÎÊ.
2. Íà Service1.asmx.cs[Design] ñòðàíèöû, ïåðåêëþ÷èòåñü â ïðåäñòàâëåíèå «êîä». Îáðàòèòå âíèìàíèå, ÷òî îòêðûâàåòñÿ îêíî êîäà äëÿ âåá-ñëóæáû.
3.  âåðõíåé ÷àñòè îêíà êîäà äîáàâüòå ñëåäóþùóþ ñ ïîìîùüþ èíñòðóêöèè:

   using System.Data.SqlClient;

4. Äîáàâüòå â êëàññ Service1 ðåàëèçàöèè:

           [WebMethod]
           public DataSet GetData()
           {
               SqlConnection conn = new SqlConnection ("server=vcdb02;uid=sa;pwd=ricka;database=northwind");
               //Pull back the recent orders for the parent rows.
               SqlDataAdapter daOrder = new SqlDataAdapter("SELECT * FROM Orders WHERE OrderDate >= '05/01/1998'",conn);
               //Get only the appropriate child rows for the parent rows.
               SqlDataAdapter daDetails = new SqlDataAdapter("SELECT * FROM [Order Details] WHERE OrderID in ( SELECT OrderID FROM Orders WHERE OrderDate >= '05/01/1998')",conn);
   
               DataSet ds = new DataSet();
               try
               {
   				
                   //Fill DataSet, and then set DataRelation to move through the DataGrid.
                   conn.Open();
   
                   daOrder.FillSchema(ds,SchemaType.Mapped,"Orders");
                   daOrder.Fill(ds,"Orders");
   
                   daDetails.FillSchema(ds,SchemaType.Mapped,"Details");
                   daDetails.Fill(ds,"Details");
   
                   ds.Relations.Add("OrdDetail", ds.Tables["Orders"].Columns["OrderID"], ds.Tables["Details"].Columns["OrderID"]);
   			
                   DataColumn dc = ds.Tables["Orders"].Columns["OrderID"];
                   dc.AutoIncrement = true;
                   dc.AutoIncrementSeed = -1;
                   dc.AutoIncrementStep = -1;
               }
               catch(SqlException ex)
               {
                   Console.Write (ex.Message.ToString ());
                   Console.Write(ex.InnerException.ToString ());
   
               }
   
               return ds;
           }
   		
           [WebMethod]
           public DataSet UpdateData(DataSet ds)
           {
               SqlConnection conn = new SqlConnection ("server=vcdb02;uid=sa;pwd=ricka;database=northwind");
               //Pull back the recent orders for the parent rows.
               SqlDataAdapter daOrders = new SqlDataAdapter("SELECT * FROM Orders WHERE OrderDate >= '05/01/1998'",conn);
               //Get only the appropriate child rows for the parent rows.
               SqlDataAdapter daDetails = new SqlDataAdapter("SELECT * FROM [Order Details] WHERE OrderID in ( SELECT OrderID FROM Orders WHERE OrderDate >= '05/01/1998')",conn);
   
   			
               try
               {
                   conn.Open();
                   // Get commands for the Orders table.
                   // Reselect record after insert to get new Identity value.
                   // You must get the schema, which you did in GetData(), before you get commands; 
                   // otherwise, the Command builder tries to insert new rows, based 
                   // on the Identity column.
                   SqlCommandBuilder cb = new SqlCommandBuilder(daOrders);
                   daOrders.DeleteCommand = cb.GetDeleteCommand();
                   daOrders.UpdateCommand = cb.GetUpdateCommand();
                   daOrders.InsertCommand = cb.GetInsertCommand();
                   daOrders.InsertCommand.CommandText = String.Concat(daOrders.InsertCommand.CommandText, "; Select * From Orders Where OrderID = @@IDENTITY");
   
                   //UpdateRowSource tells the DataAdapter that there will be a re-selected record.
         
                   daOrders.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
                   //cb = null;
           
   
                   // Get commands for the Order Details table.
                   // Must set the QuotePrefix and QuoteSuffix; 
                   // otherwise, the CommandBuilder does not put brackets ([])
                   // around the table name.
   
                   SqlCommandBuilder cb1 = new SqlCommandBuilder(daDetails);
                   cb1.QuotePrefix = "[";
                   cb1.QuoteSuffix = "]";
                   daDetails.DeleteCommand = cb1.GetDeleteCommand();
                   daDetails.InsertCommand = cb1.GetInsertCommand();
                   daDetails.UpdateCommand = cb1.GetUpdateCommand();
   
   
                   // Create a new DataAdapter based on the original one to prevent the
                   // CommandBuilder from modifying the SQL statements, 
                   // specifically the custom InsertCommand.
                   // You do not need this if you roll your own commands and parameters 
                   // or if you use the Visual Tools to do it.
              
                   SqlDataAdapter daOrd2 = new SqlDataAdapter();
                   daOrd2.DeleteCommand = daOrders.DeleteCommand;
                   daOrd2.InsertCommand = daOrders.InsertCommand;
                   daOrd2.UpdateCommand = daOrders.UpdateCommand;
   
                   // Use a delegate to prevent AcceptChanges from occurring on Deletes and Inserts.
                   // This is for a limitation of the DataAdapter; see Q313540. 
   
                   daOrd2.RowUpdated += new SqlRowUpdatedEventHandler(OnOrd1RowUpdated);
                   daDetails.RowUpdated += new SqlRowUpdatedEventHandler(OnDetailsRowUpdated);
   
   
                   daDetails.Update(GetDeletedRows(ds.Tables["Details"]));
                   daOrd2.Update(GetDeletedRows(ds.Tables["Orders"]));
                   DataRow [] dsArray = ds.Tables["Orders"].Select("", "", DataViewRowState.ModifiedCurrent);
                   daOrd2.Update(ds.Tables["Orders"].Select("", "", DataViewRowState.ModifiedCurrent));
                   daDetails.Update(ds.Tables["Details"].Select("", "", DataViewRowState.ModifiedCurrent));
   
                   daOrd2.Update(ds.Tables["Orders"].Select("", "", DataViewRowState.Added));
   
                   ds.EnforceConstraints = false;
                   daDetails.Update(ds.Tables["Details"].Select("","", DataViewRowState.Added));
                   ds.EnforceConstraints = true;
   
                   conn.Close();
   
   				
   
               }
               catch(SqlException ex)
               {
                   Console.Write (ex.Message.ToString ());
                   Console.Write(ex.InnerException.ToString ());
               }
               return ds;
           }
   
   
           protected static void OnOrd1RowUpdated(object sender, SqlRowUpdatedEventArgs args)
           {
               if(args.StatementType == StatementType.Insert || args.StatementType == StatementType.Delete )
                   args.Status = UpdateStatus.SkipCurrentRow;
   
           }
           protected static void OnDetailsRowUpdated(object sender, SqlRowUpdatedEventArgs args)
           {
               if(args.StatementType == StatementType.Insert )
               {
                   // Do not allow the AcceptChanges to occur on this row.
                   args.Status = UpdateStatus.SkipCurrentRow;
   
                   // Get the current, actual primary key value so that you can plug it back
                   // in after you get the correct original value that was generated for the child row.
                   int currentkey = (int)args.Row["OrderID"];
                   // This is where you get a correct original value key that is stored to the child row. 
                   // You pull the original, pseudo key value from the parent, plug it in as the child row's primary key
                   // field, and then accept changes on it. Specifically, this is why you turned off EnforceConstraints.
                   args.Row["OrderID"] = args.Row.GetParentRow("OrdDetail")["OrderID",DataRowVersion.Original];
                   args.Row.AcceptChanges();
                   // Store the actual primary key value in the foreign key column of the child row.
                   args.Row["OrderID"] = currentkey;
               }
   
               if(args.StatementType == StatementType.Delete )
                   args.Status = UpdateStatus.SkipCurrentRow;
   
           }
           private DataRow [] GetDeletedRows(DataTable dt)
           {
               DataRow [] dr ;
               if(dt == null)
                   return null;
               dr = dt.Select("","",DataViewRowState.Deleted );
               if(dr.Length ==0 || dr[0] != null)
                   return dr;
               // Workaround:
               // With a remoted DataSet, Select returns the array elements
               // that are filled with Nothing/null instead of DataRow objects.
   					
               for(int i=0; i < (int)dt.Rows.Count; i++)
               {
                   if(dt.Rows[i].RowState ==DataRowState.Deleted )
                       dr[i]=dt.Rows[i];
               }
               return dr;
   						
   
           } 
   					

5. Èçìåíèòå ñòðîêè SqlConnection äëÿ ïîäêëþ÷åíèÿ ê ñåðâåðó íà êîòîðîì çàïóùåí SQL Server.

Òåñòèðîâàíèå âåá-ñëóæáû

1. Íàæìèòå êëàâèøó F5 äëÿ êîìïèëÿöèè è çàïóñêà âåá-ñëóæáû. Îáðàòèòå âíèìàíèå, ÷òî âåá-ñòðàíèöû âîçâðàùàåòñÿ ñ URL-àäðåñà http://localhost/CSharpUpdateData/Service1.asmx. Â ýòîé âåá-ñòðàíèöû ìîæíî âçàèìîäåéñòâèå ñ âåá-ñëóæáû â îáîçðåâàòåëå Internet Explorer.
2. Service1.asmx âåá-ñòðàíèöû íàæìèòå êíîïêó GetData. Îáðàòèòå âíèìàíèå, ÷òî âîçâðàùàåìûé âåá-ñòðàíèöû, îòîáðàæàþùåé ïîäðîáíûå ñâåäåíèÿ î î÷åðåäü GetData Âåá-ìåòîäà.
3. Çàêðîéòå âåá-ñòðàíèö.

Ñîçäàíèå êëèåíòñêîãî ïðèëîæåíèÿ

1. Ñîçäàíèå íîâîãî ïðîåêòà ïðèëîæåíèÿ Windows Visual C# âûïîëíèòå ñëåäóþùèå äåéñòâèÿ.
   1. Íà Ôàéë ìåíþ Visual Studio.NET, âûáåðèòå ïóíêò Íîâûé, à çàòåì íàæìèòå êíîïêó Ïðîåêò.
   2.  Íîâûé ïðîåêò äèàëîãîâîå îêíî, íàæìèòå êíîïêó Ïðîåêòû Visual C# Èç ñïèñêà Òèïû ïðîåêòîâ, à çàòåì íàæìèòå êíîïêó Ïðèëîæåíèå Windows Èç ñïèñêà Øàáëîíû. Ïî óìîë÷àíèþ Form1 äîáàâëÿåòñÿ â ïðîåêò.
2. Ïåðåòàñêèâàíèå Êíîïêà ýëåìåíò óïðàâëåíèÿ è DataGrid ýëåìåíò óïðàâëåíèÿ èç ïàíåëè ýëåìåíòîâ â ôîðìó Form1. Ïî óìîë÷àíèþ, ýëåìåíòû óïðàâëåíèÿ ñ èìåíåì Button1 è dataGrid1 ñîîòâåòñòâåííî.
3. Èçìåíåíèå Èìÿ ñâîéñòâà ýëåìåíòà Button1 Êîìó btnSave, à çàòåì èçìåíèòå Òåêñò ñâîéñòâà ýëåìåíòà Button1 Êîìó Ñîõðàíèòü.
4. Íà Ïðîåêò ìåíþ, íàæìèòå êíîïêó Äîáàâëåíèå âåá-ññûëêè. Ââåäèòå URL-àäðåñ äëÿ âåá-ñëóæáû (â äàííîì ñëó÷àå òèïhttp://localhost/CSharpUpdateData/Service1.asmx), íàæìèòå êëàâèøó Ââåäèòå, à çàòåì íàæìèòå êíîïêó Äîáàâèòü ññûëêó. Îáðàòèòå âíèìàíèå, ÷òî â ðåøåíèè ïîÿâèòñÿ çàïèñü äëÿ ýòîãî âåá-ññûëêè Îáîçðåâàòåëü.
5. Äîáàâüòå ñëåäóþùèé êîä â êëàññ Form1 äëÿ îáúÿâëåíèÿ ÷ëåí íà óðîâíå ôîðìû äëÿ Íàáîð äàííûõ:

          private System.Data.DataSet ds;
   					

6. Äâàæäû ùåëêíèòå ôîðìó, ÷òîáû ïåðåéòè â ïðåäñòàâëåíèå «êîä» è îáðàòèòå âíèìàíèå Visual Studio.NET ñîçäàåò Form1_Load ìåòîä. Äîáàâüòå ñëåäóþùèé êîä äëÿ Form1_Load ìåòîä:

       localhost.Service1 sv = new localhost.Service1();
       ds = sv.GetData ();
          
       dataGrid1.DataSource = ds;
       dataGrid1.DataMember = "Orders";
   					

7. Ïåðåêëþ÷èòåñü â ðåæèì ôîðìû.
8. Îòêðîéòå îêíî ïðîãðàììíîãî êîäà Ñîõðàíèòü Êíîïêà, à çàòåì äîáàâüòå ñëåäóþùèé êîä äëÿbtnSave_Click îáðàáîò÷èê ñîáûòèé:

       localhost.Service1 sv = new localhost.Service1();
   
       DataSet MyChangedRows;
   
       dataGrid1.DataMember = "";
       dataGrid1.DataSource = null;
       //Pull out only what you must send over the wire.
       MyChangedRows = ds.GetChanges();
       MyChangedRows = sv.UpdateData(MyChangedRows);
   
       //You must accept changes on the DataSet because of a known problem. See Q313540.
   
       ds.AcceptChanges();
   
       ds.EnforceConstraints = false;
   
       //Merge in the parent rows first and then the child rows.
       ds.Merge(MyChangedRows.Tables["Orders"], false, MissingSchemaAction.Ignore);
       ds.Merge(MyChangedRows.Tables["details"], false, MissingSchemaAction.Ignore);
   
       //Accept changes that you have made to the DataSet.
       ds.AcceptChanges();
       //Turn on the integrity constraints that you turned off earlier.
       ds.EnforceConstraints = true;
   
       dataGrid1.DataSource = ds;
       dataGrid1.DataMember = "Orders";
   					

Òåñòèðîâàíèå êëèåíòñêîãî ïðèëîæåíèÿ

1. Íàæìèòå êëàâèøó F5 äëÿ êîìïèëÿöèè è çàïóñêà êëèåíòà ïðèëîæåíèÿ.
2. Â dataGrid1, èçìåíèòü íåêîòîðûå äàííûå è íàæìèòå êíîïêó Ñîõðàíèòü.
   
   ÏÐÈÌÅ×ÀÍÈÅ: Íå èçìåíÿéòå ïîëå êëþ÷à. Ïðè èçìåíåíèè ïîëÿ êëþ÷à, ïîÿâëÿåòñÿ ñîîáùåíèå îá îøèáêå, ãîâîðèòñÿ, ÷òî ðàçáèåíèè ññûëîê öåëîñòíîñòü íà ñåðâåðå.
3. Äîáàâëåíèå äî÷åðíåé ñòðîêè ê ñóùåñòâóþùåé ðîäèòåëüñêîé ñòðîêè. Îáðàòèòå âíèìàíèå, ÷òî Äî÷åðíÿÿ ñòðîêà àâòîìàòè÷åñêè ïîëó÷àåò ïðàâèëüíûå çíà÷åíèÿ âíåøíåãî êëþ÷à.
4. Äîáàâüòå ñëåäóþùèå ïàðàìåòðû äëÿ íîâîé ðîäèòåëüñêîé ñòðîêè è íîâûé äî÷åðíèå ñòðîêè, à çàòåì íàæìèòå Ñîõðàíèòü. Îáðàòèòå âíèìàíèå íà ñëåäóþùèå êëþ÷åâûå çíà÷åíèÿ:
   • Orders.CustomerID=RATTC
   • Orders.EmployeeID=1
   • Orders.OrderDate=2/2/2002
   • Details.ProductID=1
   • Details.UnitPrice=18
   • Details.Quantity=1
   • Details.Discount=0

Äëÿ äîïîëíèòåëüíîé ñâåäåíèÿ, ùåëêíèòå ñëåäóþùèé íîìåð ñòàòüè äëÿ ïðîñìîòðà ñòàòüè â Áàçà çíàíèé êîðïîðàöèè Ìàéêðîñîôò: