/// <summary> /// Creates the necessary rows for a message in the MSGS table /// </summary> /// <param name="msgTag">The message name</param> /// <param name="srcUnitId">The source identifier</param> /// <param name="dstUnitId">The destination identifier</param> /// <param name="configDs">The message configuration</param> /// <param name="msgBody">The text of the message</param> public void CreateFromConfig(string msgTag, decimal srcUnitId, decimal dstUnitId, DataSet configDs, string msgBody) { if (configDs != null && configDs.Tables.Count > 0 && configDs.Tables[0].Rows != null && configDs.Tables[0].Rows.Count > 0) { DataSet msgsDs = null; IDbDataAdapter adapter = null; try { /// Create messages with configuration values adapter = GetAdapter(); msgsDs = new DataSet(); adapter.FillSchema(msgsDs, SchemaType.Mapped); int count = configDs.Tables[0].Rows.Count; CmpMsgsDB msgDb = new CmpMsgsDB(); decimal id = msgDb.LastPKValue + 1; Util.CreateMessageRows(msgBody, configDs, msgsDs, ref id); adapter.Update(msgsDs); } catch (Exception ex) { Debug.WriteLine("Messages.CreateMessages - " + ex.Message); throw; } } }
public void ExtractTableParameters(string TableName, IDbDataAdapter adapter, out DatabaseCache InsertCache, out DatabaseCache DeleteCache, out DatabaseCache UpdateCache, out DatabaseCache IsExistCache, out DataTable dt ) { adapter.SelectCommand.CommandText = "select top 1 * from " + TableName; DataSet ds = new DataSet(); dt = adapter.FillSchema(ds, SchemaType.Source)[0]; dt.TableName = TableName; SqlCommandBuilder builder = new SqlCommandBuilder(adapter as SqlDataAdapter); builder.ConflictOption = ConflictOption.OverwriteChanges; //builder.SetAllValues = false; SqlCommand InsertCmd = builder.GetInsertCommand(true); builder.ConflictOption = ConflictOption.OverwriteChanges; InsertCache = new DatabaseCache(InsertCmd.CommandText, InsertCmd.Parameters); InsertCache.CurrentTable = dt; foreach (DataColumn c in dt.Columns) { if (c.AutoIncrement) { InsertCache.IsHaveAutoIncrement = true; InsertCache.SQL += ";Select @@IDENTITY;"; break; } } SqlCommand UpdateCmd = builder.GetUpdateCommand(true); UpdateCache = new DatabaseCache(UpdateCmd.CommandText, UpdateCmd.Parameters); UpdateCache.CurrentTable = dt; SqlCommand DeleteCmd = builder.GetDeleteCommand(true); DeleteCache = new DatabaseCache(DeleteCmd.CommandText, DeleteCmd.Parameters); DeleteCache.CurrentTable = dt; IsExistCache = new DatabaseCache(DeleteCmd.CommandText, DeleteCmd.Parameters); IsExistCache.CurrentTable = dt; IsExistCache.SQL = IsExistCache.SQL.Replace("DELETE FROM [" + TableName + "]", "Select count(1) from [" + TableName + "] with(nolock) "); }
/// <summary> /// テーブルスキーマを取得します。 /// </summary> /// <param name="adapter"></param> /// <returns></returns> internal static DataTable SelectTableSchema(this IDbDataAdapter adapter) { try { var ds = new DataSet(); adapter.FillSchema(ds, SchemaType.Source); return(ds.Tables[0]); } catch (SystemException e) { throw new DBHelperException(e, adapter.SelectCommand); } }
/// <summary> /// Fills the schema. /// </summary> /// <param name="dataSet">The data set.</param> public void FillSchema(DataSet dataSet) { TraceStart("FillSchema"); this.Prepare(); try { IDbDataAdapter adapter = DataAccess.Provider.CreateDataAdapter(); adapter.SelectCommand = Command; adapter.FillSchema(dataSet, SchemaType.Mapped); } finally { this.CloseConnection(false); TraceEnd("FillSchema"); } }
private DataTable LoadConfigData(DataSet ds, IDbDataAdapter da, bool fillSchema = false) { ds.Clear(); if (!fillSchema) { da.Fill(ds); } else { da.FillSchema(ds, SchemaType.Source); } ds.Tables[0].PrimaryKey = new DataColumn[] { ds.Tables[0].Columns[_dbGenericKeyValueConfigStorageParams.KEY_COLUMN_NAME] }; return(ds.Tables[0]); }
internal static DataSet GetEmptyEntityWithSchema(string selStr, string tableName, AbsDatabase db) { string key = selStr + db.GetRealOne().ConnectionString; if (hashEntitiesWithSchema.ContainsKey(key)) { return((hashEntitiesWithSchema[key] as DataSet).Clone()); } else { DataSet ds = new DataSet(); IDbDataAdapter adapter = db.GetDataAdapter(selStr); adapter.FillSchema(ds, SchemaType.Source); ds.Tables[0].TableName = tableName; hashEntitiesWithSchema[key] = ds; return(ds.Clone()); } }
protected virtual DataTable BuildSchemaTable(Boolean withGeometryColumn) { using (IDbConnection conn = DbUtility.CreateConnection(ConnectionString)) using (IDbCommand cmd = DbUtility.CreateCommand()) { cmd.CommandText = string.Format("SELECT * FROM {0} ", QualifiedTableName); cmd.CommandType = CommandType.Text; cmd.Connection = conn; IDbDataAdapter da = DbUtility.CreateAdapter(cmd); DataSet ds = new DataSet(); da.FillSchema(ds, SchemaType.Source); DataTable dt = ds.Tables[0]; //remove geometry column from schema if (!withGeometryColumn) { dt.Columns.Remove(GeometryColumn); } return(dt); } }
/// <summary> /// Adds a <see cref="T:System.Data.DataTable"/> named "Table" to the specified <see cref="T:System.Data.DataSet"/> and configures the schema to match that in the data source based on the specified <see cref="T:System.Data.SchemaType"/>. /// </summary> /// <param name="dataSet">The <see cref="T:System.Data.DataSet"/> to be filled with the schema from the data source.</param> /// <param name="schemaType">One of the <see cref="T:System.Data.SchemaType"/> values.</param> /// <returns> /// An array of <see cref="T:System.Data.DataTable"/> objects that contain schema information returned from the data source. /// </returns> public new DataTable[] FillSchema(DataSet dataSet, SchemaType schemaType) { return(_adapter.FillSchema(dataSet, schemaType)); }
/// <summary> /// Adds a <see cref="T:System.Data.DataTable"/> named "Table" to the specified <see cref="T:System.Data.DataSet"/> and configures the schema to match that in the data source based on the specified <see cref="T:System.Data.SchemaType"/>. /// </summary> /// <param name="dataSet">The <see cref="T:System.Data.DataSet"/> to be filled with the schema from the data source.</param> /// <param name="schemaType">One of the <see cref="T:System.Data.SchemaType"/> values.</param> /// <returns> /// An array of <see cref="T:System.Data.DataTable"/> objects that contain schema information returned from the data source. /// </returns> public new DataTable[] FillSchema(DataSet dataSet, SchemaType schemaType) => InternalAdapter.FillSchema(dataSet, schemaType);
//////////////////////////////////////////////////// public virtual IDataAdapter GetNewAdapter(DataRowState etatsAPrendreEnCompte, bool bDisableIdAuto, params string[] strExclusions) { IDbDataAdapter adapter = (IDbDataAdapter)m_connexion.GetTableAdapter(m_strNomTableInDb); if (m_connexion.IsInTrans()) { adapter.SelectCommand.Transaction = m_connexion.Transaction; } string strNomTableInContexte = m_connexion.GetNomTableInContexteFromNomTableInDb(m_strNomTableInDb); if (strNomTableInContexte == null) { strNomTableInContexte = m_strNomTableInDb; } adapter.TableMappings.Add("Table", strNomTableInContexte); DataSet ds = new DataSet(); adapter.FillSchema(ds, SchemaType.Mapped); DataTable table = ds.Tables[strNomTableInContexte]; if (table == null) { table = ds.Tables[m_strNomTableInDb]; if (table != null) { table.TableName = strNomTableInContexte; } } if ((etatsAPrendreEnCompte & DataRowState.Added) != 0) { adapter.InsertCommand = GetInsertCommand(table, adapter, bDisableIdAuto); } else { adapter.InsertCommand = m_connexion.GetConnexion().CreateCommand(); adapter.InsertCommand.CommandType = CommandType.Text; adapter.InsertCommand.CommandText = GetCommandQuiFaitRien(); adapter.InsertCommand.Transaction = m_connexion.Transaction; } if ((etatsAPrendreEnCompte & DataRowState.Deleted) != 0) { adapter.DeleteCommand = GetDeleteCommand(table, adapter); } else { adapter.DeleteCommand = m_connexion.GetConnexion().CreateCommand(); adapter.DeleteCommand.CommandType = CommandType.Text; adapter.DeleteCommand.CommandText = GetCommandQuiFaitRien(); adapter.DeleteCommand.Transaction = m_connexion.Transaction; } if ((etatsAPrendreEnCompte & DataRowState.Modified) != 0) { adapter.UpdateCommand = GetUpdateCommand(table, adapter); } else { adapter.UpdateCommand = m_connexion.GetConnexion().CreateCommand(); adapter.UpdateCommand.CommandType = CommandType.Text; adapter.UpdateCommand.CommandText = GetCommandQuiFaitRien(); adapter.UpdateCommand.Transaction = m_connexion.Transaction; } return(adapter); }
public void ExtractTableParameters(string TableName, IDbDataAdapter adapter, out DatabaseCache InsertCache, out DatabaseCache DeleteCache, out DatabaseCache UpdateCache, out DatabaseCache IsExistCache, out DataTable dt ) { MySqlDataAdapter adap = adapter as MySqlDataAdapter; adap.FillLoadOption = LoadOption.OverwriteChanges; adapter.SelectCommand.CommandText = "select * from " + TableName; DataSet ds = new DataSet(); dt = adapter.FillSchema(ds, SchemaType.Source)[0]; dt.TableName = TableName; InsertCache = new DatabaseCache(string.Empty, new IDataParameter[dt.Columns.Count]); DeleteCache = new DatabaseCache(string.Empty, new IDataParameter[dt.PrimaryKey.Length]); UpdateCache = new DatabaseCache(string.Empty, new IDataParameter[dt.Columns.Count]); IsExistCache = new DatabaseCache(string.Empty, new IDataParameter[dt.PrimaryKey.Length]); InsertCache.SQL = "insert into " + TableName + "("; DeleteCache.SQL = "delete from " + TableName + " where "; UpdateCache.SQL = "Update " + TableName + " set "; IsExistCache.SQL = "select count(1) from " + TableName + " where "; InsertCache.SQL += StringTools.JoinString(dt.Columns, string.Empty, string.Empty, ",", false); InsertCache.SQL += ") values("; InsertCache.SQL += StringTools.JoinString(dt.Columns, "?", string.Empty, ",", false); InsertCache.SQL += ");"; Dictionary <string, DataColumn> PrimaryKeys = new Dictionary <string, DataColumn>(); foreach (DataColumn c in dt.PrimaryKey) { if (c.AutoIncrement) { InsertCache.IsHaveAutoIncrement = true; } PrimaryKeys.Add(c.ColumnName, c); } string s = " 1=1 "; foreach (DataColumn c in dt.Columns) { if (!PrimaryKeys.ContainsKey(c.ColumnName)) { UpdateCache.SQL += c.ColumnName + "=?" + c.ColumnName + ","; } else { s += " And " + c.ColumnName + "=?" + c.ColumnName; } } if (UpdateCache.SQL.EndsWith(",")) { UpdateCache.SQL = UpdateCache.SQL.Remove(UpdateCache.SQL.Length - 1); } UpdateCache.SQL += " Where "; DeleteCache.SQL += s; UpdateCache.SQL += s; IsExistCache.SQL += s; if (InsertCache.IsHaveAutoIncrement == true) { InsertCache.SQL += "select last_insert_id();"; } InsertCache.Parameters = new IDataParameter[InsertCache.IsHaveAutoIncrement?dt.Columns.Count - 1:dt.Columns.Count]; DeleteCache.Parameters = new IDataParameter[PrimaryKeys.Count]; UpdateCache.Parameters = new IDataParameter[dt.Columns.Count]; IsExistCache.Parameters = new IDataParameter[PrimaryKeys.Count]; int InsertIndex = 0, DeleteIndex = 0, UpdateIndex = 0; foreach (DataColumn c in dt.Columns) { IDataParameter p = adapter.SelectCommand.CreateParameter(); p.ParameterName = "?" + c.ColumnName; p.SourceColumn = c.ColumnName; if (!c.AutoIncrement) { InsertCache.Parameters[InsertIndex++] = p; } if (PrimaryKeys.ContainsKey(c.ColumnName)) { DeleteCache.Parameters[DeleteIndex] = p; IsExistCache.Parameters[DeleteIndex] = p; DeleteIndex++; } UpdateCache.Parameters[UpdateIndex] = p; UpdateIndex++; } InsertCache.CurrentTable = UpdateCache.CurrentTable = DeleteCache.CurrentTable = IsExistCache.CurrentTable = dt; }
/// <summary> /// 数据导入. /// </summary> /// <param name="tableName"></param> public void DoImp(string tableName) { Console.WriteLine("从 XML 文件中,读取数据到 DataTable 里面。"); DataTable dtXml = new DataTable(); dtXml.ReadXmlSchema(GetXmlSchema(tableName)); dtXml.ReadXml(GetXml(tableName)); using (IDbConnection conn = GetDbConnection(ConnString)) { // 创建一个适配器 IDbDataAdapter adapter = GetDbDataAdapter(GetExpSql(tableName), conn); // 创建DataSet,用于存储数据. DataSet resultDataSet = new DataSet(); // 表结构定义导入DataSet. adapter.FillSchema(resultDataSet, SchemaType.Source); // 数据导入DataSet. adapter.Fill(resultDataSet); // 这些数据此时作为 DataSet 的 Tables 集合内独立的 DataTable 对象来提供。 // 如果在对 FillSchema 和 Fill 的调用中指定了一个表名, // 则可以使用该名称访问您需要的特定表。 DataTable dtDatabase = resultDataSet.Tables[0]; Console.WriteLine("将数据写入到数据库中..."); // 遍历 xml 文件中的每一行. foreach (DataRow xmlRow in dtXml.Rows) { // 从 DataTable 获取新的 DataRow 对象。 DataRow drCurrent = dtDatabase.NewRow(); // 每一列依次赋值. for (int i = 0; i < dtDatabase.Columns.Count; i++) { // 取得列名. string colName = dtDatabase.Columns[i].ColumnName; // 赋值. drCurrent[colName] = xmlRow[colName]; } // 将新的对象传递给 DataTable.Rows 集合的 Add 方法。 dtDatabase.Rows.Add(drCurrent); } // 通过 SqlCommandBuilder 设置 DataAdapter 对象的 InsertCommand、UpdateCommand 和 DeleteCommand 属性。 // 注意:表必须有主键信息 DoSqlCommandBuilder(adapter); // 更新原始数据库,可将 DataSet 传递到 DataAdapter 对象的 Update 方法。 adapter.Update(resultDataSet); } Console.WriteLine("处理完毕!"); }
public void ExtractTableParameters(string TableName, IDbDataAdapter adapter, out DatabaseCache InsertCache, out DatabaseCache DeleteCache, out DatabaseCache UpdateCache, out DatabaseCache IsExistCache, out DataTable dt ) { MySqlDataAdapter adap = adapter as MySqlDataAdapter; adap.FillLoadOption = LoadOption.OverwriteChanges; adapter.SelectCommand.CommandText = "select * from " + TableName; DataSet ds = new DataSet(); dt = adapter.FillSchema(ds, SchemaType.Source)[0]; dt.TableName = TableName; InsertCache = new DatabaseCache(string.Empty, new IDataParameter[dt.Columns.Count]); DeleteCache = new DatabaseCache(string.Empty, new IDataParameter[dt.PrimaryKey.Length]); UpdateCache = new DatabaseCache(string.Empty, new IDataParameter[dt.Columns.Count]); IsExistCache = new DatabaseCache(string.Empty, new IDataParameter[dt.PrimaryKey.Length]); InsertCache.SQL = "insert into " + TableName + "("; DeleteCache.SQL = "delete from " + TableName + " where "; UpdateCache.SQL = "Update " + TableName + " set "; IsExistCache.SQL = "select count(1) from " + TableName + " where "; InsertCache.SQL += StringTools.JoinString(dt.Columns, string.Empty, string.Empty, ",", false); InsertCache.SQL += ") values("; InsertCache.SQL += StringTools.JoinString(dt.Columns, "?", string.Empty, ",", false); InsertCache.SQL += ");"; Dictionary<string, DataColumn> PrimaryKeys = new Dictionary<string,DataColumn>(); foreach (DataColumn c in dt.PrimaryKey) { if (c.AutoIncrement) InsertCache.IsHaveAutoIncrement = true; PrimaryKeys.Add(c.ColumnName, c); } string s = " 1=1 "; foreach (DataColumn c in dt.Columns) { if (!PrimaryKeys.ContainsKey(c.ColumnName)) { UpdateCache.SQL += c.ColumnName + "=?" + c.ColumnName + ","; } else { s += " And " + c.ColumnName + "=?" + c.ColumnName; } } if (UpdateCache.SQL.EndsWith(",")) UpdateCache.SQL = UpdateCache.SQL.Remove(UpdateCache.SQL.Length - 1); UpdateCache.SQL += " Where "; DeleteCache.SQL += s; UpdateCache.SQL += s; IsExistCache.SQL += s; if (InsertCache.IsHaveAutoIncrement == true) { InsertCache.SQL += "select last_insert_id();"; } InsertCache.Parameters = new IDataParameter[InsertCache.IsHaveAutoIncrement?dt.Columns.Count - 1:dt.Columns.Count]; DeleteCache.Parameters = new IDataParameter[PrimaryKeys.Count]; UpdateCache.Parameters = new IDataParameter[dt.Columns.Count]; IsExistCache.Parameters = new IDataParameter[PrimaryKeys.Count]; int InsertIndex = 0 , DeleteIndex = 0, UpdateIndex = 0; foreach (DataColumn c in dt.Columns) { IDataParameter p = adapter.SelectCommand.CreateParameter(); p.ParameterName = "?" + c.ColumnName; p.SourceColumn = c.ColumnName; if (!c.AutoIncrement) { InsertCache.Parameters[InsertIndex++] = p; } if (PrimaryKeys.ContainsKey(c.ColumnName)) { DeleteCache.Parameters[DeleteIndex] = p; IsExistCache.Parameters[DeleteIndex] = p; DeleteIndex++; } UpdateCache.Parameters[UpdateIndex] = p; UpdateIndex++; } InsertCache.CurrentTable = UpdateCache.CurrentTable = DeleteCache.CurrentTable = IsExistCache.CurrentTable = dt; }
internal static DataSet ExecuteSelect(EDtxReaderType connType, int timeout, IDbConnection conn, string cmdText, ParamHelper[] paramList, ref TimeSpan executionTime) { DateTime started = DateTime.Now; IDataReader reader = null; IDbCommand cmd = null; bool bConnOpenedHere = EnsureConnectionOpen(conn); IDbTransaction transaction = conn.BeginTransaction(); DataSet retv = new DataSet(); try { cmd = conn.CreateCommand(); cmd.CommandTimeout = timeout; cmd.Transaction = transaction; cmd.CommandText = cmdText; DbType[] reqLenTypes = new DbType[] { DbType.AnsiString, DbType.AnsiStringFixedLength, DbType.String, DbType.StringFixedLength, DbType.Binary, DbType.Object, DbType.Xml }; for (int i = 0; i < paramList.Length; i++) { IDbDataParameter idb = cmd.CreateParameter(); cmd.Parameters.Add(BuildParameter(idb, paramList[i])); if (reqLenTypes.Contains(idb.DbType)) { if (idb is OdbcParameter) { (idb as OdbcParameter).Size = paramList[i].Size; } else if (idb is CtreeSqlParameter) { (idb as CtreeSqlParameter).Size = paramList[i].Size; } } } switch (connType) { case EDtxReaderType.Adapter: case EDtxReaderType.FaircomAdapter: { IDbDataAdapter adap = GetAdapter(connType, cmd); retv.BeginInit(); //Since the FillSchema and Fill functions return the same instance of 'DataTable' //There is probably a better way of doing this, but for sake of explination //Read the db schema bool bSchemaFound = false; DataTable[] dta = adap.FillSchema(retv, SchemaType.Source); DataTable clone = null; if (dta.Length > 0) { bSchemaFound = true; dta[0].TableName = "SchemaTable"; //Ensure the table is named 'SchemaTable' retv.Tables.Remove(dta[0]); //Drop the table from the dataset clone = dta[0].Clone(); //Clone the results dta[0].TableName = "Table"; //Rename the datatable instance back to table } adap.Fill(retv); //Fill 'Table' with the actual results if (bSchemaFound && clone != null) { retv.Tables.Add(clone); //Now add the 'schematable' back to the results } retv.EndInit(); break; } default: { DataTable dt; reader = cmd.ExecuteReader(); if (reader.FieldCount > 0) { retv.Tables.Add(dt = new DataTable("Table")); retv.Tables.Add(reader.GetSchemaTable()); switch (connType) { case EDtxReaderType.FaircomReader: case EDtxReaderType.Reader: { dt.Load(reader, LoadOption.OverwriteChanges); } break; case EDtxReaderType.FaircomReaderSafe: case EDtxReaderType.ReaderSafe: default: { bool columnsBuilt = false; while (reader.Read()) { if (columnsBuilt == false) { BuildColumnData(dt, reader); columnsBuilt = true; } AddDataRow(dt, reader); } } break; } } break; } } transaction.Commit(); executionTime = DateTime.Now - started; //Now update parameter inputs with their respective values for (int i = 0; i < paramList.Length; i++) { IDbDataParameter p = (IDbDataParameter)cmd.Parameters[i]; if (p.Direction != ParameterDirection.Input) { paramList[i].OutValue = p.Value; } } } catch (Exception e) { transaction.Rollback(); executionTime = DateTime.Now - started; HelperFunctions.LogException(e, false); throw e; } finally { if (reader != null) { if (!reader.IsClosed) { reader.Close(); } reader.Dispose(); } if (cmd != null) { cmd.Dispose(); } if (bConnOpenedHere) { conn.Close(); } } return(retv); }
/// ///////////////////////////////////////////////////////////////// public DataTable[] FillSchema( DataSet ds, SchemaType schema) { return(m_adapter.FillSchema(ds, schema)); }