public DataSet ExecuteDataSet(DbCommand cmd)
{
cmd.Connection = CreateConnection();
cmd.Connection.Open();
try
{
using (DbDataAdapter dbDataAdapter = dbFactory.CreateDataAdapter())
{
dbDataAdapter.SelectCommand = cmd;
DataSet ds = new DataSet();
dbDataAdapter.Fill(ds);
if (cmd.Connection.State != ConnectionState.Closed)
{
cmd.Connection.Close();
cmd.Connection.Dispose();
}
return(ds);
}
}
catch
{
if (cmd.Connection.State != ConnectionState.Closed)
{
cmd.Connection.Close();
cmd.Connection.Dispose();
}
throw;
}
}
public TableAdapterSchemaInfo (DbProviderFactory provider) {
this.Provider = provider;
this.Adapter = provider.CreateDataAdapter ();
this.Connection = provider.CreateConnection ();
this.Commands = new ArrayList ();
this.ShortCommands = false;
}
protected void Initialize (string databaseName, string querySelectRowString)
{
string providerName = GetProviderNameByDBName(databaseName);
string connectionString = GetConnectionStringByDBName(databaseName);
// Create the DbProviderFactory and DbConnection.
factory = DbProviderFactories.GetFactory(providerName);
connection = factory.CreateConnection();
connection.ConnectionString = connectionString;
// Create the DbCommand.
DbCommand SelectTableCommand = factory.CreateCommand();
SelectTableCommand.CommandText = querySelectRowString;
SelectTableCommand.Connection = connection;
adapter = factory.CreateDataAdapter();
adapter.SelectCommand = SelectTableCommand;
// Create the DbCommandBuilder.
builder = factory.CreateCommandBuilder();
builder.DataAdapter = adapter;
adapter.ContinueUpdateOnError = true;
}
private static object CreateObject(DbProviderFactory factory, ProviderSupportedClasses kindOfObject, string providerName)
{
switch (kindOfObject)
{
case ProviderSupportedClasses.DbConnection:
return factory.CreateConnection();
case ProviderSupportedClasses.DbDataAdapter:
return factory.CreateDataAdapter();
case ProviderSupportedClasses.DbParameter:
return factory.CreateParameter();
case ProviderSupportedClasses.DbCommand:
return factory.CreateCommand();
case ProviderSupportedClasses.DbCommandBuilder:
return factory.CreateCommandBuilder();
case ProviderSupportedClasses.DbDataSourceEnumerator:
return factory.CreateDataSourceEnumerator();
case ProviderSupportedClasses.CodeAccessPermission:
return factory.CreatePermission(PermissionState.None);
}
throw new InternalException(string.Format(CultureInfo.CurrentCulture, "Cannot create object of provider class identified by enum {0} for provider {1}", new object[] { Enum.GetName(typeof(ProviderSupportedClasses), kindOfObject), providerName }));
}
public DataTable RetrieveTableStructure(string tableName)
{
DataTable tableStruct = new DataTable();
string lib = GetProviderFactoryLib(providerName);
System.Data.Common.DbProviderFactory provider = RegisterProvider(providerName);
DbConnection con = provider.CreateConnection();
con.ConnectionString = BuildConnectionString(providerName, "");
DbCommand com = provider.CreateCommand();
com.CommandTimeout = 30;
com.Connection = con;
string queryString = "";
if (providerName == ProviderFactoryType.MySql.ToString())
{
queryString = string.Format(@"
SELECT
lower( Column_name) as FieldName,
Column_name as FieldCaption,
Data_type As ValueType,
0 As ColumnIsHide,
0 As FieldIsHide,
0 As IsFake,
0 As IsReadOnly,
0 As IsRequiered,
'' As FieldCategory,
'' As UserHelp,
Extra As Extra,
if( isnull( Character_Maximum_Length),Numeric_Precision, Character_Maximum_Length) As Length,
if( isnull(Numeric_Scale), 000, Numeric_Scale) As DecimalPosition
From information_schema.COLUMNS
Where Table_Schema = '{0}'
And LOWER(Table_Name) = '{1}'" , this.dbName, tableName.ToLower()).Replace("\r", "").Replace("\n", " ").Replace("\t", " ").Replace(" ", " ");
}
com.CommandText = queryString;
com.CommandType = CommandType.Text;
DataSet ds = new DataSet();
DbDataAdapter da = provider.CreateDataAdapter();
da.SelectCommand = com;
try
{
con.Open();
da.Fill(ds);
tableStruct = ds.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
con.Close();
return(tableStruct);
}
public ConnexionOracle()
{
connec = new OracleConnection();
connec.ConnectionString = string.Format("User Id=om141055; Password=om141055; Data Source=//{0}", Properties.Settings.Default.connexion);
dbpf = DbProviderFactories.GetFactory("Oracle.DataAccess.Client");
connec.Open();
// initialisation de la connexion conn et de dbpf
dba = dbpf.CreateDataAdapter();
}
protected DbDataAdapter CreateAdapter()
{
if (IsSingleton)
{
if (dbDataAdapter == null)
{
dbDataAdapter = dbFactory.CreateDataAdapter();
}
}
else
{
if (dbDataAdapter != null)
{
dbDataAdapter.Dispose();
}
dbDataAdapter = dbFactory.CreateDataAdapter();
}
return(dbDataAdapter);
}
private void ClickSendRequest(object sender, EventArgs e)
{
connection.ConnectionString = textBoxConnectionString.Text;
DbDataAdapter dataAdapter = factory.CreateDataAdapter();
dataAdapter.SelectCommand = (DbCommand)connection.CreateCommand();
dataAdapter.SelectCommand.CommandText = textBoxSqlRequest.Text;
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
dataGridView.DataSource = null;
dataGridView.DataSource = dataTable;
}
//Constructor: intiliazes dbfactory and its connection
public DALBlog(string connectionString)
{
// set object connection string to the one provided
this.connectionString = ConfigurationManager.ConnectionStrings[connectionString].ConnectionString;
// create new factory based on provider
dbFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");
// create a connection to the desired database
connection = dbFactory.CreateConnection();
connection.ConnectionString = this.connectionString;
// initialize DataSet and DataAdapter objects
ds = new DataSet();
da = dbFactory.CreateDataAdapter();
}
private System.Data.Common.DbDataAdapter getDbDataAdapter(string providerName)
{
System.Data.Common.DbDataAdapter returnValue = null;
if (!String.IsNullOrEmpty(providerName))
{
System.Data.Common.DbProviderFactory factory = System.Data.Common.DbProviderFactories.GetFactory(providerName);
if (factory != null)
{
returnValue = factory.CreateDataAdapter();
}
}
return(returnValue);
}
public void RecordUpdate <T>(out int rowsAffected, T row)
{
rowsAffected = 0;
readConectionSettings();
string lib = GetProviderFactoryLib(providerName);
provider = RegisterProvider(providerName);
con = provider.CreateConnection();
con.ConnectionString = BuildConnectionString(providerName, dbName);
tableStruct = RetrieveTableStructure(tableName);
if (tableStruct.Columns.Count == 0)
{
return;
}
dataAdapter = provider.CreateDataAdapter();
dataAdapter.UpdateCommand = BuildUpdateCommand(provider, con);
dataAdapter.AcceptChangesDuringFill = true;
dataAdapter.AcceptChangesDuringUpdate = true;
dataAdapter.ContinueUpdateOnError = false;
DbCommand updCmd = dataAdapter.UpdateCommand;
Console.WriteLine("Update cmd: " + updCmd.CommandText);
try
{
updCmd.Connection.Open();
foreach (DbParameter parameter in updCmd.Parameters)
{
string fieldName = parameter.SourceColumn;
parameter.Value = row.GetType().GetProperty(fieldName).GetValue(row, null);
if (fieldName == identityColumn)
{
parameter.SourceVersion = DataRowVersion.Original;
}
}
object returnVal = updCmd.ExecuteNonQuery();
rowsAffected = Convert.ToInt16(returnVal);
}
catch (Exception ex)
{
throw ex;
}
finally
{ updCmd.Connection.Close(); }
}
public static DataTable ExecuteScalar(string tableName,
string connectionString,
string command,
DbProviderFactory dbFactory)
{
DataTable dtTable = CreateDataTable(tableName);
using (DbConnection dbConnection = dbFactory.CreateConnection()) {
dbConnection.ConnectionString = connectionString;
dbConnection.Open();
using (DbDataAdapter dbAdapter = dbFactory.CreateDataAdapter()) {
dbAdapter.SelectCommand = dbConnection.CreateCommand();
dbAdapter.SelectCommand.CommandText = command;
dbAdapter.Fill(dtTable);
}
}
return dtTable;
}
public static DataTable ReadTable(string sproc, List <ParamStruct> paramList)
{
DataTable dt = new DataTable();
SDC.DbProviderFactory factory = SDC.DbProviderFactories.GetFactory(Properties.Settings.Default.provider);
SDC.DbCommand comm = factory.CreateCommand();
comm = BuildCommand(sproc, paramList);
SDC.DbDataAdapter da = factory.CreateDataAdapter();
SDC.DbConnection conn = Connection(factory);
comm.Connection = conn;
da.SelectCommand = comm;
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
using (conn)
{
conn.Open();
da.Fill(dt);
return(dt);
}
}
public DataTable ExecuteQuery(String queryString, params dynamic[] args)
{
readConectionSettings();
string lib = GetProviderFactoryLib(providerName);
provider = RegisterProvider(providerName);
con = provider.CreateConnection();
con.ConnectionString = BuildConnectionString(providerName, this.dbName);
DataTable table = null;
DbCommand com = provider.CreateCommand();
com.CommandTimeout = 30;
com.Connection = con;
com.CommandText = queryString;
com.CommandType = CommandType.Text;
dataSet = new DataSet();
dataAdapter = provider.CreateDataAdapter();
dataAdapter.SelectCommand = com;
foreach (var param in args)
{
DbParameter par = provider.CreateParameter();
par.Direction = ParameterDirection.Input;
par.ParameterName = param.Name;
par.Value = param.Value;
dataAdapter.SelectCommand.Parameters.Add(par);
}
try
{
con.Open();
dataAdapter.Fill(dataSet);
table = dataSet.Tables[0];
}
catch (Exception ex)
{
Console.Write(ex);
throw ex;
}
con.Close();
return(table);
}
/// <summary>
/// 创建数据集
/// </summary>
/// <param name="dbProvider">数据库提供者</param>
/// <param name="dbCommand">数据库命令</param>
/// <param name="tableNames">数据表名称</param>
/// <returns>数据集</returns>
internal static DataSet InternalCreateDataSet(DbProviderFactory dbProvider, DbCommand dbCommand, String[] tableNames)
{
DataSet dataSet = new DataSet();
dataSet.Locale = CultureInfo.InvariantCulture;
using (DbDataAdapter dataAdapter = dbProvider.CreateDataAdapter())
{
((IDbDataAdapter)dataAdapter).SelectCommand = dbCommand;
String text = "Table";
for (Int32 j = 0; j < tableNames.Length; j++)
{
String sourceTable = (j == 0) ? text : (text + j);
dataAdapter.TableMappings.Add(sourceTable, tableNames[j]);
}
dataAdapter.Fill(dataSet);
}
return dataSet;
}
public DataTable getDataQuery(String queryString, params dynamic[] args)
{
if (con == null)
{
LoadConnection();
}
DataTable table = null;
DbCommand com = provider.CreateCommand();
com.CommandTimeout = 30;
com.Connection = this.con;
com.CommandText = queryString;
com.CommandType = CommandType.Text;
dataSet = new DataSet();
dataAdapter = provider.CreateDataAdapter();
dataAdapter.SelectCommand = com;
if (args != null && args.Length > 0)
{
foreach (var param in args)
{
DbParameter par = provider.CreateParameter();
par.Direction = ParameterDirection.Input;
par.ParameterName = param.Name;
par.Value = param.Value;
dataAdapter.SelectCommand.Parameters.Add(par);
}
}
try
{
con.Open();
dataAdapter.Fill(dataSet);
table = dataSet.Tables[0];
}
catch (Exception ex)
{
throw ex;
}
con.Close();
return(table);
}
/// <summary>
/// Creates data adapter for specifided database connection.
/// </summary>
/// <param name="factory">Database provider factory.</param>
/// <returns>Created Relatived data adapter.</returns>
private static DbDataAdapter _CreateDataAdapter(DbProviderFactory factory)
{
Debug.Assert(null != factory); // created
DbDataAdapter adapter = factory.CreateDataAdapter();
return adapter;
}
internal static DbDataAdapter CreateDataAdapter(DbProviderFactory factory, DbCommand command)
{
DbDataAdapter adapter = factory.CreateDataAdapter();
((IDbDataAdapter) adapter).SelectCommand = command;
return adapter;
}
private static void XML2DB(DbProviderFactory dataFactory, DbConnection dbcn, DbTransaction tran, DTDatabase dtDatabase, string namespacePrefix, string namespaceUri, XPathDocument genericXMLDate, XPathDocument formData, XPathDocument resultData, string prodef_id, string project_id, DateTime? applyingDate, DateTime? finishedDate)
{
if (!string.IsNullOrEmpty(dtDatabase.BeforeProcedure))
{
if (LoggingService.IsInfoEnabled)
{
LoggingService.InfoFormatted("执行交换前存储过程：{0}", new object[] { dtDatabase.BeforeProcedure });
}
DbCommand command = dbcn.CreateCommand();
command.CommandText = dtDatabase.BeforeProcedure;
command.CommandType = CommandType.StoredProcedure;
command.Transaction = tran;
command.ExecuteNonQuery();
}
XPathDocument[] documents = new XPathDocument[] { formData, resultData, genericXMLDate };
foreach (DTTable table in dtDatabase.DTTables)
{
XPathDocument document;
if (!table.IsActive || !table.IfImport)
{
continue;
}
if (LoggingService.IsInfoEnabled)
{
LoggingService.InfoFormatted("准备交换数据至表：'{0}<{1}>'", new object[] { table.Name, table.Description });
}
if (LoggingService.IsDebugEnabled)
{
LoggingService.DebugFormatted("基础数据来自：{0}", new object[] { table.SourceType });
}
switch (table.SourceType)
{
case SourceType.FormData:
document = formData;
break;
case SourceType.ResultData:
document = resultData;
break;
case SourceType.GenericXMLData:
document = genericXMLDate;
break;
default:
throw new NotSupportedException(string.Format("尚不支持的类型:{0}！", table.SourceType));
}
if (document != null)
{
DbDataAdapter adapter = dataFactory.CreateDataAdapter();
DbCommand command2 = dbcn.CreateCommand();
command2.Transaction = tran;
command2.CommandText = string.Format("select * from {0} where 1<>1", string.IsNullOrEmpty(table.ImportToTable) ? table.Name : table.ImportToTable);
adapter.SelectCommand = command2;
DbCommandBuilder builder = dataFactory.CreateCommandBuilder();
builder.DataAdapter = adapter;
adapter.InsertCommand = builder.GetInsertCommand();
adapter.InsertCommand.Transaction = tran;
XPathNavigator navigator = document.CreateNavigator();
XPathNodeIterator iterator = null;
if (!string.IsNullOrEmpty(namespacePrefix) && !string.IsNullOrEmpty(namespaceUri))
{
table.NamespacePrefix = string.Format(table.NamespacePrefix, namespacePrefix);
table.NamespaceUri = string.Format(table.NamespaceUri, namespaceUri);
table.MapXMLElementName = string.Format(table.MapXMLElementName, namespacePrefix);
}
if (!string.IsNullOrEmpty(table.NamespacePrefix))
{
if (LoggingService.IsDebugEnabled)
{
LoggingService.DebugFormatted("搜索:'{0}',前缀:'{1}',命名空间:'{2}'", new object[] { table.MapXMLElementName, table.NamespacePrefix, table.NamespaceUri });
}
XmlNamespaceManager resolver = new XmlNamespaceManager(navigator.NameTable);
resolver.AddNamespace(table.NamespacePrefix, table.NamespaceUri);
iterator = navigator.Select(table.MapXMLElementName, resolver);
if ((iterator.Count == 0) && !string.IsNullOrEmpty(table.DataSetName))
{
if (LoggingService.IsDebugEnabled)
{
LoggingService.DebugFormatted("搜索:'/{0}:{1}/{2}'", new object[] { namespacePrefix, table.DataSetName, table.MapXMLElementName });
}
iterator = navigator.Select(string.Format("/{0}:{1}/{2}", namespacePrefix, table.DataSetName, table.MapXMLElementName), resolver);
}
}
else
{
iterator = navigator.Select(table.MapXMLElementName);
}
if (iterator.Count > 0)
{
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
foreach (XPathNavigator navigator2 in iterator)
{
DataRow row = dataTable.NewRow();
foreach (DTColumn column in table.DTColumns)
{
if (!column.IsActive || !column.IfImport)
{
continue;
}
if (!dataTable.Columns.Contains(column.Name))
{
goto Label_0615;
}
if (!string.IsNullOrEmpty(column.MapXMLElementName))
{
switch (column.MapXMLElementName.ToUpper())
{
case "[GUID]":
try
{
row[column.Name] = StringHelper.GetNewGuid();
}
catch (Exception exception)
{
LoggingService.Error(exception);
}
goto Label_0580;
case "[PROJECT_ID]":
try
{
row[column.Name] = project_id;
}
catch (Exception exception2)
{
LoggingService.Error(exception2);
}
goto Label_0580;
case "[PRODEF_ID]":
try
{
row[column.Name] = prodef_id;
}
catch (Exception exception3)
{
LoggingService.Error(exception3);
}
goto Label_0580;
case "[NUM]":
try
{
row[column.Name] = dataTable.Rows.Count + 1;
}
catch (Exception exception4)
{
LoggingService.Error(exception4);
}
goto Label_0580;
case "[APPLYING_DATE]":
try
{
row[column.Name] = applyingDate;
}
catch (Exception exception5)
{
LoggingService.Error(exception5);
}
goto Label_0580;
case "[FINISHED_DATE]":
try
{
row[column.Name] = finishedDate;
}
catch (Exception exception6)
{
LoggingService.Error(exception6);
}
goto Label_0580;
}
try
{
SetColumnValue(documents, namespacePrefix, namespaceUri, navigator2, row, column);
}
catch (Exception exception7)
{
LoggingService.Error(string.Format("设置'{0}<{1}>'时出错", column.Name, column.Description), exception7);
}
}
Label_0580:
if (!string.IsNullOrEmpty(column.DefaultValue) && Convert.IsDBNull(row[column.Name]))
{
row[column.Name] = column.DefaultValue;
}
if (LoggingService.IsDebugEnabled && !Convert.IsDBNull(row[column.Name]))
{
LoggingService.DebugFormatted("获取的列'{0}<{1}>'的值为:'{2}'", new object[] { column.Name, column.Description, row[column.Name] });
}
continue;
Label_0615:;
LoggingService.WarnFormatted("表‘{0}<{1}>’已经不包含列‘{2}<{3}>’", new object[] { table.Name, table.Description, column.Name, column.Description });
}
dataTable.Rows.Add(row);
}
if (dataTable.Rows.Count > 0)
{
if (LoggingService.IsDebugEnabled)
{
LoggingService.Debug("添加了新数据,更新表");
}
adapter.Update(dataTable);
}
continue;
}
LoggingService.WarnFormatted("没有找到与表'{0}'对应的'{1}'节点!", new object[] { table.Name, table.MapXMLElementName });
}
}
if (!string.IsNullOrEmpty(dtDatabase.AfterProcedure))
{
DbCommand command3 = dbcn.CreateCommand();
command3.CommandText = dtDatabase.AfterProcedure;
command3.CommandType = CommandType.StoredProcedure;
command3.Transaction = tran;
command3.ExecuteNonQuery();
if (LoggingService.IsDebugEnabled)
{
LoggingService.DebugFormatted("执行导入后存储过程:'{0}'!", new object[] { dtDatabase.AfterProcedure });
}
}
}
public IDataAdapter CreateDataAdapter()
{
//blank dataadapter of the correct type
return(_frameworkProviderFactory.CreateDataAdapter());
}
public virtual DataTable GetSchema(string collectionName, string[] restrictionValues)
{
if (collectionName == null)
{
//LAMESPEC: In MS.NET, if collectionName is null, it throws ArgumentException.
throw new ArgumentException();
}
String cName = null;
DataTable schemaTable = MetaDataCollections.Instance;
int length = restrictionValues == null ? 0 : restrictionValues.Length;
foreach (DataRow row in schemaTable.Rows)
{
if (String.Compare((string)row ["CollectionName"], collectionName, true) == 0)
{
if (length > (int)row ["NumberOfRestrictions"])
{
throw new ArgumentException("More restrictions were provided " +
"than the requested schema ('" +
row ["CollectionName"].ToString() + "') supports");
}
cName = row ["CollectionName"].ToString();
}
}
if (cName == null)
{
throw new ArgumentException("The requested collection ('" + collectionName + "') is not defined.");
}
DbCommand command = null;
DataTable dataTable = new DataTable();
switch (cName)
{
case "Databases":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select name as database_name, dbid, crdate as create_date " +
"from master.sys.sysdatabases where (name = @Name or (@Name " +
"is null))";
AddParameter(command, "@Name", DbType.StringFixedLength, 4000);
break;
case "ForeignKeys":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, " +
"TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, " +
"IS_DEFERRABLE, INITIALLY_DEFERRED from " +
"INFORMATION_SCHEMA.TABLE_CONSTRAINTS where (CONSTRAINT_CATALOG" +
" = @Catalog or (@Catalog is null)) and (CONSTRAINT_SCHEMA = " +
"@Owner or (@Owner is null)) and (TABLE_NAME = @Table or (" +
"@Table is null)) and (CONSTRAINT_NAME = @Name or (@Name is null))" +
" and CONSTRAINT_TYPE = 'FOREIGN KEY' order by CONSTRAINT_CATALOG," +
" CONSTRAINT_SCHEMA, CONSTRAINT_NAME";
AddParameter(command, "@Catalog", DbType.StringFixedLength, 4000);
AddParameter(command, "@Owner", DbType.StringFixedLength, 4000);
AddParameter(command, "@Table", DbType.StringFixedLength, 4000);
AddParameter(command, "@Name", DbType.StringFixedLength, 4000);
break;
case "Indexes":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select distinct db_name() as constraint_catalog, " +
"constraint_schema = user_name (o.uid), " +
"constraint_name = x.name, table_catalog = db_name (), " +
"table_schema = user_name (o.uid), table_name = o.name, " +
"index_name = x.name from sysobjects o, sysindexes x, " +
"sysindexkeys xk where o.type in ('U') and x.id = o.id and " +
"o.id = xk.id and x.indid = xk.indid and xk.keyno = x.keycnt " +
"and (db_name() = @Catalog or (@Catalog is null)) and " +
"(user_name() = @Owner or (@Owner is null)) and (o.name = " +
"@Table or (@Table is null)) and (x.name = @Name or (@Name is null))" +
"order by table_name, index_name";
AddParameter(command, "@Catalog", DbType.StringFixedLength, 4000);
AddParameter(command, "@Owner", DbType.StringFixedLength, 4000);
AddParameter(command, "@Table", DbType.StringFixedLength, 4000);
AddParameter(command, "@Name", DbType.StringFixedLength, 4000);
break;
case "IndexColumns":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select distinct db_name() as constraint_catalog, " +
"constraint_schema = user_name (o.uid), constraint_name = x.name, " +
"table_catalog = db_name (), table_schema = user_name (o.uid), " +
"table_name = o.name, column_name = c.name, " +
"ordinal_position = convert (int, xk.keyno), keyType = c.xtype, " +
"index_name = x.name from sysobjects o, sysindexes x, syscolumns c, " +
"sysindexkeys xk where o.type in ('U') and x.id = o.id and o.id = c.id " +
"and o.id = xk.id and x.indid = xk.indid and c.colid = xk.colid " +
"and xk.keyno <= x.keycnt and permissions (o.id, c.name) <> 0 " +
"and (db_name() = @Catalog or (@Catalog is null)) and (user_name() " +
"= @Owner or (@Owner is null)) and (o.name = @Table or (@Table is" +
" null)) and (x.name = @ConstraintName or (@ConstraintName is null)) " +
"and (c.name = @Column or (@Column is null)) order by table_name, " +
"index_name";
AddParameter(command, "@Catalog", DbType.StringFixedLength, 8);
AddParameter(command, "@Owner", DbType.StringFixedLength, 4000);
AddParameter(command, "@Table", DbType.StringFixedLength, 13);
AddParameter(command, "@ConstraintName", DbType.StringFixedLength, 4000);
AddParameter(command, "@Column", DbType.StringFixedLength, 4000);
break;
case "Procedures":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, " +
"ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, " +
"CREATED, LAST_ALTERED from INFORMATION_SCHEMA.ROUTINES where " +
"(SPECIFIC_CATALOG = @Catalog or (@Catalog is null)) and " +
"(SPECIFIC_SCHEMA = @Owner or (@Owner is null)) and (SPECIFIC_NAME" +
" = @Name or (@Name is null)) and (ROUTINE_TYPE = @Type or (@Type " +
"is null)) order by SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME";
AddParameter(command, "@Catalog", DbType.StringFixedLength, 4000);
AddParameter(command, "@Owner", DbType.StringFixedLength, 4000);
AddParameter(command, "@Name", DbType.StringFixedLength, 4000);
AddParameter(command, "@Type", DbType.StringFixedLength, 4000);
break;
case "ProcedureParameters":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, " +
"ORDINAL_POSITION, PARAMETER_MODE, IS_RESULT, AS_LOCATOR, " +
"PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, " +
"CHARACTER_OCTET_LENGTH, COLLATION_CATALOG, COLLATION_SCHEMA, " +
"COLLATION_NAME, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, " +
"CHARACTER_SET_NAME, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, " +
"NUMERIC_SCALE, DATETIME_PRECISION, INTERVAL_TYPE, " +
"INTERVAL_PRECISION from INFORMATION_SCHEMA.PARAMETERS where " +
"(SPECIFIC_CATALOG = @Catalog or (@Catalog is null)) and " +
"(SPECIFIC_SCHEMA = @Owner or (@Owner is null)) and (SPECIFIC_NAME = " +
"@Name or (@Name is null)) and (PARAMETER_NAME = @Parameter or (" +
"@Parameter is null)) order by SPECIFIC_CATALOG, SPECIFIC_SCHEMA," +
" SPECIFIC_NAME, PARAMETER_NAME";
AddParameter(command, "@Catalog", DbType.StringFixedLength, 4000);
AddParameter(command, "@Owner", DbType.StringFixedLength, 4000);
AddParameter(command, "@Name", DbType.StringFixedLength, 4000);
AddParameter(command, "@Parameter", DbType.StringFixedLength, 4000);
break;
case "Tables":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE " +
"from INFORMATION_SCHEMA.TABLES where" +
" (TABLE_CATALOG = @catalog or (@catalog is null)) and " +
"(TABLE_SCHEMA = @owner or (@owner is null))and " +
"(TABLE_NAME = @name or (@name is null)) and " +
"(TABLE_TYPE = @table_type or (@table_type is null))";
AddParameter(command, "@catalog", DbType.StringFixedLength, 8);
AddParameter(command, "@owner", DbType.StringFixedLength, 3);
AddParameter(command, "@name", DbType.StringFixedLength, 11);
AddParameter(command, "@table_type", DbType.StringFixedLength, 10);
break;
case "Columns":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, " +
"ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, " +
"CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, " +
"NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, " +
"DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, " +
"CHARACTER_SET_NAME, COLLATION_CATALOG from INFORMATION_SCHEMA.COLUMNS" +
" where (TABLE_CATALOG = @Catalog or (@Catalog is null)) and (" +
"TABLE_SCHEMA = @Owner or (@Owner is null)) and (TABLE_NAME = @table" +
" or (@Table is null)) and (COLUMN_NAME = @column or (@Column is null" +
")) order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME";
AddParameter(command, "@Catalog", DbType.StringFixedLength, 4000);
AddParameter(command, "@Owner", DbType.StringFixedLength, 4000);
AddParameter(command, "@Table", DbType.StringFixedLength, 4000);
AddParameter(command, "@Column", DbType.StringFixedLength, 4000);
break;
case "Users":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select uid, name as user_name, createdate, updatedate from sysusers" +
" where (name = @Name or (@Name is null))";
AddParameter(command, "@Name", DbType.StringFixedLength, 4000);
break;
case "Views":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CHECK_OPTION, " +
"IS_UPDATABLE from INFORMATION_SCHEMA.VIEWS where (TABLE_CATALOG" +
" = @Catalog or (@Catalog is null)) TABLE_SCHEMA = @Owner or " +
"(@Owner is null)) and (TABLE_NAME = @table or (@Table is null))" +
" order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME";
AddParameter(command, "@Catalog", DbType.StringFixedLength, 4000);
AddParameter(command, "@Owner", DbType.StringFixedLength, 4000);
AddParameter(command, "@Table", DbType.StringFixedLength, 4000);
break;
case "ViewColumns":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME, TABLE_CATALOG, " +
"TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from " +
"INFORMATION_SCHEMA.VIEW_COLUMN_USAGE where (VIEW_CATALOG = " +
"@Catalog (@Catalog is null)) and (VIEW_SCHEMA = @Owner (@Owner" +
" is null)) and (VIEW_NAME = @Table or (@Table is null)) and " +
"(COLUMN_NAME = @Column or (@Column is null)) order by " +
"VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME";
AddParameter(command, "@Catalog", DbType.StringFixedLength, 4000);
AddParameter(command, "@Owner", DbType.StringFixedLength, 4000);
AddParameter(command, "@Table", DbType.StringFixedLength, 4000);
AddParameter(command, "@Column", DbType.StringFixedLength, 4000);
break;
case "UserDefinedTypes":
command = CreateCommand();
command.Connection = this;
command.CommandText = "select assemblies.name as assembly_name, types.assembly_class " +
"as udt_name, ASSEMBLYPROPERTY(assemblies.name, 'VersionMajor') " +
"as version_major, ASSEMBLYPROPERTY(assemblies.name, 'VersionMinor') " +
"as version_minor, ASSEMBLYPROPERTY(assemblies.name, 'VersionBuild') " +
"as version_build, ASSEMBLYPROPERTY(assemblies.name, 'VersionRevision') " +
"as version_revision, ASSEMBLYPROPERTY(assemblies.name, 'CultureInfo') " +
"as culture_info, ASSEMBLYPROPERTY(assemblies.name, 'PublicKey') " +
"as public_key, is_fixed_length, max_length, Create_Date, " +
"Permission_set_desc from sys.assemblies as assemblies join " +
"sys.assembly_types as types on assemblies.assembly_id = types.assembly_id" +
" where (assemblies.name = @AssemblyName or (@AssemblyName is null)) and " +
"(types.assembly_class = @UDTName or (@UDTName is null))";
AddParameter(command, "@AssemblyName", DbType.StringFixedLength, 4000);
AddParameter(command, "@UDTName", DbType.StringFixedLength, 4000);
break;
case "MetaDataCollections":
return(MetaDataCollections.Instance);
case "DataSourceInformation":
throw new NotImplementedException();
case "DataTypes":
return(DataTypes.Instance);
case "ReservedWords":
return(ReservedWords.Instance);
case "Restrictions":
return(Restrictions.Instance);
}
for (int i = 0; i < length; i++)
{
command.Parameters [i].Value = restrictionValues [i];
}
DbDataAdapter dataAdapter = DbProviderFactory.CreateDataAdapter();
dataAdapter.SelectCommand = command;
dataAdapter.Fill(dataTable);
return(dataTable);
}
public DataTable getDataTable(String queryString, params dynamic[] args)
{
DataTable table = null;
queryString = queryString.Replace("\r", " ").Replace("\n", "").Replace("\t", " ");
Console.WriteLine("Select cmd: " + queryString);
string lib = GetProviderFactoryLib(providerName);
System.Data.Common.DbProviderFactory provider = RegisterProvider(providerName);
DbConnection con = provider.CreateConnection();
con.ConnectionString = BuildConnectionString(providerName, this.dbName);
dataSet = new DataSet();
if (dataAdapter == null)
{
if (this.tableName == null)
{
SetTableName(queryString);
}
tableStruct = RetrieveTableStructure(this.tableName);
if (tableStruct.Columns.Count == 0)
{
return(null);
}
dataAdapter = provider.CreateDataAdapter();
dataAdapter.SelectCommand = BuildSelectCommand(provider, con, queryString, args);;
dataAdapter.DeleteCommand = BuildDeleteCommand(provider, con);
dataAdapter.UpdateCommand = BuildUpdateCommand(provider, con);
dataAdapter.InsertCommand = BuildInsertCommand(provider, con);
dataAdapter.AcceptChangesDuringFill = true;
dataAdapter.AcceptChangesDuringUpdate = true;
dataAdapter.ContinueUpdateOnError = false;
}
if (con.ConnectionString != dataAdapter.SelectCommand.Connection.ConnectionString)
{
dataAdapter.SelectCommand = BuildSelectCommand(provider, con, queryString, args);;
dataAdapter.DeleteCommand = BuildDeleteCommand(provider, con);
dataAdapter.UpdateCommand = BuildUpdateCommand(provider, con);
dataAdapter.InsertCommand = BuildInsertCommand(provider, con);
}
if (args.Length > 0)
{
dataAdapter.SelectCommand.Parameters.Clear();
foreach (var param in args)
{
DbParameter par = provider.CreateParameter();
par.Direction = ParameterDirection.Input;
par.ParameterName = param.Name;
par.Value = param.Value;
dataAdapter.SelectCommand.Parameters.Add(par);
}
}
try
{
con.Open();
//BuildTableMapping(dataAdapter);
dataAdapter.Fill(dataSet, this.tableName);
table = dataSet.Tables[this.tableName];
dataAdapter.MissingMappingAction = MissingMappingAction.Passthrough;
if (identityColumn != null)
{
if (table.Columns.Contains(identityColumn))
{
table.PrimaryKey = new DataColumn[] { table.Columns[identityColumn] };
table.Columns[identityColumn].AllowDBNull = true;
}
else
{
throw new Exception(String.Format("Tabela {0}, nu contine coloana Identity ={1}", tableName, identityColumn));
}
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
con.Close();
}
return(table);
}
/// <summary>
/// 返回DataTable
/// </summary>
/// <param name="dbTransaction"></param>
/// <param name="commandType"></param>
/// <param name="commandText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable(DbProviderFactory factory, DbTransaction dbTransaction, CommandType commandType, string commandText, params DbParameter[] commandParameters)
{
if (dbTransaction == null)
{
throw new ArgumentNullException("dbTransaction");
}
if (dbTransaction != null && dbTransaction.Connection == null)
{
throw new ArgumentNullException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
}
using (DbCommand dbCommand = factory.CreateCommand())
{
PrepareCommand(dbCommand, dbTransaction.Connection, dbTransaction, commandType, commandText, commandParameters);
using (DbDataAdapter da = factory.CreateDataAdapter())
{
da.SelectCommand = dbCommand;
DataSet ds = new DataSet();
da.Fill(ds);
dbCommand.Parameters.Clear();
DataTable dt = ds.Tables[0];
return dt;
}
}
}
// Utilizes the SQLiteCommandBuilder,
// which in turn utilizes SQLiteDataReader's GetSchemaTable() functionality
// This insert is slow because it must raise callbacks before and after every update.
// For a fast update, see the FastInsertMany function beneath this one
internal static void DataAdapter(DbProviderFactory fact, DbConnection cnn, bool bWithIdentity)
{
using (DbTransaction dbTrans = cnn.BeginTransaction())
{
using (DbDataAdapter adp = fact.CreateDataAdapter())
{
using (DbCommand cmd = cnn.CreateCommand())
{
cmd.Transaction = dbTrans;
cmd.CommandText = "SELECT * FROM TestCase WHERE 1 = 2";
adp.SelectCommand = cmd;
using (DbCommandBuilder bld = fact.CreateCommandBuilder())
{
bld.DataAdapter = adp;
using (adp.InsertCommand = (DbCommand)((ICloneable)bld.GetInsertCommand()).Clone())
{
if (bWithIdentity)
{
adp.InsertCommand.CommandText += ";SELECT last_insert_rowid() AS [ID]";
adp.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
}
bld.DataAdapter = null;
using (DataTable tbl = new DataTable())
{
adp.Fill(tbl);
for (int n = 0; n < 10000; n++)
{
DataRow row = tbl.NewRow();
row[1] = n + (50000 * ((bWithIdentity == true) ? 2 : 1));
tbl.Rows.Add(row);
}
Console.WriteLine(String.Format(" Inserting using CommandBuilder and DataAdapter\r\n ->{0} (10,000 rows) ...", (bWithIdentity == true) ? "(with identity fetch)" : ""));
int dtStart = Environment.TickCount;
adp.Update(tbl);
int dtEnd = Environment.TickCount;
dtEnd -= dtStart;
Console.Write(String.Format(" -> Insert Ends in {0} ms ... ", (dtEnd)));
dtStart = Environment.TickCount;
dbTrans.Commit();
dtEnd = Environment.TickCount;
dtEnd -= dtStart;
Console.WriteLine(String.Format("Commits in {0} ms", (dtEnd)));
}
}
}
}
}
}
}
/// <summary>
///
/// </summary>
/// <param name="dbConnection"></param>
/// <param name="commandType"></param>
/// <param name="commandText"></param>
/// <param name="commandParameters"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(DbProviderFactory factory, DbConnection dbConnection, CommandType commandType, string commandText, params DbParameter[] commandParameters)
{
if (dbConnection == null)
{
throw new ArgumentNullException("dbConnection");
}
using (DbCommand dbCommand = factory.CreateCommand())
{
PrepareCommand(dbCommand, dbConnection, (DbTransaction)null, commandType, commandText, commandParameters);
using (DbDataAdapter da = factory.CreateDataAdapter())
{
da.SelectCommand = dbCommand;
DataSet ds = new DataSet();
da.Fill(ds);
dbCommand.Parameters.Clear();
return ds;
}
}
}