刚学习C#时自己写的数据库连接类
using System; using System.Collections.Generic; using System.Text; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace DBUtility { /// <summary> /// SQLServer的数据库连接类 /// </summary> public abstract class SQLDBHelper { private static SqlConnection conn = new SqlConnection( ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); private SQLDBHelper() { } ~SQLDBHelper() { Dispose(); } /// <summary> /// 释放资源 /// </summary> public static void Dispose() { // 确认连接是否已经关闭 if (conn != null) { conn.Dispose(); conn = null; } } /// <summary> /// 打开连接 /// </summary> private static void Open() { if (conn.State == ConnectionState.Closed) { try { conn.Open(); } catch (Exception ex) { throw ex; } } } /// <summary> /// 关闭连接 /// </summary> private static void Close() { while (conn.State == ConnectionState.Open) { conn.Close(); } } /// <summary> /// 执行查询语句或存储过程并返回受影响的行数 /// </summary> /// <param name="sql">查询语句或存储过程</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(string sql) { return ExecuteNonQuery(sql, null); } /// <summary> /// 执行查询语句或存储过程并返回受影响的行数 /// </summary> /// <param name="sql">查询语句或存储过程</param> /// <param name="parameters">参数列表</param> /// <returns>受影响的行数</returns> public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters) { int returnValue; SqlCommand cmd = new SqlCommand(sql, conn); if (sql.ToLower().StartsWith("insert ") || sql.ToLower().StartsWith("update ") || sql.ToLower().StartsWith("delete ")) { cmd.CommandType = CommandType.Text; } else { cmd.CommandType = CommandType.StoredProcedure; } if (parameters != null) { for (int i = 0; i < parameters.Length; i++) { cmd.Parameters.Add(parameters[i]); } } try { Open(); returnValue = cmd.ExecuteNonQuery(); } catch (Exception ex) { throw ex; } finally { Close(); } return returnValue; } /// <summary> /// 执行查询或存储过程并返回是否有查询结果 /// </summary> /// <param name="sql">查询语句或存储过程</param> /// <returns>是否有查询结果</returns> public static bool ExecuteReader(string sql) { return ExecuteReader(sql, null); } /// <summary> /// 执行查询或存储过程并返回是否有查询结果 /// </summary> /// <param name="sql">查询语句或存储过程</param> /// <param name="parameters">参数列表</param> /// <returns>是否有查询结果</returns> public static bool ExecuteReader(string sql, params SqlParameter[] parameters) { bool flag = false; SqlCommand cmd = new SqlCommand(sql, conn); if (sql.ToLower().StartsWith("select ")) { cmd.CommandType = CommandType.Text; } else { cmd.CommandType = CommandType.StoredProcedure; } if (parameters != null) { for (int i = 0; i < parameters.Length; i++) { cmd.Parameters.Add(parameters[i]); } } try { Open(); SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read()) { flag = true; } } catch (Exception ex) { throw ex; } finally { Close(); } return flag; } /// <summary> /// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。 /// </summary> /// <param name="sql">查询语句或存储过程</param> /// <returns>结果集中第一行的第一列</returns> public static object ExecuteScalar(string sql) { return ExecuteScalar(sql, null); } /// <summary> /// 执行查询,并返回查询所返回的结果集中第一行的第一列。忽略其他列或行。 /// </summary> /// <param name="sql">查询语句或存储过程</param> /// <param name="parameters">参数列表</param> /// <returns>结果集中第一行的第一列</returns> public static object ExecuteScalar(string sql, params SqlParameter[] parameters) { object returnValue; SqlCommand cmd = new SqlCommand(sql, conn); if (sql.ToLower().StartsWith("select ")) { cmd.CommandType = CommandType.Text; } else { cmd.CommandType = CommandType.StoredProcedure; } if (parameters != null) { for (int i = 0; i < parameters.Length; i++) { cmd.Parameters.Add(parameters[i]); } } try { Open(); returnValue = cmd.ExecuteScalar(); } catch (Exception ex) { throw ex; } finally { Close(); } return returnValue; } /// <summary> /// 获得数据集 /// </summary> /// <param name="sql">查询语句或存储过程</param> /// <param name="tableName">要查询的表名</param> /// <returns>数据集</returns> public static DataSet GetDataSet(string sql, string tableName) { return GetDataSet(sql, tableName, null); } /// <summary> /// 获得数据集 /// </summary> /// <param name="sql">查询语句或存储过程</param> /// <param name="tableName">要查询的表名</param> /// <param name="parameters">参数</param> /// <returns>数据集</returns> public static DataSet GetDataSet(string sql, string tableName, params SqlParameter[] parameters) { SqlCommand cmd = new SqlCommand(sql, conn); if (sql.ToLower().StartsWith("select ")) { cmd.CommandType = CommandType.Text; } else { cmd.CommandType = CommandType.StoredProcedure; } if (parameters != null) { for (int i = 0; i < parameters.Length; i++) { cmd.Parameters.Add(parameters[i]); } } SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); da.Fill(ds, tableName); return ds; } /// <summary> /// 根据数据集修改数据库 /// </summary> /// <param name="ds">数据集</param> /// <param name="sql">获得次数据集的查询语句或存储过程</param> /// <returns>受影响的行数</returns> public static int AcceptChanges(DataSet ds, string sql) { return AcceptChanges(ds, sql); } /// <summary> /// 根据数据集修改数据库 /// </summary> /// <param name="ds">数据集</param> /// <param name="sql">获得次数据集的查询语句或存储过程</param> /// <param name="parameters">参数列表</param> /// <returns>受影响的行数</returns> public static int AcceptChanges(DataSet ds, string sql, params SqlParameter[] parameters) { int rows = 0; //取出表结构 SqlCommand cmd = new SqlCommand(sql, conn); if (sql.ToLower().StartsWith("select ")) { cmd.CommandType = CommandType.Text; } else { cmd.CommandType = CommandType.StoredProcedure; } if (parameters != null) { for (int i = 0; i < parameters.Length; i++) { cmd.Parameters.Add(parameters[i]); } } SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; //生成实现 DataSet 的更改与关联的 SQL Server 实例之间的协调所需的 Transact-SQL 语句。 SqlCommandBuilder cb = new SqlCommandBuilder(da); try { rows = da.Update(ds, sql); ds.AcceptChanges(); } catch { } return rows; } } }
posted on 2009-05-29 01:42 Lionheart Zhang 阅读(566) 评论(0) 编辑 收藏 举报