防止查詢數據返回數據行為零引起的邏輯判斷重復或拋出異常

以下代碼以操作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;