/// <summary>
/// returns a DataTable object that has the same schema as
/// the user-specified table with the name
/// </summary>
/// <param name="tableName">name of the table in the database</param>
/// <returns>an empty dataTable with the same column names and types</returns>
public DataTable GetTableSchema(string tableName)
{
DataTable table = null;
DbConnection conn = CreateConnection();
try
{
table = new DataTable();
DbDataAdapter da = dbFactory.CreateDataAdapter();
da.SelectCommand = conn.CreateCommand();
da.SelectCommand.CommandText = "SELECT * FROM " + tableName;
da.FillSchema(table, SchemaType.Source);
table = new DataTable();
da.FillSchema(table, SchemaType.Source);
table.TableName = tableName;
}
finally
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
}
return(table);
}
public DataTable ExecuteDataTable()
{
try
{
if (this.dbConnection.State != ConnectionState.Open)
{
this.dbConnection.Open();
}
DbDataAdapter adapter = this.CreateAdapter();
DataTable dt = new DataTable();
DataSet ds = new DataSet();
ds.EnforceConstraints = false;
adapter.FillSchema(ds, SchemaType.Mapped);
adapter.Fill(ds);
dt = ds.Tables[0];
if (!IsInTransaction)
{
dbConnection.Close();
}
return(dt);
}
catch (Exception ex)
{
dbConnection.Close();
throw new Exception(ex.Message);
}
}
public virtual List <PrimaryKey> GetPrimaryKeyList(string tableName)
{
List <PrimaryKey> primaryKeys = new List <PrimaryKey>();
DataTable schemaDataTable = new DataTable();
try {
using (DbDataAdapter da = this.Factory.CreateDataAdapter()) {
DbCommand command = this.Connection.CreateCommand();
command.CommandText = string.Format("select * from {0} where 1=2", this.GetQuotedName(tableName));;
da.SelectCommand = command;
da.FillSchema(schemaDataTable, SchemaType.Source);
}
for (int index = 0, total = schemaDataTable.PrimaryKey.Length; index < total; index++)
{
primaryKeys.Add(new PrimaryKey {
ColumnName = schemaDataTable.PrimaryKey[index].ColumnName,
AutoIncrement = schemaDataTable.PrimaryKey[index].AutoIncrement
});
}
}
catch (Exception ex) {
Console.WriteLine(ex.ToString());
}
return(primaryKeys);
}
/// <summary>
/// 执行查询语句
/// </summary>
/// <param name="strSql">查询语句</param>
/// <param name="tableName">表名</param>
/// <returns></returns>
public static DataSet DoQuery(string strSql, string tableName)
{
DbConnection conn = DbFactory.CreateConnection;
try
{
using (DbDataAdapter dAdapter = DbFactory.CreateAdapter(strSql, conn))
{
DataSet ds = new DataSet();
dAdapter.SelectCommand.CommandTimeout = 0;
//dAdapter.Fill(ds);
dAdapter.FillSchema(ds, SchemaType.Mapped, tableName);
dAdapter.Fill(ds, tableName);
ds.Tables[0].TableName = tableName;
//Utils.Tools.RemoveDataTablePrimaryAndAllowDBNull(ds);
return(ds);
}
}
catch (Exception e)
{
throw e;
}
finally
{
DbFactory.CloseConnection(conn);
}
}
public void SelectSchema(DataSet pDs, string pSql, String pTableName)
{
DbDataAdapter MyDa = CreateDataAdapter();
MyDa.SelectCommand = CreateCommand(pSql);
MyDa.FillSchema(pDs, SchemaType.Source, pTableName);
}
/* selects the PICTURES table and binds Grid and picBox to data */
void SelectTable()
{
picBox.DataBindings.Clear();
Grid.DataSource = null;
DataTable Table = new DataTable("PICTURES");
using (DbConnection Con = factory.CreateConnection())
{
Con.ConnectionString = cs;
Con.Open();
DbCommand Cmd = Con.CreateCommand();
Cmd.CommandText = "select * from PICTURES";
using (DbDataAdapter adapter = factory.CreateDataAdapter())
{
adapter.SelectCommand = Cmd;
adapter.FillSchema(Table, SchemaType.Source);
adapter.Fill(Table);
Grid.DataSource = Table;
/* add a proper DataBinding to picBox */
picBox.DataBindings.Add("Image", Table, "IMG", true, DataSourceUpdateMode.OnPropertyChanged);
}
}
EnableCommands();
}
public DataTable[] FillSchema(string strSql, DataSet dataSet, SchemaType schemaType, string srcTable)
{
DbDataAdapter adapter = (DbDataAdapter)builder.GetDataAdapter(strSql);
AssignConnection(adapter.SelectCommand, null);
return(adapter.FillSchema(dataSet, schemaType, srcTable));
}
public AutoUpdateDataTable(Database iniDatabase, DbCommand command, string tableName, string key = "ID")
: base()
{
CurrentDatabase = iniDatabase;
if (CurrentDatabase != null)
{
DataAdapter = CurrentDatabase.GetDataAdapter();
//DbCommand command = CurrentDatabase.GetSqlStringCommand(Sqlstring);
command.Connection = CurrentDatabase.CreateConnection();
DataAdapter.SelectCommand = command;
CommandBuilder = CurrentDatabase.DbProviderFactory.CreateCommandBuilder();
CommandBuilder.DataAdapter = DataAdapter;
CommandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
this.TableName = tableName;
DataAdapter.Fill(this);
if (key == null)
{
DataAdapter.FillSchema(this, SchemaType.Mapped);
}
else
{
this.PrimaryKey = new DataColumn[] { this.Columns[key] };
}
}
}
} // Generate
/// <summary>
/// Build a new set of TableMappings from SELECT's column.
/// Make a copy of the DataAdapter, Command, and Connection
/// objects to avoid accidentally overriding fields in them.
/// </summary>
/// <param name="dbDataAdapter">A writeable DataAdapter.</param>
/// <returns>2-column DataTable to hold the names.</returns>
static internal DataTable BuildMapFromDatabase(DbDataAdapter dbAdapter)
{
DataTable dt = new DataTable("mapping");
dt.Columns.Add(SOURCE_COLUMNS);
dt.Columns.Add(DATASET_COLUMNS);
IDbDataAdapter dataAdapter = (IDbDataAdapter)dbAdapter;
IDbCommand dbCommand = dataAdapter.SelectCommand;
// safety check for a missing or incomplete Command
if (dbCommand == null ||
dbCommand.CommandText == null ||
dbCommand.CommandText.Trim().Length == 0)
{
System.Windows.Forms.MessageBox.Show(
"Data Adapter's SELECT command is null or an empty string; " +
"table mappings are not available.",
"Table Mappings");
return(dt);
}
// safety check for a missing Connection
if (dbCommand.Connection == null)
{
System.Windows.Forms.MessageBox.Show(
"Data Adapter's SELECT command's Connection is null; " +
"table mappings are not available.",
"Table Mappings");
return(dt);
}
// make a schema table that the DbAdapter can fill in
DataTable dtSchema = new DataTable();
Cursor cursor = Cursor.Current; // save cursor, probably Arrow
Cursor.Current = Cursors.WaitCursor; // hourglass cursor
try
{
dbAdapter.FillSchema(dtSchema, SchemaType.Source);
// copy the schema columns to the 2-column DataTable
// that will be the DataSource for the form's datagrid.
for (int i = 0; i < dtSchema.Columns.Count; i++)
{
DataColumn dataColumn = dtSchema.Columns[i];
System.Data.DataRow datarow = dt.NewRow();
datarow[TableMappings.SOURCE_COLUMNS] =
dataColumn.ColumnName;
datarow[TableMappings.DATASET_COLUMNS] =
dataColumn.ColumnName;
dt.Rows.Add(datarow);
}
}
finally { Cursor.Current = cursor; } // resored Arrow cursor
return(dt); // return with all went well
} // BuildMapFromDatabase
protected void FillSchema(DataSet dataSet, string sqlFormat)
{
DbCommand command = Connection.CreateCommand();
foreach (DataTable table in dataSet.Tables)
{
command.CommandText = string.Format(sqlFormat, table.TableName);
DbDataAdapter adapter = CreateDataAdapter();
adapter.SelectCommand = command;
try
{
adapter.FillSchema(table, SchemaType.Source);
}
catch (Exception ex)
{
if ((string)table.ExtendedProperties["TableType"] == "View")
{
//throw new SchemaException(string.Format(SchemaMessages.ErrorView, table.TableName), ex);
}
else
{
throw ex;
}
}
}
}
public void SelectDsData(DataSet pDs, string pSql, string pTableName, bool pWithSchema, int pStartRecord, int pMaxRecord)
{
DbDataAdapter MyDA = CreateDataAdapter();
MyDA.SelectCommand = CreateCommand(pSql);
if (pWithSchema)
{
MyDA.FillSchema(pDs, SchemaType.Source, pTableName);
}
MyDA.Fill(pDs, pStartRecord, pMaxRecord, pTableName);
}
internal bool RefreshSchema(DesignerDataConnection connection, string commandText, SqlDataSourceCommandType commandType, ParameterCollection parameters, bool preferSilent)
{
IServiceProvider site = this.SqlDataSource.Site;
DbCommand command = null;
try
{
DbProviderFactory dbProviderFactory = GetDbProviderFactory(connection.ProviderName);
DbConnection designTimeConnection = GetDesignTimeConnection(base.Component.Site, connection);
if (designTimeConnection == null)
{
if (!preferSilent)
{
UIServiceHelper.ShowError(this.SqlDataSource.Site, System.Design.SR.GetString("SqlDataSourceDesigner_CouldNotCreateConnection"));
}
return(false);
}
command = this.BuildSelectCommand(dbProviderFactory, designTimeConnection, commandText, parameters, commandType);
DbDataAdapter adapter = CreateDataAdapter(dbProviderFactory, command);
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataSet dataSet = new DataSet();
adapter.FillSchema(dataSet, SchemaType.Source, "DefaultView");
DataTable schemaTable = dataSet.Tables["DefaultView"];
if (schemaTable == null)
{
if (!preferSilent)
{
UIServiceHelper.ShowError(site, System.Design.SR.GetString("SqlDataSourceDesigner_CannotGetSchema"));
}
return(false);
}
this.SaveSchema(connection, commandText, schemaTable);
return(true);
}
catch (Exception exception)
{
if (!preferSilent)
{
UIServiceHelper.ShowError(site, exception, System.Design.SR.GetString("SqlDataSourceDesigner_CannotGetSchema"));
}
}
finally
{
if ((command != null) && (command.Connection.State == ConnectionState.Open))
{
command.Connection.Close();
}
}
return(false);
}
protected DataTable SelectData(string pSql, string pName, bool pWithSchema)
{
DbDataAdapter MyDA = CreateDataAdapter();
DataSet MyDs = new DataSet();
MyDA.SelectCommand = CreateCommand(pSql);
if (pWithSchema)
{
MyDA.FillSchema(MyDs, SchemaType.Source, pName);
}
MyDA.Fill(MyDs, pName);
DataTable MyTable = MyDs.Tables[0];
MyDs.Tables.Remove(MyTable);
return(MyTable);
}
private bool HasIdentityColumnInternal(string tableName)
{
DbDataAdapter dataAdapter = connection.ProviderFactory.CreateDataAdapter();
dataAdapter.SelectCommand = CreateCommand("select * from " + tableName);
DataTable table = new DataTable(tableName);
table = dataAdapter.FillSchema(table, SchemaType.Source);
foreach (DataColumn column in table.Columns)
{
if (column.AutoIncrement)
{
return(true);
}
}
return(false);
}
/// <summary>
/// Fills the schema.
/// </summary>
/// <param name="dataTable">The data table.</param>
public void FillSchema(DataTable dataTable)
{
TraceStart("FillSchema");
DataSet dataSet = new DataSet();
this.Prepare();
try {
using (DbDataAdapter adapter = DataAccess.Provider.CreateDataAdapter() as DbDataAdapter) {
adapter.SelectCommand = Command as DbCommand;
adapter.FillSchema(dataTable, SchemaType.Mapped);
}
}
finally {
this.CloseConnection(false);
TraceEnd("FillSchema");
}
}
private void ValidateDatabaseTableStructure(string tableName, DataTable dataTable)
{
if (_connection == null)
{
return;
}
DbDataAdapter dbDA = GetCurrentFactory().CreateDataAdapter();
dbDA.SelectCommand = _connection.CreateCommand();
dbDA.SelectCommand.Transaction = _transaction;
dbDA.SelectCommand.CommandType = CommandType.Text;
dbDA.SelectCommand.CommandText = string.Format("select * from {0}", tableName);
var ds = new DataSet();
dbDA.FillSchema(ds, SchemaType.Source);
ValidateTableStructure(tableName, dataTable, ds);
}
public virtual DataTable GetSchema(DbDataAdapter dbDataAdapter)
{
// preconditions
if (dbDataAdapter == null)
{
throw new Exception(" ADatabase.GetSchema() dbDataAdapter is null");
}
try {
DataTable dataTable = new DataTable();
dataTable = dbDataAdapter.FillSchema(dataTable, SchemaType.Source);
return(dataTable);
}
catch (Exception exception) {
string message = string.Format("ADatabase.GetSchema() {0} fails\n") + exception.Message;
throw new Exception(message);
}
}
public DataTable getTableStructure(string tableName)
{
try
{
DataSet dst = new DataSet(tableName);
db = DatabaseFactory.CreateDatabase(this.useDatabaseServer);
DbDataAdapter adapter = db.GetDataAdapter();
DbCommand selectCommand = db.GetSqlStringCommand("Select * From " + tableName + " WHERE 1=2");
adapter.SelectCommand = selectCommand;
adapter.FillSchema(dst, SchemaType.Mapped, tableName);
adapter.Fill(dst, tableName);
return(dst.Tables[tableName]);
}
catch (System.Exception ex)
{
throw ex;
}
}
/// <summary>
/// Fills the table schema.
/// </summary>
/// <param name="table">DataTable to fill.</param>
/// <param name="selectCommand">The SQL select command.</param>
/// <param name="parameters">SQL parameters.</param>
/// <remarks>
/// Usually you don't need to use this method. Internally it uses the <see cref="GetConnection"/> and
/// <see cref="GetAdapter"/> methods to fill the table schema. If you create own connection component
/// that does not use nor connection or adapter, then you need to override this method.
/// </remarks>
public virtual void FillTableSchema(DataTable table, string selectCommand,
CommandParameterCollection parameters)
{
using (DbConnection conn = GetConnection())
{
OpenConnection(conn);
// prepare select command
selectCommand = PrepareSelectCommand(selectCommand, table.TableName, conn);
// read the table schema
using (DbDataAdapter adapter = GetAdapter(selectCommand, conn, parameters))
{
adapter.SelectCommand.CommandTimeout = CommandTimeout;
adapter.FillSchema(table, SchemaType.Source);
}
}
}
/* DbDataAdapter.FillSchema() method.
* FillSchema() returns schema information based on the DbDataAdapter.SelectCommand.
*
* Also the FillSchema() configures the following DataColumn properties
* AllowDBNull
* AutoIncrement (AutoIncrementStep and AutoIncrementSeed must by set manually)
* MaxLength
* ReadOnly
* Unique
*
* and the following DataTable properties
* PrimaryKey
* Constraints
*
* FillSchema() preserves any schema already defined in the DataTable objects.
*/
void SelectFillSchema(string SQL, DataTable table)
{
using (DbConnection con = factory.CreateConnection())
{
con.ConnectionString = cs;
con.Open();
DbCommand cmd = con.CreateCommand();
cmd.CommandText = SQL;
using (DbDataAdapter adapter = factory.CreateDataAdapter())
{
adapter.SelectCommand = cmd;
adapter.FillSchema(table, SchemaType.Source);
adapter.Fill(table);
Grid.DataSource = table;
}
}
}
public DataTable FillTableSchema(string Provider, string Connection, string Query)
{
DataTable dt = new DataTable();
if (string.IsNullOrEmpty(this.ProviderName))
{
this.ProviderName = "System.Data.SqlClient";
}
if (string.IsNullOrEmpty(Provider))
{
Provider = "System.Data.SqlClient";
}
using (DbConnection dbconnection = DbProviderFactories.GetFactory(Provider).CreateConnection())
{
dbconnection.ConnectionString = Connection;
DbCommand dbcommand = dbconnection.CreateCommand();
dbcommand.CommandText = Query;
try
{
dbcommand.Connection.Open();
DbDataAdapter dbdataadapter = DbProviderFactories.GetFactory(ProviderName).CreateDataAdapter();
dbdataadapter.SelectCommand = dbcommand;
dbdataadapter.FillSchema(dt, SchemaType.Mapped);
IsErrorFound = false;
}
catch (Exception ex)
{
IsErrorFound = true;
ErrorMessage = ex.Message;
}
finally
{
if (dbcommand.Connection.State != ConnectionState.Closed)
{
dbcommand.Connection.Close();
dbcommand.Dispose();
}
}
}
return(dt);
}
public DataSet ExecuteKDDataSet(string string_2)
{
DataSet dataSet = null;
DbConnection dbConnection = null;
DbCommand dbCommand = null;
DbDataAdapter dbDataAdapter = null;
DbCommandBuilder dbCommandBuilder = null;
dbDataAdapter = this.GetDbDataAdapter();
dbConnection = this.GetDbConnection();
dbConnection.ConnectionString = this.ConnectionString;
dbCommand = this.GetDbCommand();
if (dbConnection.State == ConnectionState.Closed)
{
dbConnection.Open();
}
dbCommand.Connection = dbConnection;
dbCommand.CommandText = string_2;
dbDataAdapter.SelectCommand = dbCommand;
dbCommandBuilder = this.GetDbCommandBuilder();
dbCommandBuilder.DataAdapter = dbDataAdapter;
dataSet = new DataSet();
dbDataAdapter.FillSchema(dataSet, SchemaType.Source);
dbDataAdapter.Fill(dataSet);
if (dbConnection != null)
{
dbConnection.Dispose();
}
dbConnection = null;
if (dbCommand != null)
{
dbCommand.Dispose();
}
dbCommand = null;
dbDataAdapter = null;
if (dbCommandBuilder != null)
{
dbCommandBuilder.Dispose();
}
dbCommandBuilder = null;
return(dataSet);
}
/// <summary>
/// Create a datatable using data contained in the adapter
/// </summary>
/// <param name="dataAdapter">Adapter to use</param>
/// <param name="fillSchema">Specify if data schema must be recovered</param>
/// <returns>Return a datatable</returns>
public static DataTable CreateDataTable(DbDataAdapter dataAdapter, bool fillSchema = true)
{
//Verifico che l'adatattatore non sia nullo
if (dataAdapter == null)
{
throw new ArgumentNullException(nameof(dataAdapter));
}
//Creo un nuovo oggetto DataTable vuoto
DataTable dataTable = new DataTable();
//Se ho specificato di importare lo schema
if (fillSchema)
{
dataAdapter.FillSchema(dataTable, SchemaType.Source);
}
//Eseguo il riempimento del datase
dataAdapter.Fill(dataTable);
return(dataTable);
}
[Incomplete]//--Работает только с таблицами до 8 миллионов записей, далее вылетает System.OutOfMemoryException
public virtual DataTable GetTableFromDb(string tablename, bool with_primary_key, bool with_max_string_length, bool with_default_values, bool CaseSensivity)
{
_dt = new DataTable(tablename);
string sql = "";
if (!CaseSensivity)
{
sql = "select * from {0}";
if (!string.IsNullOrEmpty(_owner))
{
sql = "select * from {0}.{1}";
sql = string.Format(sql, _owner, tablename);
}
else
{
sql = string.Format(sql, tablename);
}
}
else
{
sql = "select * from {0}{1}{2}";
if (!string.IsNullOrEmpty(_owner))
{
sql = "select * from {0}{1}{2}.{0}{3}{2}";
sql = string.Format(sql, _open_bracket, _owner, _close_bracket, tablename);
}
else
{
sql = string.Format(sql, _open_bracket, tablename, _close_bracket);
}
}
_DA = CreateDataAdapter(sql);
_DA.FillLoadOption = LoadOption.Upsert;//--Важнейшая опция, после которой можно вставить эту таблицу в другую базу
_DA.FillSchema(_dt, SchemaType.Source);
PrepareTableSchemeBeforeFill(_dt, with_primary_key, with_max_string_length, with_default_values, CaseSensivity);
_DA.Fill(_dt);
return(_dt);
}
protected DataTable open_schema(string sql, bool throwerr = true, string table_name = "")
{
bool opened = false;
try {
opened = open_conn();
log.log_sql(sql);
DbCommand cmd = _conn.CreateCommand();
if (_trans != null)
{
cmd.Transaction = _trans;
}
cmd.CommandText = sql;
if (_timeout > 0)
{
cmd.CommandTimeout = _timeout;
}
cmd.CommandType = CommandType.Text;
DbDataAdapter ad = get_factory(_dbType).CreateDataAdapter();
ad.SelectCommand = cmd;
DataTable dt = table_name != "" ? new DataTable(table_name) : new DataTable();
ad.FillSchema(dt, SchemaType.Mapped);
return(dt);
} catch (Exception ex) { log.log_err(sql); log.log_err(ex); if (throwerr)
{
throw ex;
}
else
{
return(null);
} } finally { if (opened)
{
close_conn();
}
}
}
public DataTable ExecuteDataTable(string sql, CommandType cmdType, DbParameter[] dbParams = null)
{
DataTable dt = new DataTable();
DataSet ds = new DataSet();
PrepareCmd(sql, cmdType, dbParams);
try
{
dataAdpater = dbProvierFactory.CreateDataAdapter();
dataAdpater.SelectCommand = cmd;
dataAdpater.FillSchema(ds, SchemaType.Source);
dataAdpater.Fill(ds);
dt = ds.Tables[0];
}
catch (Exception ex)
{
CloseConn();
throw (ex);
}
CloseConn();
return(dt);
}
/* selects TableNames tables from the database into the ds DataSet object. */
void SelectTables(string[] TableNames)
{
using (DbConnection Con = factory.CreateConnection())
{
Con.ConnectionString = cs;
Con.Open();
DbCommand Cmd = Con.CreateCommand();
using (DbDataAdapter Adapter = factory.CreateDataAdapter())
{
Adapter.SelectCommand = Cmd;
foreach (string TableName in TableNames)
{
Cmd.CommandText = string.Format("select * from {0}", TableName);
Adapter.FillSchema(ds, SchemaType.Source, TableName);
Adapter.Fill(ds, TableName);
}
}
}
}
public static DataSet GetDataSchema(string strSql)
{
DbConnection conn = DbFactory.CreateConnection;
try
{
using (DbDataAdapter dAdapter = DbFactory.CreateAdapter(strSql, conn))
{
DataSet ds = new DataSet();
dAdapter.FillSchema(ds, SchemaType.Mapped);
return(ds);
}
}
catch (Exception e)
{
throw e;
}
finally
{
DbFactory.CloseConnection(conn);
}
}
/// <summary>
/// 得到一个DataTableSchema
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public DataTable GetDataTableSchema(string sql, DbParameter[] parameters = null)
{
using (DbCommand cmd = Connection.CreateCommand())
{
cmd.CommandText = sql;
if (IsTransaction)
{
cmd.Transaction = Transaction;
}
if (parameters != null && parameters.Length > 0)
{
cmd.Parameters.AddRange(parameters);
}
using (DbDataAdapter adapter = CreateAdapter())
{
adapter.SelectCommand = cmd;
DataTable dataTable = new DataTable();
adapter.FillSchema(dataTable, SchemaType.Mapped);
cmd.Parameters.Clear();
return(dataTable);
}
}
}
public DataTable GetDataTable(string sql, string TableName, int max_rows, bool WithSchema)
{
DbDataAdapter da = null;
try
{
PrepareSql(ref sql, max_rows);
this.Open();
da = this.GetDataAdapter(sql);
DataTable tb = new DataTable(TableName);
da.Fill(tb);
if (WithSchema)
{
da.FillSchema(tb, SchemaType.Mapped);
}
return(tb);
}
catch (Exception ex)
{
if (InTransaction())
{
RollbackTransaction();
}
throw new Exception("Erro ao executar a sql : " + sql, ex);
}
finally
{
if (da != null)
{
da.Dispose();
da = null;
}
this.Close();
}
}