/// <summary> /// Convert collection to dataset. /// </summary> /// <returns>DataSet</returns> public System.Data.DataSet ToDataSet() { System.Data.DataSet ds = new System.Data.DataSet(); for (int i = 0; i < ContainsType.Length; i++) { ds.Tables.Add(new System.Data.DataTable(ContainsType[i].Name)); System.Data.DataTable dt = ds.Tables[i]; for (int j = 0; j < this.InnerList.Count; j++) { if (this.InnerList[j].GetType() == ContainsType[i]) { OrcaLogic.Reflection.PropertyTable pt = OrcaLogic.Reflection.ReflectionHelper.GetProperties(InnerList[j]); PropertyInfo[] propertyInfo = pt.GetSimpleMembers(); if (dt.Columns.Count == 0) { BuildColumnCollection(propertyInfo, dt); } System.Data.DataRow dr = dt.NewRow(); dr.ItemArray = this.GetItemArray(propertyInfo, this.InnerList[j]); dt.Rows.Add(dr); } } } ds.AcceptChanges(); return(ds); }
static void CreateDemoData(InMemoryDataStore inMemoryDataStore) { var ds = new System.Data.DataSet(); using (var ms = new System.IO.MemoryStream()) { using (var writer = System.Xml.XmlWriter.Create(ms)) { inMemoryDataStore.WriteXml(writer); writer.Flush(); } ms.Flush(); ms.Position = 0; ds.ReadXml(ms); } var gen = new GenHelper(); var idsAccount = new List <string>(); var dtAccounts = ds.Tables["Accounts"]; for (int i = 0; i < 200; i++) { var id = gen.MakeTosh(20); idsAccount.Add(id); dtAccounts.Rows.Add(id, gen.GetFullName()); } var dtMessages = ds.Tables["Messages"]; for (int i = 0; i < 5000; i++) { var id1 = gen.Next(idsAccount.Count); var id2 = gen.Next(idsAccount.Count - 1); dtMessages.Rows.Add(null, GenHelper.ToTitle(gen.MakeBlah(gen.Next(7))), gen.MakeBlahBlahBlah(5 + gen.Next(100), 7), idsAccount[id1], idsAccount[(id1 + id2 + 1) % idsAccount.Count]); } ds.AcceptChanges(); using (var ms = new System.IO.MemoryStream()) { ds.WriteXml(ms, System.Data.XmlWriteMode.WriteSchema); ms.Flush(); ms.Position = 0; using (var reader = System.Xml.XmlReader.Create(ms)) { inMemoryDataStore.ReadXml(reader); } } }
private static void BinReadDataSetFromStream(System.IO.Stream stream, System.Data.DataSet ds, bool mergeSchema) { //Version int version; version = IO.StreamPersistence.ReadInt32(stream); if (version != c_BinaryVersion) { throw new BinaryDataSetVersionException(); } //Schema byte[] System.Data.DataSet schemaDS; //Questo dataset viene usato solo per leggere lo schema byte[] byteSchema = IO.StreamPersistence.ReadByteArray(stream); using (System.IO.MemoryStream schemaStream = new System.IO.MemoryStream(byteSchema)) { if (mergeSchema) { ds.ReadXmlSchema(schemaStream); schemaDS = ds; } else { schemaDS = new System.Data.DataSet(); schemaDS.ReadXmlSchema(schemaStream); } } //Tables for (int iTable = 0; iTable < schemaDS.Tables.Count; iTable++) { System.Data.DataTable schemaTable = schemaDS.Tables[iTable]; //Table exist on the destination if (ds.Tables.Contains(schemaTable.TableName)) { System.Data.DataTable destinationTable = ds.Tables[schemaTable.TableName]; int rowsCount = IO.StreamPersistence.ReadInt32(stream); //Rows for (int r = 0; r < rowsCount; r++) { System.Data.DataRow row = destinationTable.NewRow(); //Columns for (int c = 0; c < schemaTable.Columns.Count; c++) { string colName = schemaTable.Columns[c].ColumnName; object val = BinReadFieldFromStream(stream, schemaTable.Columns[c].DataType); //Il valore viene comunque letto per far avanzare lo stream if (destinationTable.Columns.Contains(colName)) { row[colName] = val; } } destinationTable.Rows.Add(row); } } else //Note: if the table not exist I will read anyway the columns and rows to correctly position the stream on the next table { int rowsCount = IO.StreamPersistence.ReadInt32(stream); //Rows for (int r = 0; r < rowsCount; r++) { //Columns for (int c = 0; c < schemaTable.Columns.Count; c++) { BinReadFieldFromStream(stream, schemaTable.Columns[c].DataType); //Il valore viene comunque letto per far avanzare lo stream } } } } ds.AcceptChanges(); }
private void Update(string address) { ObjectBE[] copy = updateList.ToArray(); updateList.Clear(); foreach (ObjectBE obe in copy) { try { obe.BeforeUpdate(); } catch (Exception x1) { throw new AtriumException("Update stopped on " + obe.myDT.TableName + ". Reject changes will restore Datatables consistency with database.\r\n", x1); } if (obe.myDT.HasErrors) { throw new AtriumException("Update stopped on " + obe.myDT.TableName + ". Reject changes will restore Datatables consistency with database.\r\n" + obe.ErrorsForTable(obe.myDT)); } } string tableNm = ""; System.Data.DataSet dsDown = null; try { System.Data.DataSet dsUp = new System.Data.DataSet(); List <string> tables = new List <string>(); foreach (ObjectBE obe in copy) { System.Data.DataTable dt = obe.myDT.GetChanges(); if (dt != null) { dt.ExtendedProperties.Remove("BE"); if (!obe.IsVirtual) { //extend this scheme to include the obe.MyMNg.CurrentFileid //this way we can put the returned datatable into the right obe object //we will need to rename the datatables in the server code to include this info if (obe.DALName == "") { tables.Add(dt.TableName); } else { tables.Add(dt.TableName + "." + obe.DALName); } } if (!dsUp.Tables.Contains(dt.TableName)) { //TFS#51279 CJW 2013-8-30 - Deal with time zone off sets set by web service foreach (System.Data.DataColumn column in dt.Columns) { if (column.DataType == typeof(DateTime)) { //column.DateTimeMode = System.Data.DataSetDateTime.Unspecified; } } //End TFS #51279 dsUp.Tables.Add(dt); } } } if (myMng.AppMan.Compression) //compression on { byte[] byteUp = BEManager.CompressData(dsUp); //dsUp.Dispose(); //dsUp = null; dsUp.Clear(); dsUp.AcceptChanges(); if (!myMng.AppMan.UseService) { byteUp = this.myMng.DAL.Update(tables, byteUp); } else { byteUp = myMng.AppMan.AtriumX().UpdateComp(myMng.AppMan.Connect, myMng.AppMan.myPwd, myMng.AppMan.myUser, tables.ToArray(), byteUp); } dsDown = BEManager.DecompressDataSet(byteUp, dsUp); } else //compression ffn { if (!myMng.AppMan.UseService) { dsDown = this.myMng.DAL.Update(tables, dsUp); } else { dsUp.RemotingFormat = System.Data.SerializationFormat.Binary; dsDown = myMng.AppMan.AtriumX().Update(myMng.AppMan.Connect, myMng.AppMan.myPwd, myMng.AppMan.myUser, tables.ToArray(), dsUp); } } } catch (Exception x) { if (x.InnerException != null) { if (x.InnerException.GetType() == typeof(System.Data.SqlClient.SqlException)) { System.Data.SqlClient.SqlException sqle = (System.Data.SqlClient.SqlException)x.InnerException; switch (sqle.Number) { case 2601: throw new Exception(Properties.Resources.DuplicateValueInAUniqueColumn, sqle); case 50000: throw new AtriumException(sqle.Message); default: throw sqle; } } else if (x.InnerException.GetType() == typeof(System.Data.DBConcurrencyException)) { System.Data.DBConcurrencyException dbce = (System.Data.DBConcurrencyException)x.InnerException; throw new ConcurrencyException(String.Format(Properties.Resources.ConcurrencyError, ""), x); } else { throw x; } } else { throw new UpdateFailedException("Update failed on " + tableNm + ". Datatables need to be reloaded as acceptchanges/merge has been called on them.\r\n", x); } } //TFS#51508 CJW 2013-09-19 //all updates have succeeded //merge data back into client datasets //and accept changes bool isError = false; Exception x5 = null; foreach (ObjectBE obe in copy) { tableNm = obe.myDT.TableName; if (dsDown.Tables.Count > 0 && dsDown.Tables.Contains(tableNm)) { try { //hack required because we remove the Contents column on the server to speed up the download //if we leave it in it gets blanked if (tableNm.ToLower() == "doccontent") { dsDown.Tables[tableNm].Columns.Remove("Contents"); }//end of hack if (!obe.IsVirtual) { obe.Fill(dsDown.Tables[tableNm]); } obe.AfterUpdate(); } catch (System.InvalidOperationException x) { //trap for Corrupt datatable index '5' isError = true; x5 = x; } //acceptchanges must be called last so that afterupdate knows which rows o process obe.myDT.AcceptChanges(); } } if (isError) { throw new AtriumException("An unexpected error has occurred. Please close and restart Atrium.", x5); } }
static void compareAndUpdateData() { string conStr = @"Data Source=.\SQLExpress;Initial Catalog=FactoryFloorKeyStore_2;Integrated Security=True"; string selectCommandText = "select * from ProductKeyInfo"; string dataSetXmlFile = @"D:\backup\RDBMSXmlTest2_2.xml"; System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(conStr); System.Data.SqlClient.SqlCommand selectCmd = new System.Data.SqlClient.SqlCommand(selectCommandText); selectCmd.Connection = conn; System.Data.SqlClient.SqlDataAdapter adapter = new System.Data.SqlClient.SqlDataAdapter(selectCmd); System.Data.SqlClient.SqlCommandBuilder builder = new System.Data.SqlClient.SqlCommandBuilder(adapter); if (conn.State != System.Data.ConnectionState.Open) { conn.Open(); } System.Data.DataSet dataSet = new System.Data.DataSet(); dataSet.ReadXml(dataSetXmlFile); dataSet.AcceptChanges(); System.Data.DataSet dataSet2 = new System.Data.DataSet(); adapter.Fill(dataSet2); //dataSet2.Merge(dataSet); //dataSet.Merge(dataSet2); //dataSet2 = dataSet.Clone(); List <string> dpkids = new List <string>(); foreach (System.Data.DataRow row in dataSet.Tables[0].Rows) { foreach (System.Data.DataRow row2 in dataSet2.Tables[0].Rows) { if (row["ProductKeyID"].ToString() == row2["ProductKeyID"].ToString()) { dpkids.Add(row["ProductKeyID"].ToString()); } } } int count = dataSet.Tables[0].Rows.Count;//dataSet2.Tables[0].Rows.Count; //for (int i = 0; i < count; i++) //{ // if (!dpkids.Contains(dataSet2.Tables[0].Rows[i]["ProductKeyID"].ToString())) // { // dataSet2.Tables[0].Rows[i].Delete(); // //dataSet2.Tables[0].Rows[i].AcceptChanges(); // Console.WriteLine(dataSet2.Tables[0].Rows[i].RowState); // //i--; // //count--; // } // else // { // dataSet2.Tables[0].Rows[i]["CreatedBy"] = "Rally"; // Console.WriteLine(dataSet2.Tables[0].Rows[i].RowState); // } //} for (int i = 0; i < count; i++) { //if (!dpkids.Contains(dataSet.Tables[0].Rows[i]["ProductKeyID"].ToString())) //{ // dataSet.Tables[0].Rows[i].Delete(); // //dataSet2.Tables[0].Rows[i].AcceptChanges(); // Console.WriteLine(dataSet.Tables[0].Rows[i].RowState); // //i--; // //count--; //} //else //{ // dataSet.Tables[0].Rows[i]["CreatedBy"] = "Rally"; // //dataSet.Tables[0].Rows[i].SetModified(); // Console.WriteLine(dataSet.Tables[0].Rows[i].RowState); //} } //dataSet2.Tables[0].AcceptChanges(); //dataSet2.AcceptChanges(); //int result = adapter.Update(dataSet); Console.WriteLine(builder.GetDeleteCommand().CommandText); Console.WriteLine(builder.GetInsertCommand().CommandText); Console.WriteLine(builder.GetUpdateCommand().CommandText); //int result = adapter.Update(dataSet2); //int result = adapter.Update(dataSet); foreach (System.Data.DataColumn col in dataSet2.Tables[0].PrimaryKey) { Console.WriteLine(col.ColumnName); } if (conn.State != System.Data.ConnectionState.Closed) { conn.Close(); } //Console.WriteLine(result); Console.Read(); }