C# SQL LINK数据库操作类
- 格式：doc
- 大小：52.00 KB
- 文档页数：14
sqllink.cs c#数据库操作类 using System; using System.Configuration; using System.Data; using System.Data.SqlClient;
namespace Chenzhang {
/// /// SQL Server数据库操作组件 /// public class SqlLink :IDisposable { private SqlDataAdapter dsCommand; private AppSettingsReader appsettingsreader; private String cn;
/// /// Constructor /// public SqlLink() { dsCommand =new SqlDataAdapter() ; appsettingsreader = new AppSettingsReader(); cn=(string)appsettingsreader.GetValue("SQLConnectionString", typeof(string)); }
/// /// Dispose /// public void Dispose() { Dispose(true); GC.SuppressFinalize(this); }
protected virtual void Dispose(bool disposing) { if (!disposing) return;
if (dsCommand != null) { //dispose dsCommand.SelectCommand if (dsCommand.SelectCommand!=null) { if (dsCommand.SelectCommand.Connection!=null) dsCommand.SelectCommand.Connection.Dispose(); dsCommand.SelectCommand.Dispose(); }
//dispose dsCommand.InsertCommand if (dsCommand.InsertCommand!=null) { if (dsCommand.InsertCommand.Connection!=null) dsCommand.InsertCommand.Connection.Dispose(); dsCommand.InsertCommand.Dispose(); }
//dispose dsCommand.UpdateCommand if (dsCommand.UpdateCommand!=null) { if (dsCommand.UpdateCommand.Connection!=null) dsCommand.UpdateCommand.Connection.Dispose(); dsCommand.UpdateCommand.Dispose(); }
dsCommand.Dispose(); dsCommand=null; }
} /// /// 获取select语句查询结果记录集 /// /// select语句 /// 结果记录集 public DataTable SelectSql(String sql) { dsCommand.SelectCommand=new SqlCommand(); dsCommand.SelectCommand.Connection=new SqlConnection(cn); DataSet ds=new DataSet() ; dsCommand.SelectCommand.CommandText=sql; dsCommand.SelectCommand.CommandType=CommandType.Text; dsCommand.SelectCommand.Connection.Open(); try { dsCommand.Fill(ds,"result"); return ds.Tables["result"]; } catch { return null; } finally { if (dsCommand.SelectCommand!=null) { if (dsCommand.SelectCommand.Connection!=null) dsCommand.SelectCommand.Connection.Dispose(); dsCommand.SelectCommand.Dispose(); } dsCommand.Dispose(); ds.Dispose(); } } /// /// 获取存储过程运行结果记录集，存储过程不带参数 /// /// 存储过程名称 /// 结果记录集 public DataTable SelectSql_sp(String sp_name) { dsCommand.SelectCommand=new SqlCommand(); dsCommand.SelectCommand.Connection=new SqlConnection(cn); DataSet ds=new DataSet() ; dsCommand.SelectCommand.CommandText=sp_name; dsCommand.SelectCommand.CommandType=CommandType.StoredProcedure; dsCommand.SelectCommand.Connection.Open(); try { dsCommand.Fill(ds,"result"); return ds.Tables["result"]; } catch { return null; } finally { if (dsCommand.SelectCommand!=null) { if (dsCommand.SelectCommand.Connection!=null) dsCommand.SelectCommand.Connection.Dispose(); dsCommand.SelectCommand.Dispose(); } dsCommand.Dispose(); ds.Dispose(); } } /// /// 获取存储过程运行结果记录集，存储过程带1个参数 /// /// 存储过程名称 /// 存储过程参数名称，如"@Age","@StudentName" /// 存储过程参数类型，如"int","varchar" /// 存储过程参数值，如"18","张三" /// 结果记录集 public DataTable SelectSql_sp_par1(String sp_name,String par1_name,String par1_type,String par1_value) { int par1_dbtype=0; if (par1_type.ToLower()=="varchar") par1_dbtype=Convert.ToInt16(SqlDbType.VarChar); else if (par1_type.ToLower()=="string") par1_dbtype=Convert.ToInt16(SqlDbType.VarChar); else if (par1_type.ToLower()=="integer") par1_dbtype=Convert.ToInt16(SqlDbType.Int); else if (par1_type.ToLower()=="int") par1_dbtype=Convert.ToInt16(SqlDbType.Int); dsCommand.SelectCommand=new SqlCommand(); dsCommand.SelectCommand.Connection=new SqlConnection(cn); DataSet ds=new DataSet() ; dsCommand.SelectCommand.CommandText=sp_name; dsCommand.SelectCommand.CommandType=CommandType.StoredProcedure; SqlParameter objPar; objPar=dsCommand.SelectCommand.Parameters.Add(par1_name,par1_dbtype); objPar.Direction=ParameterDirection.Input; objPar.Value=par1_value;
