防止查询数据返回数据行为零引起的逻辑判断重复或抛出异常
以下代码以操作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;
