/// <summary>
/// 执行指定的查询命令，并返回相应的数据读取器。
/// </summary>
/// <param name="Command">要执行的查询。</param>
/// <returns></returns>
public override IDataReader ExecuteReader(CommandBuilder Command)
{
ClearError();
SqlCommand DbCommand = new SqlCommand();
try
{
DbCommand.Connection = (SqlConnection)Connect();
DbCommand.CommandText = Command.Parsing(parserAdapter);
foreach (object p in Command.CommandParameters)
{
DbCommand.Parameters.Add((SqlParameter)p);
}
DbCommand.CommandType = Command.CommandType;
DbCommand.Connection.Open();
SqlDataReader DbReader = DbCommand.ExecuteReader(CommandBehavior.CloseConnection);
return(DbReader);
}
catch (Exception Ex)
{
_Error = new DbError(Ex.Message, DbCommand.CommandText, DbCommand.Connection.ConnectionString);
return(null);
}
finally
{
DbCommand.Parameters.Clear();
DbCommand.Dispose();
}
}
/// <summary>
/// 执行指定的查询命令，并返回相应的数据读取器。
/// </summary>
/// <param name="Command">要执行的查询。</param>
/// <returns></returns>
public override IDataReader ExecuteReader(CommandBuilder Command)
{
ClearError();
NpgsqlCommand DbCommand = new NpgsqlCommand();
try
{
DbCommand.Connection = (NpgsqlConnection)Connect();
DbCommand.CommandText = Command.Parsing(parserAdapter);
foreach (object p in Command.CommandParameters)
{
DbCommand.Parameters.Add((NpgsqlParameter)p);
}
DbCommand.CommandType = Command.CommandType;
if (ConnectionPoolAvailable)
{
DbaDataReader DbReader = new DbaDataReader(DbCommand.ExecuteReader(), DbCommand.Connection);
DbReader.Closed += (IDbConnection conn) => { ConnectionPool.ReleaseConnection(conn); };
return(DbReader);
}
return(DbCommand.ExecuteReader(CommandBehavior.CloseConnection));
}
catch (Exception Ex)
{
_Error = new DbError(Ex.Message, DbCommand.CommandText, DbCommand.Connection.ConnectionString);
return(null);
}
finally
{
DbCommand.Parameters.Clear();
DbCommand.Dispose();
}
}
/// <summary>
/// 执行指定的 SQL 命令，并返回受影响的记录数。
/// </summary>
/// <param name="Command">要执行的命令。</param>
/// <returns></returns>
public override int ExecuteNoneQuery(CommandBuilder Command)
{
ClearError();
SqlCommand DbCommand = new SqlCommand();
try
{
DbCommand.Connection = (SqlConnection)Connect();
DbCommand.CommandText = Command.Parsing(parserAdapter);
foreach (object p in Command.CommandParameters)
{
DbCommand.Parameters.Add((SqlParameter)p);
}
DbCommand.CommandType = Command.CommandType;
DbCommand.Connection.Open();
int result = DbCommand.ExecuteNonQuery();
if (result > 0)
{
QueryLastIdentity(DbCommand);
}
return(result);
}
catch (Exception Ex)
{
_Error = new DbError(Ex.Message, DbCommand.CommandText, ConnectionString);
return(-1);
}
finally
{
DbCommand.Connection.Close();
DbCommand.Connection.Dispose();
DbCommand.Parameters.Clear();
DbCommand.Dispose();
}
}
/// <summary>
/// 指行指定的查询命令，并返回数据集。
/// </summary>
/// <param name="Command">要执行的查询。</param>
/// <returns></returns>
public override T ExecuteQuery <T>(CommandBuilder Command)
{
if (!typeof(T).Equals(typeof(DataTable)))
{
throw (new Exception("目标类型只能是 System.Data.DataTable."));
}
ClearError();
SqlDataAdapter DbAdapter = new SqlDataAdapter(Command.Parsing(parserAdapter), (SqlConnection)Connect());
try
{
foreach (object p in Command.CommandParameters)
{
DbAdapter.SelectCommand.Parameters.Add((SqlParameter)p);
}
DbAdapter.SelectCommand.CommandType = Command.CommandType;
DataTable dt = new DataTable();
DbAdapter.Fill(dt);
return((T)dt);
}
catch (Exception Ex)
{
_Error = new DbError(Ex.Message, DbAdapter.SelectCommand.CommandText, ConnectionString);
return(null);
}
finally
{
DbAdapter.SelectCommand.Connection.Close();
DbAdapter.SelectCommand.Connection.Dispose();
DbAdapter.SelectCommand.Parameters.Clear();
DbAdapter.Dispose();
}
}
/// <summary>
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。所有其他的列和行将被忽略。
/// </summary>
/// <param name="Command">要执行的查询。</param>
/// <returns></returns>
public override object ExecuteScalar(CommandBuilder Command)
{
ClearError();
NpgsqlCommand DbCommand = new NpgsqlCommand();
try
{
DbCommand.Connection = (NpgsqlConnection)Connect();
DbCommand.CommandText = Command.Parsing(parserAdapter);
foreach (object p in Command.CommandParameters)
{
DbCommand.Parameters.Add((NpgsqlParameter)p);
}
DbCommand.CommandType = Command.CommandType;
DbCommand.Connection.Open();
object result = DbCommand.ExecuteScalar();
return(result);
}
catch (Exception Ex)
{
_Error = new DbError(Ex.Message, DbCommand.CommandText, DbCommand.Connection.ConnectionString);
return(-1);
}
finally
{
DbCommand.Connection.Close();
DbCommand.Connection.Dispose();
DbCommand.Parameters.Clear();
DbCommand.Dispose();
}
}
internal List <RecordCache <TTable> > Excute <TTable>(string sqlCommand, List <Parameter> parameters, List <bool> fieldsUsable) where TTable : new()
{
dbError = new DbError();
if (string.IsNullOrEmpty(connection.ConnectionString))
{
return(null);
}
Func <DbDataReader, TTable> readRowFunc = ExpressionFunc.GetReader <TTable>(fieldsUsable);
Func <TTable, TTable> cloneFunc = ExpressionFunc.PrimaryKeyClone <TTable>();
if (readRowFunc == null || cloneFunc == null)
{
return(null);
}
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
try
{
// Create the command and open the connection
var command = connection.CreateCommand();
command.CommandText = sqlCommand;
command.CommandTimeout = 15;
command.CommandType = CommandType.Text;
if (parameters != null)
{
for (int index = 0; index < parameters.Count; index++)
{
var dbParameter = command.CreateParameter();
dbParameter.ParameterName = parameters[index].Name;
dbParameter.Value = parameters[index].Value;
command.Parameters.Add(dbParameter);
}
}
// Create the DataReader to retrieve data
using (var dr = command.ExecuteReader(CommandBehavior.CloseConnection))
{
return(ReadRecords <TTable>(dr, readRowFunc, cloneFunc));
}
}
catch (Exception e)
{
dbError.Code = ErrorCode.DatabaseException;
dbError.Text = e.Message;
return(null);
}
}
/// <summary>
/// 若指定名称的表在数据库中存在则返回 true，否则返回 false .
/// </summary>
/// <param name="tableName">表名称.</param>
/// <param name="commander">在该 DbCommand 上执行查询（为空时则自动创建，默认值 null）.</param>
/// <returns></returns>
public override bool TableExists(string tableName, IDbCommand commander = null)
{
bool releaseCommander = false;
if (commander == null)
{
commander = CreateDbCommand();
commander.Connection = Connect();
releaseCommander = true;
}
ClearError();
try
{
commander.CommandText = "select count(1) from information_schema.tables where table_name = :tableName";
commander.CommandType = CommandType.Text;
commander.Parameters.Add(CreateParameter("tableName", tableName));
object result = commander.ExecuteScalar();
if (result == null)
{
return(false);
}
return(Convert.ToInt32(result) > 0);
}
catch (Exception Ex)
{
_Error = new DbError(Ex.Message, commander.CommandText, commander.Connection.ConnectionString);
return(false);
}
finally
{
if (releaseCommander)
{
if (ConnectionPoolAvailable)
{
ConnectionPool.ReleaseConnection(commander.Connection);
}
else
{
commander.Connection.Close();
commander.Connection.Dispose();
}
commander.Parameters.Clear();
commander.Dispose();
}
}
}
/// <summary>
/// 若指定名称的表在数据库中存在则返回 true，否则返回 false .
/// </summary>
/// <param name="tableName">表名称.</param>
/// <param name="commander">在该 DbCommand 上执行查询（为空时则自动创建，默认值 null）.</param>
/// <returns></returns>
public override bool TableExists(string tableName, IDbCommand commander = null)
{
bool releaseCommander = false;
if (commander == null)
{
commander = CreateDbCommand();
commander.Connection = Connect();
releaseCommander = true;
}
ClearError();
try
{
commander.CommandText = "SELECT COUNT(1) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ?tableName";
commander.CommandType = CommandType.Text;
commander.Parameters.Add(CreateParameter("tableName", tableName));
object result = commander.ExecuteScalar();
if (result == null)
{
return(false);
}
return(Convert.ToInt32(result) > 0);
}
catch (Exception Ex)
{
_Error = new DbError(Ex.Message, commander.CommandText, commander.Connection.ConnectionString);
return(false);
}
finally
{
if (releaseCommander)
{
if (ConnectionPoolAvailable)
{
ConnectionPool.ReleaseConnection(commander.Connection);
}
else
{
commander.Connection.Close();
commander.Connection.Dispose();
}
commander.Parameters.Clear();
commander.Dispose();
}
}
}
internal DbError Excute <TTable>(string sqlCommand, List <Parameter> parameters, DbTransaction transaction) where TTable : new()
{
DbError dbError = new DbError();
int result = -1;
try
{
// Create the command and open the connection
var command = connection.CreateCommand();
command.CommandText = sqlCommand;
command.CommandTimeout = 15;
command.CommandType = CommandType.Text;
command.Transaction = transaction;
command.Parameters.Clear();
if (parameters != null)
{
for (int index = 0; index < parameters.Count; index++)
{
var dbParameter = command.CreateParameter();
dbParameter.ParameterName = parameters[index].Name;
dbParameter.Value = parameters[index].Value;
command.Parameters.Add(dbParameter);
}
}
result = command.ExecuteNonQuery();
}
catch (Exception e)
{
dbError.Code = ErrorCode.DatabaseException;
dbError.Text = e.Message;
}
finally
{
if (result == 0)
{
dbError.Code = ErrorCode.Conflict;
dbError.Text = "The record has be changed.";
}
}
return(dbError);
}
public TableQuery(DbSet dbSet)
{
this._dbSet = dbSet;
this._dbError = new DbError();
this._translateQuery = new QueryTranslator();
this._sqlBuilder = new SqlBuilder();
this._parameters = new List <Parameter>();
this._excuteQuery = new Query(dbSet.DbConnection);
this._fieldsUsable = new List <bool>();
TableSchema tableSchema = TableSchemaResolver.GetTableSchema(typeof(TTable));
for (int i = 0, count = tableSchema.FieldsSchema.Count; i < count; i++)
{
this._fieldsUsable.Add(true);
}
}
/// <summary>
/// 执行查询，并返回查询所返回的结果集中第一行的第一列。所有其他的列和行将被忽略。
/// </summary>
/// <param name="Command">要执行的查询。</param>
/// <returns></returns>
public override object ExecuteScalar(CommandBuilder Command)
{
ClearError();
MySqlCommand DbCommand = new MySqlCommand();
try
{
DbCommand.Connection = (MySqlConnection)Connect();
DbCommand.CommandText = Command.Parsing(parserAdapter);
foreach (object p in Command.CommandParameters)
{
DbCommand.Parameters.Add((MySqlParameter)p);
}
DbCommand.CommandType = Command.CommandType;
object result = DbCommand.ExecuteScalar();
return(result);
}
catch (Exception Ex)
{
_Error = new DbError(Ex.Message, DbCommand.CommandText, DbCommand.Connection.ConnectionString);
return(-1);
}
finally
{
if (ConnectionPoolAvailable)
{
ConnectionPool.ReleaseConnection(DbCommand.Connection);
}
else
{
DbCommand.Connection.Close();
DbCommand.Connection.Dispose();
}
DbCommand.Parameters.Clear();
DbCommand.Dispose();
}
}
/// <summary>
/// 指行指定的查询命令，并返回数据集。
/// </summary>
/// <param name="Command">要执行的查询。</param>
/// <returns></returns>
public override DataTable QueryDataTable(CommandBuilder Command)
{
ClearError();
NpgsqlDataAdapter DbAdapter = new NpgsqlDataAdapter(Command.Parsing(parserAdapter), (NpgsqlConnection)Connect());
try
{
foreach (object p in Command.CommandParameters)
{
DbAdapter.SelectCommand.Parameters.Add((NpgsqlParameter)p);
}
DbAdapter.SelectCommand.CommandType = Command.CommandType;
DataTable table = new DataTable();
DbAdapter.Fill(table);
return(table);
}
catch (Exception Ex)
{
_Error = new DbError(Ex.Message, DbAdapter.SelectCommand.CommandText, ConnectionString);
return(null);
}
finally
{
if (ConnectionPoolAvailable)
{
ConnectionPool.ReleaseConnection(DbAdapter.SelectCommand.Connection);
}
else
{
DbAdapter.SelectCommand.Connection.Close();
DbAdapter.SelectCommand.Connection.Dispose();
}
DbAdapter.SelectCommand.Parameters.Clear();
DbAdapter.Dispose();
}
}
/// <summary>
/// 清除以前产生的错误信息。
/// </summary>
protected void ClearError()
{
_Error = null;
}