/// <summary> /// 更新数据 /// </summary> /// <param name="sqlstring"></param> /// <param name="data"></param> /// <returns></returns> public bool UpdateData(string sqlstring, System.Data.DataTable data) { if (_Idbcmd == null) return false; try { _Idbcmd.CommandText = sqlstring; if (_Idbcmd.Connection.State != ConnectionState.Open) { _Idbcmd.Connection.Open(); } //此举可解决连续多个不同表在插入或更新是抛出的异常 //Table不存在*列等,即使DataAdapter更换了Select,但是Insert,Update却无法更新 if (_DA != null && _ilsc != null) { _DA.Dispose(); _DA = _ilsc.GetDbDataAdapter(); } else { return false; } _DA.SelectCommand = _Idbcmd; _ldbcbuid.DataAdapter = _DA; _DA.InsertCommand = _ldbcbuid.GetInsertCommand(); _DA.UpdateCommand = _ldbcbuid.GetUpdateCommand(); _DA.Update(data); data.AcceptChanges(); return true; } catch { return false; } }
/// <summary> /// DataSet 更新是需要注意各个表之间的关系 /// </summary> /// <param name="sqlstring"></param> /// <param name="data"></param> /// <returns></returns> public bool UpdateData(string sqlstring, System.Data.DataSet data) { if (_Idbcmd == null) return false; try { _Idbcmd.CommandText = sqlstring; if (_Idbcmd.Connection.State != ConnectionState.Open) { _Idbcmd.Connection.Open(); } int i = 0; foreach (DataTable dt in data.Tables) { if (i == 0) { _DA.TableMappings.Add("Table", dt.TableName); } else { _DA.TableMappings.Add(string.Format("Table{0}", i), dt.TableName); } i++; } _ldbcbuid.DataAdapter = _DA; _DA.InsertCommand = _ldbcbuid.GetInsertCommand(); _DA.UpdateCommand = _ldbcbuid.GetUpdateCommand(); //_DA.DeleteCommand = _ldbcbuid.GetDeleteCommand(); for ( i=0;i<_DA.TableMappings.Count ;i++) { _DA.Update(data,_DA.TableMappings[i].SourceTable ); } data.AcceptChanges(); _DA.TableMappings.Clear(); return true; } catch(Exception ex) { Message.Invoke(this, new DataOperationErrorMessageArgs(ex.Message)); return false; } }
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 CargarDataset(System.Data.DataSet ds) { foreach (System.Collections.Generic.KeyValuePair<string, System.Collections.Generic.List<IntelliTrack.Client.Application.Dataset.ReporteRegadoresDataset.ReportElement>> pair in info) { foreach (ReportElement element in pair.Value) { System.Data.DataRow dataRow = ds.Tables[0].NewRow(); if(element.ID.HasValue) dataRow["ID"] = element.ID; dataRow["IDTransponder"] = element.IDTransponder; dataRow["IDElemento"] = element.IDElemento; dataRow["Categoria"] = element.Categoria; dataRow["Equipo"] = element.Equipo; if (element.DIA_HORA.HasValue) dataRow["DIA_HORA"] = element.DIA_HORA; if(element.DIA_HORA_Final.HasValue) dataRow["DIA_HORA_Final"] = element.DIA_HORA_Final; if (element.Duracion.HasValue) dataRow["Duracion"] = element.Duracion; dataRow["Latitud"] = element.Latitud; dataRow["Longitud"] = element.Longitud; dataRow["Velocidad"] = element.Velocidad; dataRow["Curso"] = element.Curso; dataRow["NOM_PUNTO"] = element.NOM_PUNTO; dataRow["NOM_CAMINO"] = element.NOM_CAMINO; dataRow["NOM_AREA"] = element.NOM_AREA; dataRow["NOM_FRENTE"] = element.NOM_FRENTE; dataRow["TAG_EQUIPO"] = element.TAG_EQUIPO; dataRow["TRANSP_VOLT"] = element.TRANSP_VOLT; dataRow["TRANSP_TEMP"] = element.TRANSP_TEMP; dataRow["SENTIDO"] = element.SENTIDO; dataRow["TiempoCarga"] = element.TiempoCarga; dataRow["TiempoCargaSegundos"] = (int)element.TiempoCarga.TotalSeconds; dataRow["TiempoDetenido"] = element.TiempoDetenido; dataRow["TiempoDetenidoSegundos"] = (int)element.TiempoDetenido.TotalSeconds; dataRow["TiempoTransito"] = element.TiempoTransito; dataRow["TiempoTransitoSegundos"] = (int)element.TiempoTransito.TotalSeconds; dataRow["Estado"] = element.Estado; dataRow["VelocidadMaxima"] = element.VelocidadMaxima; dataRow["VelocidadPromedio"] = element.VelocidadPromedio; dataRow["ReferenciaSalida"] = element.ReferenciaSalida; dataRow["ReferenciaCaminos"] = element.ReferenciaCaminos; dataRow["ReferenciaLlegada"] = element.ReferenciaLlegada; dataRow["DistanciaRecorrida"] = element.DistanciaRecorrida; ds.Tables[0].Rows.Add(dataRow); } } ds.AcceptChanges(); }
public static System.Data.DataTable calcBaseSubsidy(System.Data.DataTable dt, System.Data.DataTable anc, string option, double modalAdj) { Worksheet worksheet = (Worksheet) AxCalcEngine.AxCalcEngine.shts["Projection Input Options"]; worksheet.Activate(); dt.Columns.Add("Subsidy"); dt.Columns.Add("EE Contribution"); double num = double.Parse(worksheet.Range("Sub").Value.ToString()); double num2 = double.Parse(worksheet.Range("Min").Value.ToString()) * modalAdj; double num3 = double.Parse(worksheet.Range("Max").Value.ToString()) * modalAdj; string str = option; if (str != null) { double num4; double num5; double num6; double num7; double num8; if (!(str == "National")) { System.Data.DataTable table; if (str == "Regional") { anc.Columns["Premium"].ColumnName = "AncPrem"; anc.Columns.Remove("Metallic Level"); anc.Columns.Remove("Coverage Tier"); anc.Columns.Remove("AV"); anc.Columns.Remove("Carrier"); anc.Columns.Add("Base Subsidy", typeof(double)); anc.AcceptChanges(); foreach (DataRow row in anc.Rows) { row["Base Subsidy"] = double.Parse(row["AncPrem"].ToString()) - (num * modalAdj); } anc.Columns.Remove("AncPrem"); table = Join(dt, anc); foreach (DataRow row in table.Rows) { num7 = double.Parse(row["Factor"].ToString()); num8 = double.Parse(row["Premium"].ToString()); num4 = double.Parse(row["Base Subsidy"].ToString()) * num7; num6 = num8 - num4; num6 = Math.Min(Math.Max(0.0, num6), num8); num5 = num8 - num6; row["Subsidy"] = num5; row["EE Contribution"] = num6; } table.Columns.Remove("Base Subsidy"); return table; } if (str != "Percent of Anchored Plan") { return dt; } anc.Columns["Premium"].ColumnName = "AncPrem"; anc.Columns.Remove("Metallic Level"); anc.Columns.Remove("Coverage Tier"); anc.Columns.Remove("AV"); anc.Columns.Remove("Carrier"); anc.Columns.Add("Base Subsidy"); anc.AcceptChanges(); foreach (DataRow row in anc.Rows) { row["Base Subsidy"] = double.Parse(row["AncPrem"].ToString()) * num; } anc.Columns.Remove("AncPrem"); table = Join(dt, anc); foreach (DataRow row in table.Rows) { num7 = double.Parse(row["Factor"].ToString()); num8 = double.Parse(row["Premium"].ToString()); num4 = double.Parse(row["Base Subsidy"].ToString()) * num7; num6 = num8 - num4; num6 = Math.Min(Math.Max(0.0, num6), num8); num5 = num8 - num6; row["Subsidy"] = num5; row["EE Contribution"] = num6; } table.Columns.Remove("Base Subsidy"); return table; } foreach (DataRow row in dt.Rows) { num4 = num * modalAdj; num7 = double.Parse(row["Factor"].ToString()); num8 = double.Parse(row["Premium"].ToString()); num4 *= num7; num6 = num8 - num4; num6 = Math.Min(Math.Min(num6, num3), num8); num6 = Math.Max(Math.Max(num2, num6), 0.0); num5 = num8 - num6; row["Subsidy"] = num5; row["EE Contribution"] = num6; } } return dt; }
public bool WriteExcelDateDatabase(ref System.Data.DataTable dt) { bool IsSuccess = false; Excel.Application exapp = new Excel.Application(); ; //'定义excel应用程序 Excel.Workbook exbook = null;// '定义工作簿 Excel.Worksheet exsheet2 = null;// 数据工作簿 Excel.Worksheet exsheet1 = null;//指导工作 System.Array w_ArrayData = null;//需要导出的数组 int w_ExcelMaxRow = 0;//excel最大行 int w_ExcelMaxCol = 0;//excel最大列 ////区域信息 //Object oBooks = exapp.Workbooks; //System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("ja-JP"); //oBooks.GetType().InvokeMember("Add", System.Reflection.BindingFlags.InvokeMethod, null, oBooks, null, ci); try { exapp.Visible = false; exapp.UserControl = true; //打开excel exbook = exapp.Workbooks.Open(FileName, Missing, Missing, Missing, Missing, Missing, Missing , Missing, Missing, Missing, Missing, Missing, Missing, Missing, Missing); //从excel中获取sheet数据 foreach (Excel.Worksheet item in exbook.Worksheets) { if (item.Name == Sheet2Name) { if (exsheet2 == null) exsheet2 = item; } //else if (item.Name == Sheet1Name) //{ // if (exsheet1 == null) // exsheet1 = item; //} //if (exsheet1 != null && exsheet2 != null) // break; if (exsheet2 != null) break; } //读取使用的最大的行数/列 w_ExcelMaxRow = exsheet2.UsedRange.Rows.Count; w_ExcelMaxCol = exsheet2.UsedRange.Columns.Count; w_ArrayData = (System.Array)exsheet2.get_Range(exsheet2.Cells[StartRowIndex, 1], exsheet2.Cells[w_ExcelMaxRow, w_ExcelMaxCol]).Value2; //for (int i = 0; i < w_ArrayData.Length; i++) //{ // string tt = w_ArrayData.GetValue(0, 2); //} DataRow dr; for (int i = 0; i < w_ExcelMaxRow - StartRowIndex + 1; i++) { dr = dt.NewRow(); for (int j = 0; j < dt.Columns.Count; j++) { dr[j] = w_ArrayData.GetValue(i + 1, j + 1); } dt.Rows.Add(dr); } dt.AcceptChanges(); //退出excel exbook.Close(false, Missing, Missing); IsSuccess = true; } catch (Exception ex) { throw ex; } finally { exbook = null; exapp.Quit(); exapp = null; GC.Collect(); } return IsSuccess; }
/// <summary> /// using the DataSet and DataTable names. /// </summary> /// <param name="dataSet">A DataSet to fill with records and, if necessary, schema.</param> /// <param name="tableName">The name of the source table to use for table mapping.</param> /// <returns>The number of rows successfully added to or refreshed in the DataSet. This does not include rows affected by statements that do not return rows.</returns> public int Fill(System.Data.DataSet dataSet, string tableName) { //table within the dataset DataTable dt; if(dataSet.Tables.Contains(tableName)) { dt = dataSet.Tables[tableName]; } else { dt = dataSet.Tables.Add(tableName); } //store records affected int records = 0; //raw row string rawrow; //store the current row string[] rowbuffer = new string[0]; //more records bool morerecords = true; //open stream to read from file m_reader = new StreamReader(m_filename); if(m_hasheader) { //read header row and construct schema m_headers = SplitRow(m_reader.ReadLine()); foreach(string column in m_headers) { //add to columns collection DataColumn dc = dt.Columns.Add(column); dc.DataType = typeof(string); dc.AllowDBNull = true; } //read first data row //Updated (Bug# 000091) Failure if only header row present rawrow = m_reader.ReadLine(); //check for null first - avoid throwing exception if(rawrow==null | rawrow==String.Empty) { morerecords = false; } else { rowbuffer = SplitRow(rawrow); } } else { //read line rawrow = m_reader.ReadLine(); if(rawrow==null | rawrow==String.Empty) { morerecords = false; } else { //read the first row and get the length rowbuffer = SplitRow(rawrow); } for(int iColumn = 0; iColumn < rowbuffer.Length; iColumn++) { //add to columns collection DataColumn dc = dt.Columns.Add("Column " + iColumn.ToString()); dc.DataType = typeof(string); dc.AllowDBNull = true; } } //processing of further rows goes here while(morerecords) { //increment rows affected records++; //add values to row and insert into table dt.Rows.Add(rowbuffer); //read the next row rawrow = m_reader.ReadLine(); if(rawrow==null | rawrow==String.Empty) { morerecords = false; } else { //read the first row and get the length rowbuffer = SplitRow(rawrow); } } //close stream m_reader.Close(); //mark dataset as up-to-date dataSet.AcceptChanges(); return records; }
public static void UpdateDataset(System.Data.SqlClient.SqlCommand insertCommand, System.Data.SqlClient.SqlCommand deleteCommand, System.Data.SqlClient.SqlCommand updateCommand, System.Data.DataSet dataSet, string tableName) { if (insertCommand == null) throw new System.ArgumentNullException("insertCommand"); if (deleteCommand == null) throw new System.ArgumentNullException("deleteCommand"); if (updateCommand == null) throw new System.ArgumentNullException("updateCommand"); if (dataSet == null) throw new System.ArgumentNullException("dataSet"); if ((tableName == null) || (tableName.Length == 0)) throw new System.ArgumentNullException("tableName"); using (System.Data.SqlClient.SqlDataAdapter sqlDataAdapter = new System.Data.SqlClient.SqlDataAdapter()) { sqlDataAdapter.UpdateCommand = updateCommand; sqlDataAdapter.InsertCommand = insertCommand; sqlDataAdapter.DeleteCommand = deleteCommand; sqlDataAdapter.Update(dataSet, tableName); dataSet.AcceptChanges(); } }
void DataModell_SaveAllChangesCall(object Sender, System.Data.DataSet AllDataDataSet) { DataSet Changes = AllDataDataSet.GetChanges (); if (Changes == null) return; DataBase.SetCommonDataSet (Changes); AllDataDataSet.AcceptChanges (); }