public DataTable GetDataTable(string sql, DataTable dt, string tableName)
{
if (string.IsNullOrEmpty(sql))
{
throw new ArgumentNullException("sql", "A SQL query or stored procedure name is required");
}
try
{
using (DbDataAdapter adapter = ProviderFactory.CreateDataAdapter())
{
Command.CommandText = sql;
adapter.SelectCommand = Command;
if (dt == null)
{
dt = new DataTable();
}
adapter.Fill(dt);
if (!string.IsNullOrEmpty(tableName))
{
dt.TableName = tableName;
}
return(dt);
}
}
finally
{
CloseConnection(); // Clears parameters
}
}
public int DeleteDataTable(DataTable dt, string sql)
{
if (string.IsNullOrEmpty(sql))
{
throw new ArgumentNullException("sql", "A SQL query or stored procedure name is required");
}
if (dt == null)
{
throw new ArgumentNullException("dt", "DataSet cannot be null.");
}
DbDataAdapter adapter = null;
try
{
adapter = ProviderFactory.CreateDataAdapter();
adapter.DeleteCommand = Command;
adapter.DeleteCommand.CommandText = sql;
return(adapter.Update(dt));
}
finally
{
if (adapter.DeleteCommand != null)
{
adapter.DeleteCommand.Dispose();
}
adapter.Dispose();
}
}
private void RunQuery()
{
var regexPattern = @"(?<=^([^']|'[^']*')*)(update |insert |delete |merge )";
var uhOh = Regex.Match(SqlQuery.Sql.ToLower(), regexPattern, RegexOptions.Multiline | RegexOptions.IgnoreCase).Success;
var cudWarning = "WARNING! The following query appears to contain an INSERT/UPDATE/DELETE/MERGE operation, but the CUD switch was not used (which is recommended). Are you sure you want to execute this query?";
if (uhOh && !ShouldContinue(SqlQuery.Sql, cudWarning))
{
WriteWarning("Not running query!");
return;
}
if (!ShouldProcess("Database server", "Run Query:`" + SqlQuery.Sql + "`"))
{
WriteWarning("Not running query!");
return;
}
var command = GetDbCommand();
var dataTable = new DataTable();
var adapter = ProviderFactory.CreateDataAdapter();
adapter.SelectCommand = command;
using (adapter)
{
adapter.Fill(dataTable);
}
WriteVerbose("Query returned " + dataTable.Rows.Count + " rows.");
var outputData = GetDataRowArrayFromTable(dataTable);
ExecuteCallbackData(outputData);
WriteObject(outputData);
}
public DataTable SelectTable(string connectionString, string tableName, int nrows = int.MaxValue)
{
using (var conn = ProviderFactory.CreateConnection())
{
conn.ConnectionString = connectionString;
conn.Open();
var result = new DataTable();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = string.Format("select * from [{0}]", tableName);
using (var da = ProviderFactory.CreateDataAdapter())
{
da.SelectCommand = cmd;
da.FillSchema(result, SchemaType.Source);
}
int n = 0;
var items = Array.CreateInstance(typeof(object), result.Columns.Count) as object[];
try
{
using (var rdr = cmd.ExecuteReader())
{
while (n < nrows && rdr.Read())
{
rdr.GetValues(items);
result.Rows.Add(items);
n++;
}
}
}
catch (DbException ex)
{
Logger.Write(ex, System.Diagnostics.TraceEventType.Error,
new Dictionary <string, object>()
{
{ "Action", string.Format("Read Table [{0}] failed on data provider: {1}", tableName, Name) },
{ "sql", cmd.CommandText },
{ "connectionString", connectionString },
{ "tableName", tableName },
{ "nRows", nrows }
},
"Error selecting data from table {0}", tableName);
// BUG: this throw is not caught in (some) wizard steps. One cause of the exceptions has been fixed, so to test that
// this throw gets caught now, we need to temporarily hard-code divide-by-zero inside the try block above.
throw new DataProviderControllerException(
string.Format("Read Table [{0}] failed on data provider: {1}", tableName, Name)
, ex);
}
}
return(result);
}
}
//************************************************************************
/// <summary>
/// コンストラクタ
/// </summary>
//************************************************************************
public BaseDA()
{
// ロガーを取得
m_logger = LogManager.GetLogger(this.GetType());
// DBプロバイダファクトリ作成
ProviderFactory = DbProviderFactories.GetFactory(Properties.Settings.Default.DbProviderFactory);
// データアダプタはfactoryから作成する
DataAdapter = ProviderFactory.CreateDataAdapter();
}
public virtual DataSet ExecuteDataSet(DbCommand dm)
{
using (var conn = CreateConnection())
{
dm.Connection = conn;
using (var myDataAdapter = ProviderFactory.CreateDataAdapter())
{
var myDataSet = new DataSet();
if (myDataAdapter != null)
{
myDataAdapter.SelectCommand = dm;
myDataAdapter.Fill(myDataSet);
}
myDataSet.RemotingFormat = SerializationFormat.Binary;
return(myDataSet);
}
}
}
public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, out bool Result)
{
Result = false;
DBOpenResults firstConnectionState = DBOpenResults.Failed;
try
{
firstConnectionState = Open();
if (!IsConnectionOpen && firstConnectionState == DBOpenResults.Failed)
{
return(null);
}
DataSet dataSet = new DataSet();
bool subResult = false;
var dataAdapter = ProviderFactory.CreateDataAdapter();
dataAdapter.SelectCommand = BuildQueryCommand(storedProcName, parameters, out subResult);
if (subResult)
{
dataAdapter.Fill(dataSet, tableName);
Result = true;
return(dataSet);
}
else
{
Result = false;
return(null);
}
}
catch (System.Exception e)
{
SetLastExceptionError(e);
Result = false;
return(null);
}
finally
{
if (KeepConnectionStates)
{
Close(firstConnectionState);
}
}
}