以下代码以操作ORACLE数据库为例
1.返回DataSet数据，若查询返回行数为零，返回null
1 /// <summary>
2 /// 执行查询语句，返回DataSet
3 /// </summary>
4 /// <param name="SQLString">查询语句</param>
5 /// <returns>DataSet</returns>
6 public static DataSet Query(string SQLString, params OracleParameter[] cmdParms)
7 {
8 using (OracleConnection connection = new OracleConnection(connectionString))
9 {
10 OracleCommand cmd = new OracleCommand();
11 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
12 using (OracleDataAdapter da = new OracleDataAdapter(cmd))
13 {
14 DataSet ds = new DataSet();
15 try
16 {
17 da.Fill(ds, "ds");
18 cmd.Parameters.Clear();
19 }
20 catch (System.Data.OracleClient.OracleException ex)
21 {
22 throw new Exception(ex.Message);
23 }
24 //没有数据返回，返回null
25 if (ds.Tables[0].Rows.Count <= 0)
26 {
27 return null;
28 }
29 return ds;
30 }
31 }
32 }
2.返回DataTable数据，若查询返回行数为零，返回null
1 /// <summary>
2 /// 执行查询语句，返回DataTable
3 /// </summary>
4 /// <param name="SQLString">查询语句</param>
5 /// <returns>DataTable</returns>
6 public static DataTable Query(string SQLString, params OracleParameter[] cmdParms)
7 {
8 using (OracleConnection connection = new OracleConnection(connectionString))
9 {
10 OracleCommand cmd = new OracleCommand();
11 PrepareCommand(cmd, connection, null, SQLString, cmdParms);
12 using (OracleDataAdapter da = new OracleDataAdapter(cmd))
13 {
14 DataTable dt = new DataTable();
15 try
16 {
17 da.Fill(dt);
18 cmd.Parameters.Clear();
19 }
20 catch (System.Data.OracleClient.OracleException ex)
21 {
22 throw new Exception(ex.Message);
23 }
24 //没有数据返回，返回null
25 if (dt.Rows.Count <= 0)
26 {
27 return null;
28 }
29 return dt;
30 }
31 }
32 }
PrepareCommand的实现(1和2中均有用到)
1 /// <summary>
2 /// 打开数据库连接，传递参数等前置处理
3 /// </summary>
4 /// <param name="cmd">OracleCommand</param>
5 /// <param name="conn">数据库连接</param>
6 /// <param name="trans">数据库事务</param>
7 /// <param name="cmdText">SQL语句</param>
8 /// <param name="cmdParms">传递的参数</param>
9 private static void PrepareCommand(OracleCommand cmd, OracleConnection conn,
10 OracleTransaction trans, string cmdText, OracleParameter[] cmdParms)
11 {
12 if (conn.State != ConnectionState.Open)
13 conn.Open();
14 cmd.Connection = conn;
15 cmd.CommandText = cmdText;
16 if (trans != null)
17 cmd.Transaction = trans;
18 cmd.CommandType = CommandType.Text;
19 if (cmdParms != null)
20 {
21 foreach (OracleParameter parm in cmdParms)
22 {
23 if (parm.Value == null)
24 {
25 parm.Value = DBNull.Value;
26 }
27 cmd.Parameters.Add(parm);
28 }
29 }
30 }
应用场景
1.防止多次判断如：
if (dt == null || dt.Rows.Count <= 0) { return; }
只需写作
if (dt == null) { return; }
2.防止引用为null的数据引发异常：
this.dataGridView1.DataSource = dt;
//若dt返回数据行数为零,以下赋值会产生异常
this.dataGridView1.Rows[0].Selected = false;