用于MySQL的SqlHelper
1 /// <summary> 2 3 /// Title ：MySqlHelper 4 /// Author ：WinterT 5 /// Date ：2015-1-8 08:12:54 6 /// Description： 7 /// ExecuteNonQuery 8 /// ExecuteScalar 9 /// ExecuteReader 10 /// ExecuteTable 11 /// </summary> 12 public static class MySqlHelper 13 { 14 /// <summary> 15 /// 返回配置文件中指定的連接 16 /// </summary> 17 /// <returns>配置文件中指定的連接</returns> 18 PRivate static MySqlConnection GetConnection() 19 { 20 string connString = ConfigurationManager.ConnectionStrings[1].ConnectionString; 21 return new MySqlConnection(connString); 22 } 23 #region ExecuteNonQuery 24 /// <summary> 25 /// 執行sql語句 26 /// </summary> 27 /// <param name="sql">sql語句</param> 28 /// <returns>受影響行數</returns> 29 public static int ExecuteNonQuery(string sql) 30 { 31 using (MySqlConnection conn = GetConnection()) 32 { 33 using (MySqlCommand cmd = conn.CreateCommand()) 34 { 35 cmd.CommandText = sql; 36 return cmd.ExecuteNonQuery(); 37 } 38 } 39 } 40 /// <summary> 41 /// 根據給定連接，執行帶參數的SQL語句 42 /// </summary> 43 /// <param name="conn">連接、使用前確保連接以打開。</param> 44 /// <param name="sql">帶參數的sql語句</param> 45 /// <param name="paras">參數</param> 46 /// <returns>受影響行數</returns> 47 public static int ExecuteNonQuery 48 (MySqlConnection conn, string sql, params MySqlParameter[] paras) 49 { 50 using (MySqlCommand cmd = conn.CreateCommand()) 51 { 52 cmd.CommandText = sql; 53 cmd.Parameters.AddRange(paras); 54 return cmd.ExecuteNonQuery(); 55 } 56 } 57 /// <summary> 58 /// 執行帶參數的SQL語句 59 /// </summary> 60 /// <param name="sql">帶參數的sql語句</param> 61 /// <param name="paras">參數</param> 62 /// <returns>受影響行數</returns> 63 public static int ExecuteNonQuery 64 (string sql, params MySqlParameter[] paras) 65 { 66 using (MySqlConnection conn = GetConnection()) 67 { 68 return ExecuteNonQuery(conn, sql, paras); 69 } 70 } 71 #endregion 72 #region ExecuteScalar 73 /// <summary> 74 /// 執行sql語句，返回第一行第一列 75 /// </summary> 76 /// <param name="sql">sql語句</param> 77 /// <returns>第一行第一列</returns> 78 public static Object ExecuteScalar(string sql) 79 { 80 using (MySqlConnection conn = GetConnection()) 81 { 82 using (MySqlCommand cmd = conn.CreateCommand()) 83 { 84 cmd.CommandText = sql; 85 return cmd.ExecuteScalar(); 86 } 87 } 88 } 89 /// <summary> 90 /// 根據Connection對象，執行帶參數的sql語句，返回第一行第一列 91 /// </summary> 92 /// <param name="conn">連接</param> 93 /// <param name="sql">sql語句</param> 94 /// <param name="paras">參數</param> 95 /// <returns>返回第一行第一列</returns> 96 public static object ExecuteScalar 97 (MySqlConnection conn, string sql, MySqlParameter[] paras) 98 { 99 using (MySqlCommand cmd = conn.CreateCommand())100 {101 cmd.CommandText = sql;102 cmd.Parameters.AddRange(paras);103 return cmd.ExecuteScalar();104 }105 }106 /// <summary>107 /// 執行帶參數的sql語句，返回第一行第一列108 /// </summary>109 /// <param name="sql">sql語句</param>110 /// <param name="paras">參數</param>111 /// <returns>返回第一行第一列</returns>112 public static object ExecuteScalar113 (string sql, MySqlParameter[] paras)114 {115 using (MySqlConnection conn = GetConnection())116 {117 return ExecuteScalar(conn, sql, paras);118 }119 }120 #endregion121 #region ExecuteReader122 /// <summary>123 /// 執行sql語句，返回一個MySqlDataReader124 /// </summary>125 /// <param name="sql">sql語句</param>126 /// <returns>一個MySqlDataReader對象</returns>127 public static MySqlDataReader ExecuteReader(string sql)128 {129 MySqlConnection conn = GetConnection();130 using (MySqlCommand cmd = conn.CreateCommand())131 {132 cmd.CommandText = sql;133 conn.Open();134 return cmd.ExecuteReader(CommandBehavior.CloseConnection);135 }136 }137 /// <summary>138 /// 根據指定的連接，執行帶參數的sql語句，返回一個Reader對象139 /// </summary>140 /// <param name="conn">連接</param>141 /// <param name="sql">sql語句</param>142 /// <param name="paras">參數</param>143 /// <returns>一個MySqlDataReader對象</returns>144 public static MySqlDataReader ExecuteReader145 (MySqlConnection conn, string sql, params MySqlParameter[] paras)146 {147 using (MySqlCommand cmd = conn.CreateCommand())148 {149 cmd.CommandText = sql;150 cmd.Parameters.AddRange(paras);151 conn.Open();152 return cmd.ExecuteReader(CommandBehavior.CloseConnection);153 }154 }155 /// <summary>156 /// 執行帶參數的sql語句，返回一個Reader對象157 /// </summary>158 /// <param name="sql">sql語句</param>159 /// <param name="paras">參數</param>160 /// <returns>一個MySqlDataReader對象</returns>161 public static MySqlDataReader ExecuteReader162 (string sql, params MySqlParameter[] paras)163 {164 MySqlConnection conn = GetConnection();165 using (MySqlCommand cmd = conn.CreateCommand())166 {167 return ExecuteReader(conn, sql, paras);168 }169 }170 #endregion171 #region ExecuteTable172 /// <summary>173 /// 執行sql語句，返回一個DataTable174 /// </summary>175 /// <param name="sql">sql語句</param>176 /// <returns>DataTable</returns>177 public static DataTable ExecuteTable(string sql)178 {179 using (MySqlConnection conn = GetConnection())180 {181 using (MySqlCommand cmd = conn.CreateCommand())182 {183 cmd.CommandText = sql;184 using (MySqlDataReader reader = cmd.ExecuteReader())185 {186 DataTable table = new DataTable();187 table.Load(reader);188 return table;189 }190 }191 }192 }193 /// <summary>194 /// 根據連接，執行帶參數的sql語句，返回一個DataTable195 /// </summary>196 /// <param name="conn">連接，切記連接已打開</param>197 /// <param name="sql">sql語句</param>198 /// <param name="paras">參數</param>199 /// <returns>DataTable</returns>200 public static DataTable ExecuteTable201 (MySqlConnection conn, string sql, params MySqlParameter[] paras)202 {203 using (MySqlCommand cmd = conn.CreateCommand())204 {205 cmd.CommandText = sql;206 cmd.Parameters.AddRange(paras);207 using (MySqlDataReader reader = cmd.ExecuteReader())208 {209 DataTable table = new DataTable();210 table.Load(reader);211 return table;212 }213 }214 }215 /// <summary>216 /// 執行帶參數的sql語句217 /// </summary>218 /// <param name="sql">sql語句</param>219 /// <param name="paras">參數</param>220 /// <returns>DataTable</returns>221 public static DataTable ExecuteTable222 (string sql, params MySqlParameter[] paras)223 {224 using (MySqlConnection conn = GetConnection())225 {226 return ExecuteTable(conn, sql, paras);227 }228 }229 230 #endregion231 }
新聞熱點
疑難解答
