SQL操作类代码,CSharp代码库,德仔网

代码语言
代码分类
【CSharp】 SQL操作类
作者:yyj / 发布于2012/10/8/ 528
<div>?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;</div>
<div>/// <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 { }
 }
<div> public void OpenMasterStation()
 {
 ConnectionString = ConfigurationManager.ConnectionStrings["HNIUConnectionString"].ConnectionString;
 }
<div> protected void Open()
 {
 //
 //保护方法，打开数据库连接。
 if (Connection == null)
 {
 Connection = new SqlConnection(ConnectionString);
 }
 if (Connection.State.Equals(ConnectionState.Closed))
 {
 Connection.Open();
 }
 }
<div>
 public void Close()
 {
 //公有方法，关闭数据库连接。
 if (Connection != null)
 Connection.Close();
 }
<div> public void Dispose()
 {
 //公有方法，释放资源。
 // 确保连接被关闭
 if (Connection != null)
 {
 Connection.Dispose();
 Connection = null;
 }
 }
<div> //public void GetDataReader()
 //{ 
 //公有方法，返回一个DataRead
 //Open();
 //SqlCommand cmd = new SqlCommand(SqlString, Connection);
 //OleDbDataReader dr = cmd.ExecuteReader();
 //return dr;
 //}
</div>
<div> public DataSet GetDataSet(String SqlString)
 {
 //公有方法，获取数据，返回一个DataSet。
 Open();
 SqlDataAdapter adapter = new SqlDataAdapter(SqlString, Connection);
 DataSet dataset = new DataSet();
 adapter.Fill(dataset);
 Close();
 return dataset;
 }
<div> public int ExecuteSQL(String SqlString)
 {
 // 公有方法，执行Sql语句。
 // <param name="SqlString">Sql语句</param>
 // <returns>对Update、Insert、Delete为影响到的行数，其他情况为-1</returns></div>
<div> 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></div>
<div> 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)); 
 }
<div> public object ExecuteScalar(string SqlProcName)
 {
 // 公有方法，执行Sql语句。
 // <param name="SqlProcName">存储过程名称</param>
 // <returns>返回单个值，如果没有查询结果返回为NULL</returns></div>
<div> 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;
 }
<div> 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;
 }
<div> 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;
 }
 }
<div> 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;
 }
<div> public int ExecuteNonQuery(String SqlProcName)
 {
 // 公有方法，执行Sql语句。
 // <param name="SqlProcName">存储过程名称</param>
 // <returns>对Update、Insert、Delete为影响到的行数，其他情况为-1</returns></div>
<div> 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;
 }
<div> 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;
 }
}
</div>
试试其它关键字
　SQL操作类　
同语言下
可能有用的
yyj贡献的其它代码(1)
.SQL操作类