2009-11-29 23:27:10 以前写了一个,但是由于硬盘挂了,所以又写了一遍,这次我没有生成dll文件,直接写的,现在备份一份 大家可以直接拷贝使用 using System; using System.Data; using System.Configuration; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; using System.Data.OleDb; /// <summary> ///datahelp 的摘要说明 /// </summary> namespace shijiu { /// <summary> /// datahelp 的摘要说明。 /// 用来数据库连接以及数据存储的方法 /// </summary> public enum dbType { /// <summary> /// SQL数据库 /// </summary> sql, /// <summary> /// access数据库 /// </summary> access } public class datahelp { // 连接数据源 private SqlConnection con = null; private OleDbConnection conn = null; //连接数据类型 dbType cType; public datahelp(dbType type)//选择数据库,以及相应数据库的连接字符串 { //web.cofig定义数据库的连接字符串 string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStr"].ConnectionString; this.cType = type; if (type == dbType.sql) //sqlsever 数据库 { con = new SqlConnection(conStr); } else { if (type == dbType.access) //access 数据库库 { conn = new OleDbConnection(conStr); } } } /// <summary> /// 根据SQL查询返回DataSet对象,如果没有查询到则返回NULL /// </summary> /// <param name="sql">查询语句</param> /// <returns>DataSet</returns> public DataSet returnDS(string sql) { DataSet ds = new DataSet(); try { if (cType == dbType.sql) { SqlCommand cmd = new SqlCommand(sql, con); cmd.CommandTimeout = 20;//允许由于网络拥塞或服务器负载过重产生的延迟而取消 Execute 方法调用。如果在 CommandTimeout 属性中设置的时间间隔内没有完成命令执行,将产生错误,然后 ADO 将取消该命令。如果将该属性设置为零,ADO 将无限期等待直到命令执行完毕。请确保正在写入代码的提供者和数据源支持 CommandTimeout 功能。 this.Open(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(ds, "tempTable"); } else if (cType == dbType.access) { OleDbCommand cmd = new OleDbCommand(sql, conn); cmd.CommandTimeout = 20; this.Open(); System.Data.OleDb.OleDbDataAdapter adapter = new OleDbDataAdapter(cmd); adapter.Fill(ds, "tempTable"); } } catch (Exception e) { throw (e); ds = null; } finally { this.Close(); } return ds; } // /// <summary> /// 对数据库的增,删,改的操作 /// </summary> /// <param name="sql">SQL语句</param> /// <returns>是否成功</returns> /// public bool OperateDB(string sql) { bool succeed = false; int cnt = 0; try { if (cType == dbType.sql) { SqlCommand cmd = new SqlCommand(sql, con); cmd.CommandTimeout = 20; this.Open(); cnt = cmd.ExecuteNonQuery();//ExecuteNonQuery对于 UPDATE、INSERT 和 DELETE 语句,返回值为该命令所影响的行数。对于所有其他类型的语句,返回值为 -1。如果发生回滚,返回值也为 -1 } else if (cType == dbType.access) { OleDbCommand cmd = new OleDbCommand(sql, conn); cmd.CommandTimeout = 20; this.Open(); cnt = cmd.ExecuteNonQuery(); } } catch (Exception e) { throw (e); } finally { if (cnt > 0) { succeed = true; } this.Close(); } return succeed; } /// <summary> /// 获得该SQL查询返回的第一行第一列的值,如果没有查询到则返回NULL /// </summary> /// <param name="sql">查询语句</param> /// <returns>返回的第一行第一列的值</returns> public string getfirstValue(string sql) { string str = null; try { if (cType == dbType.sql) { SqlCommand cmd = new SqlCommand(sql, con); this.Open(); str = cmd.ExecuteScalar().ToString(); } else if (cType == dbType.access) { OleDbCommand cmd = new OleDbCommand(sql, conn); this.Open(); str = cmd.ExecuteScalar().ToString(); } } catch (Exception e) { throw (e); } finally { this.Close(); } return str; } /// <summary> /// 获得该SQL查询返回DataTable,如果没有查询到则返回NULL /// </summary> /// <param name="sql">查询语句</param> /// <returns></returns> public DataTable getTable(string sql) { DataTable tb = null; DataSet ds = this.returnDS(sql); if (ds != null) { tb = ds.Tables["tempTable"]; } return tb; } /// <summary> /// 打开数据库连接. /// </summary> private void Open() { if (cType == dbType.sql) { if (con.State == System.Data.ConnectionState.Closed) { con.Open(); } else if (con.State == System.Data.ConnectionState.Broken) { con.Close(); con.Open(); } } else if (cType == dbType.access) { if (conn.State == System.Data.ConnectionState.Closed) { conn.Open(); } else if (conn.State == System.Data.ConnectionState.Broken) { conn.Close(); conn.Open(); } } } /// <summary> /// 关闭数据库连接 /// </summary> public void Close() { if (cType == dbType.sql) { if (con != null) { con.Close(); } } else if (cType == dbType.access) { if (conn != null) { conn.Close(); } } } /// <summary> /// 释放资源 /// </summary> public void Dispose() { if (cType == dbType.sql) { // 确认连接是否已经关闭 if (con != null) { con.Dispose(); con = null; } } else if (cType == dbType.access) { if (conn != null) { conn.Dispose(); conn = null; } } } } }