操作数据库的类(C#)

using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Win32;
using System.Reflection;
using System.IO;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Runtime.InteropServices ;
using System.Text;
namespace IMS.Class
{
///
/// LinkDatabase 的摘要说明。
///
public class LinkDatabase
{
private string strSQL="";
//private string connectionString = “Data Source=Persist Security Info=False;Initial Catalog=;Integrated Security=SSPI”;
private SqlConnection myConnection;

private SqlCommandBuilder sqlCmdBld;
private DataSet ds = new DataSet();
private SqlDataAdapter da;

public string DB_Conn="";
public LinkDatabase()
{
// TODO: 在此处添加构造函数逻辑
DB_Conn=“Persist Security Info=False;Data Source=;Initial Catalog=;User ID=sa;Password=sa”;//ConfigurationSettings.AppSettings[“ConnStr”];
}
public LinkDatabase(string Str)
{
try
{
this.DB_Conn = Str;
}
catch(Exception ex)
{
throw ex;
}
}
public bool JudgeServer()
{
this.open();
if(this.myConnection.StateConnectionState.Open)
{
this.myConnection.Close();
return true;
}
else
return false;
}
public void open()
{
//调试时若无此句,将使得运行时提示:未将对象引用到实例
this.myConnection=new SqlConnection(this.DB_Conn);
if(this.myConnection.StateConnectionState.Open)
{

return ;

}
else
try
{
this.myConnection.Open();
}
catch(System.Data.SqlClient.SqlException ex)
{
throw new Exception(""+ex.Message +"");
}

}

///
/// 根据输入的SQL语句检索数据库数据
///
/// 检索的sql语句
/// 映射的表名
///
public DataSet SelectDataBase(string tempStrSQL,string tempTableName)
{
this.strSQL = tempStrSQL;
this.myConnection = new SqlConnection(DB_Conn);
this.da = new SqlDataAdapter(this.strSQL,this.myConnection);
this.ds.Clear();
try
{
this.da.Fill(ds,tempTableName);
}
catch(Exception e)
{
throw new Exception(""+e.Message +"");
}
return ds;//返回填充了数据的DataSet，其中数据表以tempTableName给出的字符串命名
}

///
/// 数据库数据更新(传DataSet和DataTable的对象)
///
/// 改变了的dataset
/// 映射源表的表名
/// 返回更新了的数据库表
public DataSet UpdateDataBase(DataSet changedDataSet,string tableName,string str)
{
try
{
this.myConnection = new SqlConnection(DB_Conn);
this.da = new SqlDataAdapter(str,this.myConnection);
this.da.SelectCommand=new SqlCommand(str,this.myConnection);
this.sqlCmdBld = new SqlCommandBuilder(da);
this.da.Update(changedDataSet,tableName);
return changedDataSet;//返回更新了的数据库表
}
catch(Exception ex)
{
throw new Exception(""+ex.Message +"");
}
}
/ 直接操作数据库(未创建该类的实例时直接用) /

///
/// 检索数据库数据(传字符串,直接操作数据库)
///
/// 检索的sql语句
/// 查询的结果，存在于一个datatable中
public DataTable SelectDataBase(string tempStrSQL)
{
this.myConnection = new SqlConnection(DB_Conn);
DataSet tempDataSet = new DataSet();
this.da = new SqlDataAdapter(tempStrSQL,this.myConnection);
this.da.Fill(tempDataSet);
return tempDataSet.Tables[0];
}

///
/// 数据库数据更新(传字符串，直接操作数据库)
///
/// 检索的sql语句
/// 返回数据库中影响的行数
public int UpdateDataBase(string tempStrSQL)
{
this.myConnection = new SqlConnection(DB_Conn);
//使用Command之前一定要先打开连接,后关闭连接,而DataAdapter则会自动打开关闭连接
myConnection.Open();
SqlCommand tempSqlCommand = new SqlCommand(tempStrSQL,this.myConnection);
int intNumber = tempSqlCommand.ExecuteNonQuery();//返回数据库中影响的行数
return intNumber;
}

///
/// 关闭数据库
///
public void CloseDataBase()
{
this.myConnection.Close();
this.myConnection.Dispose();
this.ds.Clear();
this.ds.Dispose();
GC.Collect();
}
/// 返回SQL语句执行结果的第一行第一列
///
/// 字符串
public string ReturnValue(string SQL)
{
this.myConnection = new SqlConnection(DB_Conn);
myConnection.Open();
string result;
SqlDataReader Dr ;
try
{
SqlCommand Cmd= new SqlCommand(SQL,this.myConnection);
Dr = Cmd.ExecuteReader();
if (Dr.Read())
{
result = Dr[0].ToString();
Dr.Close();
}
else
{
result = “”;
Dr.Close();
}
}
catch
{
throw new Exception(SQL);
}
Dispose(this.myConnection);
return result;
}
/// 运行存储过程,返回dataset.
///
/// 存储过程名.
/// 存储过程入参数组.
/// dataset对象.
public DataSet RunProc(string procName,SqlParameter[] prams,DataSet Ds)
{
this.myConnection = new SqlConnection(DB_Conn);
myConnection.Open();
SqlCommand Cmd = new SqlCommand(procName, this.myConnection);
Cmd.CommandType = CommandType.StoredProcedure;
if (prams != null)
{
foreach (SqlParameter parameter in prams)
{
if(parameter != null)
{
Cmd.Parameters.Add(parameter);
}
}
}
SqlDataAdapter Da = new SqlDataAdapter(Cmd);
try
{
Da.Fill(Ds);
}
catch(Exception Ex)
{
throw Ex;
}
return Ds;
}
/// 返回SQL语句第一列,第ColumnI列,
///
/// 字符串
public string ReturnValue(string SQL, int ColumnI)
{
this.myConnection = new SqlConnection(DB_Conn);
myConnection.Open();
string result;
SqlDataReader Dr ;
try
{
SqlCommand Cmd= new SqlCommand(SQL,this.myConnection);
Dr = Cmd.ExecuteReader();
}
catch
{
throw new Exception(SQL);
}
if (Dr.Read())
{
result = Dr[ColumnI].ToString();
}
else
{
result = “”;
}
Dr.Close();
Dispose(this.myConnection);
return result;
}
public void Dispose(SqlConnection Conn)
{
if(Conn!=null)
{
Conn.Close();
Conn.Dispose();
}
GC.Collect();
}
}
}