/// <summary>
/// Uses a select statement and the ADO.NET CommandBuilder
/// to generate Insert,Update, and Delete statements, and load them onto an adapter
/// </summary>
/// <param name="conn"></param>
/// <param name="client"></param>
/// <param name="sql"></param>
/// <returns></returns>
public static DbDataAdapter GetMagicAdapter(DbConnection conn, IDataClient client, string sql)
{
var cmd = client.GetCommand(sql, conn);
var dba = client.GetDataAdapter(cmd);
var builder = client.GetCommandBuilder(dba);
dba.InsertCommand = builder.GetInsertCommand(true);
dba.DeleteCommand = builder.GetDeleteCommand(true);
dba.UpdateCommand = builder.GetUpdateCommand(true);
if (dba.InsertCommand != null)
{
dba.InsertCommand.CommandTimeout = client.QueryTimeout;
}
if (dba.DeleteCommand != null)
{
dba.DeleteCommand.CommandTimeout = client.QueryTimeout;
}
if (dba.UpdateCommand != null)
{
dba.UpdateCommand.CommandTimeout = client.QueryTimeout;
}
return(dba);
}
/// <summary>
/// Returns true if a particular table has a particular column
/// </summary>
/// <param name="columnname"></param>
/// <param name="tablename"></param>
/// <param name="conn"></param>
/// <param name="client"></param>
/// <returns></returns>
public static bool HasColumn(string columnname, string tablename, DbConnection conn, IDataClient client)
{
string sql = string.Format("select * from {0} WHERE 0 = 1", tablename);
var dt = GetMagicTable(conn, client, sql);
var cmd = client.GetCommand(sql, conn);
var dba = client.GetDataAdapter(cmd);
dba.Fill(dt);
if (dt != null)
{
return(dt.Columns.Contains(columnname));
}
return(false);
}
/// <summary>
/// 'Magically' populates a DataTable object with the contents of your sql query
/// </summary>
/// <param name="conn"></param>
/// <param name="client"></param>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable GetMagicTable(DbConnection conn, IDataClient client, string sql)
{
DataTable dt = new DataTable();
try
{
var cmd = client.GetCommand(sql, conn);
var dba = client.GetDataAdapter(cmd);
dba.Fill(dt);
return(dt);
}
catch (Exception ex)
{
_log.Error("error during select", ex);
}
return(null);
}
/// <summary>
/// Uses a select statement and the ADO.NET CommandBuilder
/// to generate Insert,Update, and Delete statements, and load them onto an adapter
/// </summary>
/// <param name="conn"></param>
/// <param name="client"></param>
/// <param name="sql"></param>
/// <returns></returns>
public static DbDataAdapter GetMagicAdapter(DbConnection conn, IDataClient client, string sql)
{
var cmd = client.GetCommand(sql, conn);
var dba = client.GetDataAdapter(cmd);
var builder = client.GetCommandBuilder(dba);
dba.InsertCommand = builder.GetInsertCommand(true);
dba.DeleteCommand = builder.GetDeleteCommand(true);
dba.UpdateCommand = builder.GetUpdateCommand(true);
if (dba.InsertCommand != null)
dba.InsertCommand.CommandTimeout = client.QueryTimeout;
if (dba.DeleteCommand != null)
dba.DeleteCommand.CommandTimeout = client.QueryTimeout;
if (dba.UpdateCommand != null)
dba.UpdateCommand.CommandTimeout = client.QueryTimeout;
return dba;
}
/// <summary>
/// Returns true if a particular table has a particular column
/// </summary>
/// <param name="columnname"></param>
/// <param name="tablename"></param>
/// <param name="conn"></param>
/// <param name="client"></param>
/// <returns></returns>
public static bool HasColumn(string columnname, string tablename, DbConnection conn, IDataClient client)
{
string sql = string.Format("select * from {0} WHERE 0 = 1", tablename);
var dt = GetMagicTable(conn, client, sql);
var cmd = client.GetCommand(sql, conn);
var dba = client.GetDataAdapter(cmd);
dba.Fill(dt);
if (dt != null)
{
return dt.Columns.Contains(columnname);
}
return false;
}
/// <summary>
/// 'Magically' populates a DataTable object with the contents of your sql query
/// </summary>
/// <param name="conn"></param>
/// <param name="client"></param>
/// <param name="sql"></param>
/// <returns></returns>
public static DataTable GetMagicTable(DbConnection conn, IDataClient client, string sql)
{
DataTable dt = new DataTable();
try
{
var cmd = client.GetCommand(sql, conn);
var dba = client.GetDataAdapter(cmd);
dba.Fill(dt);
return dt;
}
catch (Exception ex)
{
_log.Error("error during select", ex);
}
return null;
}
