防止查询数据返回数据行为零引起的逻辑判断重复或抛出异常

以下代码以操作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                     try16                     {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                     //没有数据返回，返回null25                     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                     try16                     {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                     //没有数据返回，返回null25                     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;