数据库连接的多种方式(一)

public class Helper { private static string cons = "server=.;uid=sa;pwd=;database=pubs"; //查询 public static DataTable ExecuteDataQuery(string sql) { using (SqlConnection con = new SqlConnection(cons)) { con.Open(); DataTable tbl = new DataTable(); SqlDataAdapter adap = new SqlDataAdapter(sql, con); adap.Fill(tbl); return tbl; } } public static SqlDataReader ExecuteDataQuery(string sql) { SqlConnection con = new SqlConnection(cons); SqlCommand cmd = new SqlCommand(sql, con); con.Open(); return cmd.ExecuteReader(CommandBehavior.CloseConnection); } //增删改 public static void ExecuteNonQuery(string sql) { using (SqlConnection con = new SqlConnection(cons)) { con.Open(); SqlCommand cmd = new SqlCommand(sql, con); cmd.ExecuteNonQuery(); } } } ------------------------------------------------------------------------------------------------------------------------------ using System; using System.Collections; using System.Collections.Specialized; using System.Data; using System.Data.SqlClient; using System.Configuration; /// <summary> /// Copyright (C) 2004-2008 LiTianPing /// 数据访问基础类(基于SQLServer) /// 用户可以修改满足自己项目的需要。 /// </summary> public abstract class DbHelperSQL { protected static string connectionString = CFunc.ConnectionString; public static SqlConnection conn = null; public static void connColse() { if (conn.State == ConnectionState.Open) { conn.Close(); } } #region 执行简单SQL语句 /// <summary> /// 执行SQL语句，返回影响的记录数 /// </summary> /// <param name="SQLString">SQL语句</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString) { conn = new SqlConnection(connectionString); using (SqlCommand cmd = new SqlCommand(SQLString, conn)) { try { conn.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); } finally { cmd.Dispose(); conn.Close(); } } } /// <summary> /// 执行多条SQL语句，实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static void ExecuteSqlTran(ArrayList SQLStringList) { conn = new SqlConnection(connectionString); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; SqlTransaction tx = conn.BeginTransaction(); cmd.Transaction = tx; try { for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n].ToString(); if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch (System.Data.SqlClient.SqlException E) { tx.Rollback(); throw new Exception(E.Message); } finally { cmd.Dispose(); conn.Close(); } } /// <summary> /// 执行带一个存储过程参数的的SQL语句。 /// </summary> /// <param name="SQLString">SQL语句</param> /// <param name="content">参数内容,比如一个字段是格式复杂的文章，有特 殊符号，可以通过这个方式添加</param> /// <returns>影响的记录数</returns> public static int ExecuteSql(string SQLString, string content) { conn = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(SQLString, conn); System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@content", SqlDbType.NText); myParameter.Value = content; cmd.Parameters.Add(myParameter); try { conn.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); } finally { cmd.Dispose(); conn.Close(); } } /// <summary> /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例) /// </summary> /// <param name="strSQL">SQL语句</param> /// <param name="fs">图像字节,数据库的字段类型为image的情况</param> /// <returns>影响的记录数</returns> public static int ExecuteSqlInsertImg(string strSQL, byte[] fs) { conn = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(strSQL, conn); System.Data.SqlClient.SqlParameter myParameter = new System.Data.SqlClient.SqlParameter("@fs", SqlDbType.Image); myParameter.Value = fs; cmd.Parameters.Add(myParameter); try { conn.Open(); int rows = cmd.ExecuteNonQuery(); return rows; } catch (System.Data.SqlClient.SqlException E) { throw new Exception(E.Message); } finally { cmd.Dispose(); conn.Close(); } } /// <summary> /// 执行一条计算查询结果语句，返回查询结果（object）。 /// </summary> /// <param name="SQLString">计算查询结果语句</param> /// <returns>查询结果（object）</returns> public static object GetSingle(string SQLString) { conn = new SqlConnection(connectionString); using (SqlCommand cmd = new SqlCommand(SQLString, conn)) { try { conn.Open(); object obj = cmd.ExecuteScalar(); if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value))) { return null; } else { return obj; } } catch (System.Data.SqlClient.SqlException e) { conn.Close(); throw new Exception(e.Message); } finally { cmd.Dispose(); conn.Close(); } } } /// <summary> /// 执行查询语句，返回SqlDataReader /// </summary> /// <param name="strSQL">查询语句</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteReader(string strSQL) {//使用完后请关闭conn conn = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand(strSQL, conn); try { conn.Open(); SqlDataReader myReader = cmd.ExecuteReader(); return myReader; } catch (System.Data.SqlClient.SqlException e) { throw new Exception(e.Message); } }