access+sqlserver通用数据库访问基类

 

  1using System;
  2using System.Data;
  3using System.Configuration;
  4using System.Web;
  5using System.Web.Security;
  6using System.Web.UI;
  7using System.Web.UI.WebControls;
  8using System.Web.UI.WebControls.WebParts;
  9using System.Web.UI.HtmlControls;
 10using System.Data.OleDb;
 11
 12/**//// <summary>
 13/// DataOp 的摘要说明
 14/// </summary>
 15public abstract class DataOp
 16{
 17    private static readonly string connstr = ConfigurationManager.ConnectionStrings["SqlDb"].ConnectionString;
 18    
 19    执行sql语句#region 执行sql语句
 20    public static int RunSql(string sql)
 21    {
 22        OleDbCommand oleCmd = new OleDbCommand();
 23        using (OleDbConnection conn = new OleDbConnection(connstr))
 24        {
 25            PrepareCommand(oleCmd, conn,null,CommandType.Text, sql,null);
 26            int val = oleCmd.ExecuteNonQuery();
 27            return val;
 28        }
 29    }
 30    #endregion
 31
 32    执行sql语句#region 执行sql语句
 33    public static int RunSql(string sql,OleDbParameter[] parms)
 34    {
 35        OleDbCommand oleCmd = new OleDbCommand();
 36        using (OleDbConnection conn = new OleDbConnection(connstr))
 37        {
 38            PrepareCommand(oleCmd, conn, null, CommandType.Text, sql,parms);
 39            int val = oleCmd.ExecuteNonQuery();
 40            return val;
 41        }
 42    }
 43    #endregion
 44
 45    返回DataSet#region 返回DataSet
 46    public static DataSet RunSqlDs(string sql)
 47    {
 48        OleDbCommand oleCmd = new OleDbCommand();
 49        OleDbConnection conn = new OleDbConnection(connstr);
 50        try
 51        {
 52            PrepareCommand(oleCmd, conn, null, CommandType.Text, sql, null);            
 53            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
 54            DataSet ds = new DataSet();
 55            da.Fill(ds);
 56            return ds;
 57        }
 58        catch
 59        {
 60            conn.Close();
 61            throw;
 62        }
 63    }
 64    #endregion
 65
 66    返回DataSet#region 返回DataSet
 67    public static DataSet RunSqlDs(string sql,OleDbParameter[] parms)
 68    {
 69        OleDbCommand oleCmd = new OleDbCommand();
 70        OleDbConnection conn = new OleDbConnection(connstr);
 71        try
 72        {
 73            PrepareCommand(oleCmd, conn, null, CommandType.Text, sql, parms);            
 74            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
 75            DataSet ds = new DataSet();
 76            da.Fill(ds);
 77            return ds;
 78        }
 79        catch
 80        {
 81            conn.Close();
 82            throw;
 83        }
 84    }
 85    #endregion
 86
 87    返回DataTable#region 返回DataTable
 88    public static DataTable RunSqlDt(string sql)
 89    {
 90        OleDbCommand oleCmd = new OleDbCommand();
 91        OleDbConnection conn = new OleDbConnection(connstr);
 92        try
 93        {
 94            PrepareCommand(oleCmd, conn, null, CommandType.Text, sql, null);            
 95            
 96            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
 97            DataTable dt = new DataTable();
 98            da.Fill(dt);
 99            return dt;
100        }
101        catch
102        {
103            conn.Close();
104            throw;
105        }
106    }
107    #endregion
108
109    返回DataTable#region 返回DataTable
110    public static DataTable RunSqlDt(string sql,OleDbParameter[] parms)
111    {
112        OleDbCommand oleCmd = new OleDbCommand();
113        OleDbConnection conn = new OleDbConnection(connstr);
114        try
115        {
116            
117            PrepareCommand(oleCmd, conn, null, CommandType.Text, sql, parms);            
118
119            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
120            DataTable dt = new DataTable();
121            da.Fill(dt);
122            return dt;
123        }
124        catch
125        {
126            conn.Close();
127            throw;
128        }
129    }
130    #endregion
131
132    返回DataReader#region 返回DataReader
133    public static OleDbDataReader RunSqlDr(string sql)
134    {
135        OleDbCommand oleCmd = new OleDbCommand();
136        OleDbConnection conn = new OleDbConnection(connstr);       
137        try
138        {
139           
140            PrepareCommand(oleCmd, conn, null, CommandType.Text, sql, null);            
141
142            OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); 
143            return dr;
144        }
145        catch
146        {
147            conn.Close();
148            throw;
149        }
150    }
151    #endregion
152
153    返回DataReader#region 返回DataReader
154    public static OleDbDataReader RunSqlDr(string sql,OleDbParameter[] parms)
155    {
156        OleDbCommand oleCmd = new OleDbCommand();
157        OleDbConnection conn = new OleDbConnection(connstr);
158        try
159        {
160
161            PrepareCommand(oleCmd, conn, null, CommandType.Text, sql, parms);
162
163            OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
164            return dr;
165        }
166        catch
167        {
168            conn.Close();
169            throw;
170        }
171    }
172    #endregion
173
174
175    准备连接数据库#region 准备连接数据库
176    /**//// <summary>
177    /// 准备连接数据库
178    /// </summary>
179    /// <param name="oleCmd"></param>
180    /// <param name="oleConn"></param>
181    /// <param name="cmdText"></param>
182    private static void PrepareCommand(OleDbCommand oleCmd, OleDbConnection oleConn,OleDbTransaction trans,CommandType cmdType, string cmdText, OleDbParameter[] cmdParms)
183    {
184        if (oleConn.State != ConnectionState.Open)
185            oleConn.Open();
186        oleCmd.Connection = oleConn;
187        oleCmd.CommandText = cmdText;
188
189        if (trans != null)
190            oleCmd.Transaction = trans;
191
192        oleCmd.CommandType = cmdType;
193
194        if (cmdParms != null)
195        {
196            foreach (OleDbParameter parm in cmdParms)
197            {
198                if (parm != null)
199                {
200                    oleCmd.Parameters.Add(parm);
201                }
202            }
203        }
204    }
205    #endregion 
206}
207
208