- using System;
- using System.Data;
- using System.Configuration;
- using System.web;
- using System.Web.Security;
- using System.Web.UI;
- using System.Web.UI.WebControls;
- using System.Web.UI.WebControls.WebParts;
- using System.Web.UI.htmlControls;
- using System.Data.SqlClient;
- using System.Collections;
- using System.Collections.Generic;
- /// <summary>
- /// Database 的摘要说明
- /// </summary>
- public class Database
- {
- protected SqlConnection Connection;
- protected String ConnectionString;
- List<SqlParameter> paraArray = new List<SqlParameter>();
- public Database()
- {
- //构造函数
- ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
- }
- ~Database()
- {
- //析构函数
- try
- {
- if (Connection != null)
- Connection.Close();
- }
- catch { }
- try
- {
- Dispose();
- }
- catch { }
- }
- public void OpenMasterStation()
- {
- ConnectionString = ConfigurationManager.ConnectionStrings["HNIUConnectionString"].ConnectionString;
- }
- protected void Open()
- {
- //
- //保护方法,打开数据库连接。
- if (Connection == null)
- {
- Connection = new SqlConnection(ConnectionString);
- }
- if (Connection.State.Equals(ConnectionState.Closed))
- {
- Connection.Open();
- }
- }
- public void Close()
- {
- //公有方法,关闭数据库连接。
- if (Connection != null)
- Connection.Close();
- }
- public void Dispose()
- {
- //公有方法,释放资源。
- // 确保连接被关闭
- if (Connection != null)
- {
- Connection.Dispose();
- Connection = null;
- }
- }
- //public void GetDataReader()
- //{
- //公有方法,返回一个DataRead
- //Open();
- //SqlCommand cmd = new SqlCommand(SqlString, Connection);
- //OleDbDataReader dr = cmd.ExecuteReader();
- //return dr;
- //}
- public DataSet GetDataSet(String SqlString)
- {
- //公有方法,获取数据,返回一个DataSet。
- Open();
- SqlDataAdapter adapter = new SqlDataAdapter(SqlString, Connection);
- DataSet dataset = new DataSet();
- adapter.Fill(dataset);
- Close();
- return dataset;
- }
- public int ExecuteSQL(String SqlString)
- {
- // 公有方法,执行Sql语句。
- // <param name="SqlString">Sql语句</param>
- // <returns>对Update、Insert、Delete为影响到的行数,其他情况为-1</returns>
- int count = -1;
- Open();
- try
- {
- SqlCommand cmd = new SqlCommand(SqlString, Connection);
- count = cmd.ExecuteNonQuery();
- }
- catch
- {
- count = -1;
- }
- finally
- {
- Close();
- }
- return count;
- }
- public int ExecuteSQL(String SqlProcName, SqlParameter[] SqlParaArray)
- {
- // 公有方法,执行Sql语句。
- // <param name="SqlProcName">存储过程名称</param>
- // <param name="SqlParaArray">参数数组</param>
- // <returns>对Update、Insert、Delete为影响到的行数,其他情况为-1</returns>
- int count = -1;
- Open();
- try
- {
- SqlCommand cmd = new SqlCommand();
- cmd.CommandText = SqlProcName;
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Connection = Connection;
- cmd.Parameters.AddRange(SqlParaArray);
- count = cmd.ExecuteNonQuery();
- }
- catch
- {
- count = -1;
- }
- finally
- {
- Close();
- }
- return count;
- }
- public void AddSqlParaArray(string name,object value)
- {
- paraArray.Add(new SqlParameter(name,value));
- }
- public object ExecuteScalar(string SqlProcName)
- {
- // 公有方法,执行Sql语句。
- // <param name="SqlProcName">存储过程名称</param>
- // <returns>返回单个值,如果没有查询结果返回为NULL</returns>
- Open();
- object result = null;
- try
- {
- SqlCommand cmd = new SqlCommand();
- cmd.CommandText = SqlProcName;
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Connection = Connection;
- cmd.Parameters.AddRange(paraArray.ToArray());
- result = cmd.ExecuteScalar();
- }
- catch(Exception ee)
- {
- throw new Exception(ee.ToString());
- }
- finally
- {
- Close();
- //if (result == null) MsgInfo.showMessage("DataAccess", 5);
- }
- return result;
- }
- public DataRow GetDataRow(string SqlProcName)
- {
- Open();
- DataRow dr = null;
- DataSet ds = new DataSet();
- try
- {
- SqlCommand cmd = new SqlCommand();
- cmd.CommandText = SqlProcName;
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Connection = Connection;
- cmd.Parameters.AddRange(paraArray.ToArray());
- SqlDataAdapter sda = new SqlDataAdapter(cmd);
- sda.Fill(ds);
- if (ds.Tables[0].Rows.Count > 0) dr = ds.Tables[0].Rows[0];
- }
- catch { }
- Close();
- return dr;
- }
- public DataRow GetDataRow(String SqlProcName, SqlParameter[] SqlParaArray)
- {
- //公有方法,获取数据,返回一个DataRow。
- DataSet dataset = GetDataSet(SqlProcName,SqlParaArray);
- dataset.CaseSensitive = false;
- if (dataset.Tables[0].Rows.Count > 0)
- {
- return dataset.Tables[0].Rows[0];
- }
- else
- {
- return null;
- }
- }
- public DataSet GetDataSet(String SqlProcName, SqlParameter[] SqlParaArray)
- {
- //公有方法,获取数据,返回一个DataSet。
- Open();
- SqlDataAdapter adapter = new SqlDataAdapter();
- SqlCommand cmd = new SqlCommand();
- cmd.CommandText = SqlProcName;
- cmd.Connection = Connection;
- cmd.Parameters.AddRange(SqlParaArray);
- cmd.CommandType = CommandType.StoredProcedure;
- adapter.SelectCommand = cmd;
- DataSet dataset = new DataSet();
- adapter.Fill(dataset);
- Close();
- return dataset;
- }
- public int ExecuteNonQuery(String SqlProcName)
- {
- // 公有方法,执行Sql语句。
- // <param name="SqlProcName">存储过程名称</param>
- // <returns>对Update、Insert、Delete为影响到的行数,其他情况为-1</returns>
- int count = -1;
- Open();
- try
- {
- SqlCommand cmd = new SqlCommand();
- cmd.CommandText = SqlProcName;
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.Connection = Connection;
- cmd.Parameters.AddRange(paraArray.ToArray());
- count = cmd.ExecuteNonQuery();
- }
- catch (Exception ee)
- {
- //MsgInfo.pushMsg(ee.ToString());
- throw new Exception(ee.ToString());
- }
- finally
- {
- Close();
- }
- return count;
- }
- public DataTable GetDataTable(String SqlProcName)
- {
- //公有方法,获取数据,返回一个DataTable。
- DataTable dt = null;
- try
- {
- Open();
- SqlDataAdapter adapter = new SqlDataAdapter();
- SqlCommand cmd = new SqlCommand();
- cmd.CommandText = SqlProcName;
- cmd.Connection = Connection;
- cmd.Parameters.AddRange(paraArray.ToArray());
- cmd.CommandType = CommandType.StoredProcedure;
- adapter.SelectCommand = cmd;
- dt = new DataTable();
- adapter.Fill(dt);
- }
- catch(Exception ee)
- {
- throw new Exception(ee.ToString());
- }
- finally
- {
- Close();
- }
- return dt;
- }
- }
- //该片段来自于http://www.codesnippet.cn/detail/0408201513309.html
来源: http://www.codesnippet.cn/detail/0408201513309.html