public static DataTable ExecuteQuery(this BaseDatabase database, string query, params IDbDataParameter[] parameters)
{
DataTable dataTable = new DataTable();
using (IDbConnection connection = database.CreateConnection())
{
using (IDbCommand command = database.CreateCommand(query))
{
command.Connection = connection;
connection.Open();
foreach (IDbDataParameter param in parameters)
{
command.Parameters.Add(param);
}
using (IDataReader dataReader = command.ExecuteReader())
{
dataTable.Load(dataReader);
}
}
}
return(dataTable);
}
private void DropTable(BaseDatabase database, string tableName)
{
using var conn = database.CreateConnection();
conn.Open();
using var cmd = conn.CreateCommand();
cmd.CommandText = $"DROP TABLE {tableName}";
cmd.ExecuteNonQuery();
}
private int Count(BaseDatabase database)
{
var conn = database.CreateConnection();
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = $"SELECT COUNT(*) FROM {_tableName}";
return(Convert.ToInt32(cmd.ExecuteScalar()));
}
}
private string CreateTable(BaseDatabase database)
{
var tableName = StringUtils.Random(10);
var conn = database.CreateConnection();
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = $"CREATE TABLE {tableName} (col1 INT , col2 VARCHAR(100))";
cmd.ExecuteNonQuery();
}
return(tableName);
}
public static int ExecuteNonQuery(this BaseDatabase database, string query)
{
int rowsAffected = -1;
using (IDbConnection connection = database.CreateConnection())
{
using (IDbCommand command = database.CreateCommand(query))
{
command.Connection = connection;
connection.Open();
rowsAffected = command.ExecuteNonQuery();
}
}
return(rowsAffected);
}
private long CountTable(BaseDatabase database, string tableName)
{
using var conn = database.CreateConnection();
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = $"SELECT COUNT(*) FROM {tableName}";
var r = cmd.ExecuteScalar();
if (r is long)
{
return((long)r);
}
return((int)r);
}
}
public static object ExecuteScalar(this BaseDatabase database, string query, params IDbDataParameter[] parameters)
{
object result = null;
using (IDbConnection connection = database.CreateConnection())
{
using (IDbCommand command = database.CreateCommand(query))
{
command.Connection = connection;
connection.Open();
foreach (IDbDataParameter param in parameters)
{
command.Parameters.Add(param);
}
result = command.ExecuteScalar();
}
}
return(result);
}
public static int ExecuteNonQuery(this BaseDatabase database, string query, params IDbDataParameter[] parameters)
{
int rowsAffected = -1;
using (IDbConnection connection = database.CreateConnection())
{
using (IDbCommand command = database.CreateCommand(query))
{
command.Connection = connection;
connection.Open();
foreach (IDbDataParameter param in parameters)
{
command.Parameters.Add(param);
}
rowsAffected = command.ExecuteNonQuery();
}
}
return(rowsAffected);
}
private async Task <DbConnection> CreateConnection(SqlExportToCsvInput input, CancellationToken token)
{
BaseDatabase database = GetDataBase(input.ConnectionName, true);
return(database.CreateConnection());
}