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

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

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