当前文件路径：ClothesShop/DBUtility/OleDbHelper.cs

		            
  1using System;
  2using System.Collections.Generic;
  3using System.Text;
  4using System.Configuration;
  5using System.Data;
  6using System.Data.OleDb;
  7using System.Web;
  8
  9namespace ClothesShop.DBUtility
 10...{
 11    public class OleDbHelper
 12    ...{
 13        public OleDbHelper()...{ }
 14
 15    /**//// <summary>
 16    /// 返回数据库连接字符串
 17    /// </summary>
 18    /// <returns></returns>
 19    public static String GetSqlConnection()
 20    ...{
 21        System.Web.UI.Page page = new System.Web.UI.Page();
 22        String conn = ConfigurationSettings.AppSettings["OleDbConnectionString"].ToString() + page.Server.MapPath("~/" + ConfigurationSettings.AppSettings["AccessDbPath"].ToString());
 23        return conn;
 24    }
 25
 26    /**//// <summary>
 27    ///  获得参数对象 
 28    /// </summary>
 29    /// <param name="paramName">参数名称</param>
 30    /// <param name="paramType">数据类型</param>
 31    /// <param name="paramSize">长度</param>
 32    /// <param name="ColName">源列名称</param>
 33    /// <param name="paramValue">参数实值</param>
 34    /// <returns></returns>
 35    public static OleDbParameter GetParameter(String paramName, OleDbType paramType, Int32 paramSize, String ColName, Object paramValue)
 36    ...{
 37        OleDbParameter param = new OleDbParameter(paramName, paramType, paramSize, ColName);
 38        param.Value = paramValue;
 39        return param;
 40    }
 41
 42    /**//// <summary>
 43    /// 获得参数对象
 44    /// </summary>
 45    /// <param name="paramName">参数名称</param>
 46    /// <param name="paramType">数据类型</param>
 47    /// <param name="paramSize">长度</param>
 48    /// <param name="ColName">源列名称</param>
 49    /// <returns></returns>
 50    public static OleDbParameter GetParameter(String paramName, OleDbType paramType, Int32 paramSize, String ColName)
 51    ...{
 52        OleDbParameter param = new OleDbParameter(paramName, paramType, paramSize, ColName);
 53        return param;
 54    }
 55
 56    /**//// <summary>
 57    /// 获得参数对象
 58    /// </summary>
 59    /// <param name="paramName">参数名称</param>
 60    /// <param name="paramType">数据类型</param>
 61    /// <param name="paramSize">长度</param>
 62    /// <param name="ColName">源列名称</param>
 63    /// <returns></returns>
 64    public static OleDbParameter GetParameter(String paramName, OleDbType paramType, Object paramValue)
 65    ...{
 66        OleDbParameter param = new OleDbParameter(paramName,paramType);
 67        param.Value = paramValue;
 68        return param;
 69    }
 70
 71    /**//// <summary>
 72    /// 执行SQL语句
 73    /// </summary>
 74    /// <param name="Sqlstr">SQL语句</param>
 75    /// <param name="param">参数对象数组</param>
 76    /// <returns></returns>
 77    public static int ExecuteSql(String Sqlstr, OleDbParameter[] param)
 78    ...{
 79        String ConnStr = OleDbHelper.GetSqlConnection();
 80        using (OleDbConnection conn = new OleDbConnection(ConnStr))
 81        ...{
 82            OleDbCommand cmd = new OleDbCommand();
 83            cmd.Connection = conn;
 84            cmd.CommandText = Sqlstr;
 85            cmd.Parameters.AddRange(param);
 86            conn.Open();
 87            cmd.ExecuteNonQuery();
 88            conn.Close();
 89            return 1;
 90        }
 91    }
 92
 93    /**//// <summary>
 94    /// 执行SQL语句并返回数据表
 95    /// </summary>
 96    /// <param name="Sqlstr">SQL语句</param>
 97    /// <returns></returns>
 98    public static DataTable ExecuteDt(String Sqlstr)
 99    ...{
100        String ConnStr = OleDbHelper.GetSqlConnection();
101        using (OleDbConnection conn = new OleDbConnection(ConnStr))
102        ...{
103            OleDbDataAdapter da = new OleDbDataAdapter(Sqlstr, conn);
104            DataTable dt = new DataTable();
105            conn.Open();
106            da.Fill(dt);
107            conn.Close();
108            return dt;
109        }
110    }
111
112    /**//// <summary>
113    /// 执行SQL语句并返回数据表
114    /// </summary>
115    /// <param name="Sqlstr">SQL语句</param>
116    /// <param name="param">参数对象列表</param>
117    /// <returns></returns>
118    public static DataTable ExecuteDt(String Sqlstr, OleDbParameter[] param)
119    ...{
120        String ConnStr = OleDbHelper.GetSqlConnection();
121        using (OleDbConnection conn = new OleDbConnection(ConnStr))
122        ...{
123            DataTable dt = new DataTable();
124            OleDbDataAdapter da = new OleDbDataAdapter();
125            OleDbCommand cmd = new OleDbCommand(Sqlstr,conn);
126            cmd.Connection = conn;
127            cmd.Parameters.AddRange(param);
128            da.SelectCommand = cmd;
129            conn.Open();
130            da.Fill(dt);
131            conn.Close();
132            return dt;
133        }
134    }
135
136    /**//// <summary>
137    /// 批量执行SQL语句
138    /// </summary>
139    /// <param name="Sqlstr">SQL语句数组</param>
140    /// <param name="param">SQL参数对象数组</param>
141    /// <returns></returns>
142    public static Int32 ExecuteSqls(String [] Sqlstr,List<OleDbParameter []> param)
143    ...{
144        String ConnStr = OleDbHelper.GetSqlConnection();
145        using (OleDbConnection conn = new OleDbConnection(ConnStr))
146        ...{
147            
148            OleDbCommand cmd = new OleDbCommand();
149            OleDbTransaction tran = null;
150            cmd.Transaction = tran;
151            try
152            ...{
153                conn.Open();
154                tran = conn.BeginTransaction();
155                cmd.Connection = conn;
156                cmd.Transaction = tran;
157
158                Int32 count = Sqlstr.Length;
159                for (Int32 i = 0; i < count; i ++ )
160                ...{
161                    cmd.CommandText = Sqlstr[i];
162                    cmd.Parameters.AddRange(param[i]);
163                    cmd.ExecuteNonQuery();
164                }
165                tran.Commit();
166                return 1;
167            }
168            catch
169            ...{
170                tran.Rollback();
171                return 0;
172            }
173            finally
174            ...{
175                cmd.Dispose();
176                conn.Close();
177            }
178        }
179    }
180    }
181}
182