/// <summary>
/// Prepare a command for execution
/// </summary>
/// <param name="cmd">SqlCommand object</param>
/// <param name="conn">SqlConnection object</param>
/// <param name="trans">SqlTransaction object</param>
/// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
/// <param name="cmdText">Command text, e.g. Select * from Products</param>
/// <param name="cmdParms">SqlParameters to use in the command</param>
private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType, string cmdText, List <DbParameter> cmdParms)
{
CommonVar.WriteLog(cmdText);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
cmd.Connection = conn;
cmd.CommandText = cmdText;
if (trans != null)
{
cmd.Transaction = trans as SqlTransaction;
}
cmd.CommandType = cmdType;
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
{
if (parm.Value == null || ((parm.DbType == DbType.DateTime || parm.DbType == DbType.Date) && (parm.Value.ToString().Contains("0001"))))
{
parm.Value = DBNull.Value;
}
cmd.Parameters.Add(parm);
}
}
}
/// <summary>
/// 查询返回数据集
/// </summary>
public DataTable Query(CommandType cmdType, string cmdText, List <DbParameter> commandParameters)
{
CommonVar.WriteLog(cmdText);
SqlCommand cmd = new SqlCommand();
DataSet ds = new DataSet();
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(ds);
}
return(ds.Tables[0]);
}
public DataTable Query(CommandType cmdType, string cmdText, List <DbParameter> commandParameters)
{
if (ConnectionString == null || ConnectionString.Length == 0)
{
throw new ArgumentNullException("connectionString");
}
if (cmdText == null || cmdText.Length == 0)
{
throw new ArgumentNullException("commandText");
}
CommonVar.WriteLog(cmdText);
SQLiteCommand cmd = new SQLiteCommand();
DataSet ds = new DataSet();
using (SQLiteConnection connection = new SQLiteConnection(ConnectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
SQLiteDataAdapter adapter = new SQLiteDataAdapter(cmd);
adapter.Fill(ds);
}
return(ds.Tables[0]);
}
