/// <summary>
/// 执行统计的方法
/// </summary>
/// <param name="SQLText">SQL命令</param>
/// <returns>返回object对象代表统计数据或者结果的第一列第一行</returns>
public object ExecuteScalar(string SQLText)
{
if (IsProcedure(SQLText))
{
mCommand.CommandType = CommandType.StoredProcedure;
}
else
{
mCommand.CommandType = CommandType.Text;
}
mCommand.CommandText = SQLText;
mCommand.CommandTimeout = 5;
try
{
mCommand.Connection.Open();
return(mCommand.ExecuteScalar());
}
catch (Exception e)
{
NDapperLog.write(e.ToString(), "ExecuteScalar");
return("-1");
}
finally
{
mCommand.Connection.Close();
ClearParameters();
}
}
/// <summary>
/// 执行Access数据库
/// </summary>
/// <param name="SQLText"></param>
/// <returns></returns>
public int ExecuteNonQueryAccess(string SQLText, out string errTip)
{
try
{
errTip = null;
if (IsProcedure(SQLText))
{
mCommand.CommandType = CommandType.StoredProcedure;
}
else
{
mCommand.CommandType = CommandType.Text;
}
mCommand.CommandText = SQLText;
mCommand.CommandTimeout = 5;
mCommand.Connection.Open();
return(mCommand.ExecuteNonQuery());
}
catch (Exception e)
{
errTip = e.Message;
NDapperLog.write(e.ToString(), "ExecuteNonQuery");
return(-1);
}
finally
{
mCommand.Connection.Close();
ClearParameters();
}
}
/// <summary>
/// 执行查询返回填充的DataSet对象
/// </summary>
/// <param name="SQLText">SQl命令</param>
/// <param name="VisualTableName">虚拟表名</param>
/// <param name="StartIndex">制定返回多少行以后的数据</param>
/// <param name="Count">制定总共返回多少行</param>
/// <returns>返回按要求填充了的DataSet</returns>
public DataSet ExecuteDataSet(string SQLText, string VisualTableName, int StartIndex, int Count)
{
try
{
DataSet ds = new DataSet();
if (IsProcedure(SQLText))
{
mCommand.CommandType = CommandType.StoredProcedure;
}
else
{
mCommand.CommandType = CommandType.Text;
}
mCommand.CommandText = SQLText;
mCommand.CommandTimeout = 5;
//mCommand.Connection.Open();
mDataAdapter.Fill(ds, StartIndex, Count, VisualTableName);
//mCommand.Connection.Close();
//object state = mDataAdapter.InsertCommand.Connection.State;
return(ds);
}
catch (Exception e)
{
NDapperLog.write(e.ToString(), "ExecuteDataSet");
return(null);
}
finally
{
//mCommand.Connection.Close();
ClearParameters();
}
//if (mCommand.Connection.ConnectionString.ToLower().IndexOf("server=") > -1)
//{
// Connection = new SqlConnection(mCommand.Connection.ConnectionString);
// DataSet ds = new DataSet();
// try
// {
// Connection.Open();
// SqlDataAdapter command = new SqlDataAdapter(SQLText, Connection);
// command.Fill(ds, "ds");
// }
// catch
// {
// return null;
// }
// return ds;
//}
//else
//{
// DataSet ds = new DataSet();
// System.Data.OleDb.OleDbDataAdapter Adapter = new System.Data.OleDb.OleDbDataAdapter(SQLText, mCommand.Connection.ConnectionString);
// Adapter.Fill(ds, "w");
// return ds;
//}
}
/// <summary>
/// 执行insert命令返回新增的第一行第一列的值,即自增ID,其他返回影响的记录数
/// </summary>
/// <param name="SQLText">SQL语句</param>
/// <returns>新增的第一行第一列的值</returns>
public int ExecuteNonQuery(string SQLText)
{
try
{
if (IsProcedure(SQLText))
{
mCommand.CommandType = CommandType.StoredProcedure;
}
else
{
mCommand.CommandType = CommandType.Text;
}
mCommand.CommandTimeout = 5;
mCommand.Connection.Open();
if (SQLText.ToLower().IndexOf("update") > -1 && thisDBType == DBType.MySql)
{
SQLText = SQLText.Replace("\\", "\\\\");
}
if (SQLText.ToLower().IndexOf("insert") > -1)
{
if (thisDBType == DBType.SqlServer)
{
SQLText += " SELECT @@IDENTITY";
}
if (thisDBType == DBType.MySql)
{
SQLText = SQLText.Replace("\\", "\\\\"); SQLText += " ;SELECT LAST_INSERT_ID()";
}
mCommand.CommandText = SQLText;
object o = mCommand.ExecuteScalar();//第一行第一列的值如果是非int,ExecuteScalar会返回null
if (o == null)
{
return(0);
}
return(Convert.ToInt32(o));
}
else
{
mCommand.CommandText = SQLText;
return(mCommand.ExecuteNonQuery());
}
}
catch (Exception e)
{
NDapperLog.write(e.ToString(), "ExecuteNonQuery");
return(-1);
}
finally
{
mCommand.Connection.Close();
ClearParameters();
}
}
/// <summary>
/// 使用哈希表插入一条记录
/// </summary>
/// <param name="TableName"></param>
/// <param name="Cols"></param>
/// <param name="errTip"></param>
/// <returns></returns>
public int ExecuteNonQuery(String TableName, Hashtable Cols, out string errTip)
{
errTip = null;
int Count = 0;
if (Cols.Count <= 0)
{
return(Count);
}
String Fields = " (";
String Values = " Values(";
foreach (DictionaryEntry item in Cols)
{
if (Count != 0)
{
Fields += ",";
Values += ",";
}
Fields += item.Key.ToString();
if (item.Value.GetType().ToString() == "System.String" || item.Value.GetType().ToString() == "System.DateTime")
{
Values += "'" + item.Value.ToString().Replace("'", "''") + "'";
}
else
{
Values += item.Value.ToString().Replace("'", "''");
}
Count++;
}
Fields += ")";
Values += ")";
String SqlString = "Insert into " + TableName + " " + Fields + Values;
try
{
return(ExecuteNonQuery(SqlString, out errTip));
}
catch (Exception e)
{
errTip = e.Message;
NDapperLog.write(e.ToString(), "ExecuteNonQueryHashtable");
return(-1);
}
}
/// <summary>
/// 判断数据库连接状态
/// </summary>
/// <returns></returns>
public ConnectionState State()
{
try
{
conn.Open();
return(conn.State);
}
catch (Exception ex)
{
NDapperLog.write(ex.ToString(), "ConnectionState");
}
finally
{
conn.Close();
}
return(conn.State);
}
public int ExecuteNonQuery(DbCommand commend)
{
try
{
commend.Connection.Open();
return(commend.ExecuteNonQuery());
}
catch (Exception e)
{
NDapperLog.write(e.ToString(), "ExecuteNonQuery");
return(-1);
}
finally
{
mCommand.Connection.Close();
ClearParameters();
}
}
/// <summary>
/// 查询
/// </summary>
/// <typeparam name="T">返回类型</typeparam>
/// <param name="sql">sql语句</param>
/// <param name="dbConnKey">数据库连接</param>
/// <param name="param">sql查询参数</param>
/// <param name="commandTimeout">超时时间</param>
/// <param name="commandType">命令类型</param>
/// <returns></returns>
public T QueryFirst <T>(string sql, object param = null, bool buffered = true, int?commandTimeout = default(int?), CommandType?commandType = default(CommandType?))
{
if (DbTransaction == null)
{
try
{
return(conn.QueryFirstOrDefault <T>(sql, param, null, commandTimeout, commandType));
}
catch (Exception ex)
{
NDapperLog.write(ex.ToString(), "QueryFirst");
return(default(T));
}
}
else
{
return(DbTransaction.Connection.QueryFirstOrDefault <T>(sql, param, DbTransaction, commandTimeout, commandType));
}
}
/// <summary>
/// 执行增删改sql
/// </summary>
/// <param name="sql">sql</param>
/// <param name="dbkey">数据库连接</param>
/// <param name="param">sql查询参数</param>
/// <param name="commandTimeout">超时时间</param>
/// <param name="commandType">命令类型</param>
/// <returns></returns>
public int Execute(string sql, object param = null, int?commandTimeout = default(int?), CommandType?commandType = default(CommandType?))
{
if (DbTransaction == null)
{
try
{
return(conn.Execute(sql, param, null, commandTimeout, commandType));
}
catch (Exception ex)
{
NDapperLog.write(ex.ToString(), "ExecuteSql");
return(-1);
}
}
else
{
return(DbTransaction.Connection.Execute(sql, param, DbTransaction));
}
}
/// <summary>
/// 查询(异步版本)
/// </summary>
/// <typeparam name="T">返回类型</typeparam>
/// <param name="sql">sql语句</param>
/// <param name="dbConnKey">数据库连接</param>
/// <param name="param">sql查询参数</param>
/// <param name="buffered">是否缓冲</param>
/// <param name="commandTimeout">超时时间</param>
/// <param name="commandType">命令类型</param>
/// <returns></returns>
public async Task <IEnumerable <T> > QueryAsync <T>(string sql, object param = null, bool buffered = true, int?commandTimeout = default(int?), CommandType?commandType = default(CommandType?))
{
if (DbTransaction == null)
{
try
{
return(await conn.QueryAsync <T>(sql, param, null, commandTimeout, commandType));
}
catch (Exception ex)
{
NDapperLog.write(ex.ToString(), "QueryAsync");
return(null);
}
}
else
{
return(await DbTransaction.Connection.QueryAsync <T>(sql, param, DbTransaction, commandTimeout, commandType));
}
}
/// <summary>
/// 返回DataView对象
/// </summary>
/// <param name="SQLText"></param>
/// <returns>返回DataView对象</returns>
public DataView ExecuteDataView(string SQLText)
{
try
{
DataSet ds = ExecuteDataSet(SQLText, "Table1", 0, 0);
if (ds != null)
{
return(ds.Tables[0].DefaultView);
}
else
{
return(null);
}
}
catch (Exception e)
{
NDapperLog.write(e.ToString(), "ExecuteDataView");
return(null);
}
finally
{
ClearParameters();
}
}
/// <summary>
/// 提交当前操作的结果
/// </summary>
public int Commit()
{
try
{
if (DbTransaction != null)
{
DbTransaction.Commit();
this.Close();
}
return(1);
}
catch (Exception ex)
{
NDapperLog.write(ex.ToString(), "Commit");
return(-1);
}
finally
{
if (DbTransaction == null)
{
this.Close();
}
}
}