/// <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]); }