看到很多新人满世界大喊:跪求数据库访问类,想自己当初也是这么过来的,所以把自己写的分享出来,已经用在项目中了,当然是小的.有问题请高手斧正,针对access数据库,用sql的可以自己改,我想不是什么难事.完整代码如下:using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
/// <summary>
///DAL 的摘要说明
/// </summary>
public class DAL
{
private static readonly string strcn = ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString;
/// <summary>
/// 关闭打开的connection对象
/// </summary>
/// <param name="cn"></param>
protected static void CloseCn(OleDbConnection conn)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
} /// <summary>
/// 返回一个datatable
/// </summary>
/// <param name="strsql"></param>
/// <param name="pas"></param>
/// <returns></returns>
public static DataTable GetTable(string strsql, params OleDbParameter[] pas)
{
using (OleDbConnection cn = new OleDbConnection(strcn))
{
OleDbDataAdapter da = new OleDbDataAdapter(strsql, cn);
if(pas!=null)
da.SelectCommand.Parameters.AddRange(pas);
DataTable dt = new DataTable();
try
{
da.Fill(dt);
da.SelectCommand.Parameters .Clear ();
return dt;
}
catch
{
CloseCn(cn);
throw;
}
}
}
public static DataTable GetTable(string strsql)
{
return GetTable(strsql, null);
}
/// <summary>
/// 返回带分页功能的dataset,专为aspnetpager控件使用
/// </summary>
/// <param name="str">查询</param>
/// <param name="pagesize">aspnetpager的pagesize属性值</param>
/// <param name="currenpage">aspnetpager的当前页属性值</param>
/// <returns></returns>
public static DataSet GetPageDataSet(string str, int pagesize, int currenpage)
{
using (OleDbConnection cn = new OleDbConnection(strcn))
{
OleDbDataAdapter da = new OleDbDataAdapter(str, cn);
DataSet ds = new DataSet();
da.Fill(ds, pagesize * (currenpage - 1), pagesize, "pagetable");
return ds;
}
} /// <summary>
/// 返回一个datareader
/// </summary>
/// <param name="strsql"></param>
/// <param name="pas"></param>
/// <returns></returns>
public static OleDbDataReader GetReader(string strsql, params OleDbParameter[] pas)
{
OleDbConnection cn = new OleDbConnection(strcn);
try
{
cn.Open();
OleDbCommand cmd = new OleDbCommand(strsql, cn);
if(pas !=null )
cmd.Parameters.AddRange(pas);
OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return dr;
}
catch(Exception e)
{
CloseCn(cn);
throw new Exception(e.Message ,e) ;
}
}
public static OleDbDataReader GetReader(string strsql)
{
return GetReader(strsql, null);
} /// <summary>
/// 执行一条sql语句(insert,update,delete),成功返回true,失败返回false
/// </summary>
/// <param name="strsql"></param>
/// <param name="pas"></param>
/// <returns></returns>
public static bool ExcSql(string strsql, params OleDbParameter[] pas)
{
using (OleDbConnection cn = new OleDbConnection(strcn))
{
OleDbCommand cmd = new OleDbCommand(strsql, cn);
if(pas!=null)
cmd.Parameters.AddRange(pas);
try
{
cn.Open();
cmd.ExecuteNonQuery();
//if(pas!=null )
cmd.Parameters.Clear();
return true ;
}
catch
{
CloseCn(cn);
//throw;
return false ;
}
}
} public static bool ExcSql(string strsql)
{
return ExcSql (strsql ,null );
} /// <summary>
/// 返回一个整数值,如count,sum,avg,max,min,失败返回-1(此时通常是无记录)
/// </summary>
/// <param name="strsql">查询语句</param>
/// <returns></returns>
public static int GetNum(string strsql)
{
using (OleDbConnection cn = new OleDbConnection(strcn))
{
OleDbCommand cmd = new OleDbCommand(strsql, cn);
try
{
cn.Open();
int i = (int)cmd.ExecuteScalar();
return i;
}
catch
{
return -1;
}
}
}
/// <summary>
/// 返回一个字段值(字符串),失败返回字符串"null".
/// </summary>
/// <param name="strsql"></param>
/// <returns></returns>
public static string GetFieldValue(string strsql)
{
using (OleDbConnection cn = new OleDbConnection(strcn))
{
OleDbCommand cmd = new OleDbCommand(strsql, cn);
try
{
cn.Open();
return cmd.ExecuteScalar().ToString ();
}
catch
{
return "null";
}
}
}}
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
/// <summary>
///DAL 的摘要说明
/// </summary>
public class DAL
{
private static readonly string strcn = ConfigurationManager.ConnectionStrings["cnstr"].ConnectionString;
/// <summary>
/// 关闭打开的connection对象
/// </summary>
/// <param name="cn"></param>
protected static void CloseCn(OleDbConnection conn)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
} /// <summary>
/// 返回一个datatable
/// </summary>
/// <param name="strsql"></param>
/// <param name="pas"></param>
/// <returns></returns>
public static DataTable GetTable(string strsql, params OleDbParameter[] pas)
{
using (OleDbConnection cn = new OleDbConnection(strcn))
{
OleDbDataAdapter da = new OleDbDataAdapter(strsql, cn);
if(pas!=null)
da.SelectCommand.Parameters.AddRange(pas);
DataTable dt = new DataTable();
try
{
da.Fill(dt);
da.SelectCommand.Parameters .Clear ();
return dt;
}
catch
{
CloseCn(cn);
throw;
}
}
}
public static DataTable GetTable(string strsql)
{
return GetTable(strsql, null);
}
/// <summary>
/// 返回带分页功能的dataset,专为aspnetpager控件使用
/// </summary>
/// <param name="str">查询</param>
/// <param name="pagesize">aspnetpager的pagesize属性值</param>
/// <param name="currenpage">aspnetpager的当前页属性值</param>
/// <returns></returns>
public static DataSet GetPageDataSet(string str, int pagesize, int currenpage)
{
using (OleDbConnection cn = new OleDbConnection(strcn))
{
OleDbDataAdapter da = new OleDbDataAdapter(str, cn);
DataSet ds = new DataSet();
da.Fill(ds, pagesize * (currenpage - 1), pagesize, "pagetable");
return ds;
}
} /// <summary>
/// 返回一个datareader
/// </summary>
/// <param name="strsql"></param>
/// <param name="pas"></param>
/// <returns></returns>
public static OleDbDataReader GetReader(string strsql, params OleDbParameter[] pas)
{
OleDbConnection cn = new OleDbConnection(strcn);
try
{
cn.Open();
OleDbCommand cmd = new OleDbCommand(strsql, cn);
if(pas !=null )
cmd.Parameters.AddRange(pas);
OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return dr;
}
catch(Exception e)
{
CloseCn(cn);
throw new Exception(e.Message ,e) ;
}
}
public static OleDbDataReader GetReader(string strsql)
{
return GetReader(strsql, null);
} /// <summary>
/// 执行一条sql语句(insert,update,delete),成功返回true,失败返回false
/// </summary>
/// <param name="strsql"></param>
/// <param name="pas"></param>
/// <returns></returns>
public static bool ExcSql(string strsql, params OleDbParameter[] pas)
{
using (OleDbConnection cn = new OleDbConnection(strcn))
{
OleDbCommand cmd = new OleDbCommand(strsql, cn);
if(pas!=null)
cmd.Parameters.AddRange(pas);
try
{
cn.Open();
cmd.ExecuteNonQuery();
//if(pas!=null )
cmd.Parameters.Clear();
return true ;
}
catch
{
CloseCn(cn);
//throw;
return false ;
}
}
} public static bool ExcSql(string strsql)
{
return ExcSql (strsql ,null );
} /// <summary>
/// 返回一个整数值,如count,sum,avg,max,min,失败返回-1(此时通常是无记录)
/// </summary>
/// <param name="strsql">查询语句</param>
/// <returns></returns>
public static int GetNum(string strsql)
{
using (OleDbConnection cn = new OleDbConnection(strcn))
{
OleDbCommand cmd = new OleDbCommand(strsql, cn);
try
{
cn.Open();
int i = (int)cmd.ExecuteScalar();
return i;
}
catch
{
return -1;
}
}
}
/// <summary>
/// 返回一个字段值(字符串),失败返回字符串"null".
/// </summary>
/// <param name="strsql"></param>
/// <returns></returns>
public static string GetFieldValue(string strsql)
{
using (OleDbConnection cn = new OleDbConnection(strcn))
{
OleDbCommand cmd = new OleDbCommand(strsql, cn);
try
{
cn.Open();
return cmd.ExecuteScalar().ToString ();
}
catch
{
return "null";
}
}
}}
解决方案 »
- treeview 添加节点问题
- post的数据是:Content-Type: multipart/form-data; 程序如何实现编码
- 关于通过银行卡号获取发卡行名称
- 第三方控件不能调用javascript?
- 关于grideview的一个功能问题
- 想用DATAGRID作这样的效果--象论坛一样的第一行是主题,当你一点击的话,下面的跟贴就显示出来
- asp.net2.0空间
- 软件技术QQ群 1265452 有意的请加入
- 用户控件放到应用程序中css样式表的问题?有源代码
- 如何在datagrid模板形式下改变数据表的某一列的日期显示形式?
- 求助求助啊。各位帮帮忙啊
- asp.net中,文本框的内容如何写入access数据库
有返回Objesct的值,建议返回泛型。减少拆箱装箱操作
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); // Create & open a SqlConnection, and dispose of it after we are done
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open(); // Call the overload that takes a connection in place of the connection string
return ExecuteDataset(connection, commandType, commandText, commandParameters);
}
}
加一个commandType,调用的时候就可以自己选择是用存储过程还是Sql语句了。