Question

c# 中，怎么把连接数据库写成一个类？

Answer 1

using System;
using System.Data;
using System.Text;
using System.Data.OleDb;
using Maticsoft.IDAL;
using Maticsoft.DBUtility;//请先添加引用
namespace Maticsoft.OleDbDAL
{
/// <summary>
/// 数据访问类Train。
/// </summary>
public class Train:ITrain
{
public Train()
{}
#region 成员方法

/// <summary>
/// 增加一条数据
/// </summary>
public void Add(Maticsoft.Model.Train model)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("insert into Train(");
strSql.Append("ID,P2,P3,P4,Type,Station,S_No,Day,A_Time,D_Time,Distance,P1)");
strSql.Append(" values (");
strSql.Append("@ID,@P2,@P3,@P4,@Type,@Station,@S_No,@Day,@A_Time,@D_Time,@Distance,@P1)");
OleDbParameter[] parameters = {
new OleDbParameter("@ID", OleDbType.VarChar,20),
new OleDbParameter("@P2", OleDbType.VarChar,20),
new OleDbParameter("@P3", OleDbType.VarChar,20),
new OleDbParameter("@P4", OleDbType.VarChar,20),
new OleDbParameter("@Type", OleDbType.VarChar,20),
new OleDbParameter("@Station", OleDbType.VarChar,20),
new OleDbParameter("@S_No", OleDbType.VarChar,5),
new OleDbParameter("@Day", OleDbType.VarChar,5),
new OleDbParameter("@A_Time", OleDbType.VarChar,20),
new OleDbParameter("@D_Time", OleDbType.VarChar,20),
new OleDbParameter("@Distance", OleDbType.VarChar,20),
new OleDbParameter("@P1", OleDbType.VarChar,20)};
parameters[0].Value = model.ID;
parameters[1].Value = model.P2;
parameters[2].Value = model.P3;
parameters[3].Value = model.P4;
parameters[4].Value = model.Type;
parameters[5].Value = model.Station;
parameters[6].Value = model.S_No;
parameters[7].Value = model.Day;
parameters[8].Value = model.A_Time;
parameters[9].Value = model.D_Time;
parameters[10].Value = model.Distance;
parameters[11].Value = model.P1;

DbHelperOleDb.ExecuteSql(strSql.ToString(),parameters);
}
/// <summary>
/// 更新一条数据
/// </summary>
public void Update(Maticsoft.Model.Train model)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("update Train set ");
strSql.Append("ID=@ID,");
strSql.Append("P2=@P2,");
strSql.Append("P3=@P3,");
strSql.Append("P4=@P4,");
strSql.Append("Type=@Type,");
strSql.Append("Station=@Station,");
strSql.Append("S_No=@S_No,");
strSql.Append("Day=@Day,");
strSql.Append("A_Time=@A_Time,");
strSql.Append("D_Time=@D_Time,");
strSql.Append("Distance=@Distance,");
strSql.Append("P1=@P1");
strSql.Append(" where ");
OleDbParameter[] parameters = {
new OleDbParameter("@ID", OleDbType.VarChar,20),
new OleDbParameter("@P2", OleDbType.VarChar,20),
new OleDbParameter("@P3", OleDbType.VarChar,20),
new OleDbParameter("@P4", OleDbType.VarChar,20),
new OleDbParameter("@Type", OleDbType.VarChar,20),
new OleDbParameter("@Station", OleDbType.VarChar,20),
new OleDbParameter("@S_No", OleDbType.VarChar,5),
new OleDbParameter("@Day", OleDbType.VarChar,5),
new OleDbParameter("@A_Time", OleDbType.VarChar,20),
new OleDbParameter("@D_Time", OleDbType.VarChar,20),
new OleDbParameter("@Distance", OleDbType.VarChar,20),
new OleDbParameter("@P1", OleDbType.VarChar,20)};
parameters[0].Value = model.ID;
parameters[1].Value = model.P2;
parameters[2].Value = model.P3;
parameters[3].Value = model.P4;
parameters[4].Value = model.Type;
parameters[5].Value = model.Station;
parameters[6].Value = model.S_No;
parameters[7].Value = model.Day;
parameters[8].Value = model.A_Time;
parameters[9].Value = model.D_Time;
parameters[10].Value = model.Distance;
parameters[11].Value = model.P1;

DbHelperOleDb.ExecuteSql(strSql.ToString(),parameters);
}

/// <summary>
/// 删除一条数据
/// </summary>
public void Delete()
{
//该表无主键信息，请自定义主键/条件字段
StringBuilder strSql=new StringBuilder();
strSql.Append("delete from Train ");
strSql.Append(" where ");
OleDbParameter[] parameters = {
};

DbHelperOleDb.ExecuteSql(strSql.ToString(),parameters);
}

/// <summary>
/// 得到一个对象实体
/// </summary>
public Maticsoft.Model.Train GetModel()
{
//该表无主键信息，请自定义主键/条件字段
StringBuilder strSql=new StringBuilder();
strSql.Append("select ID,P2,P3,P4,Type,Station,S_No,Day,A_Time,D_Time,Distance,P1 from Train ");
strSql.Append(" where ");
OleDbParameter[] parameters = {
};

Maticsoft.Model.Train model=new Maticsoft.Model.Train();
DataSet ds=DbHelperOleDb.Query(strSql.ToString(),parameters);
if(ds.Tables[0].Rows.Count>0)
{
model.ID=ds.Tables[0].Rows[0]["ID"].ToString();
model.P2=ds.Tables[0].Rows[0]["P2"].ToString();
model.P3=ds.Tables[0].Rows[0]["P3"].ToString();
model.P4=ds.Tables[0].Rows[0]["P4"].ToString();
model.Type=ds.Tables[0].Rows[0]["Type"].ToString();
model.Station=ds.Tables[0].Rows[0]["Station"].ToString();
model.S_No=ds.Tables[0].Rows[0]["S_No"].ToString();
model.Day=ds.Tables[0].Rows[0]["Day"].ToString();
model.A_Time=ds.Tables[0].Rows[0]["A_Time"].ToString();
model.D_Time=ds.Tables[0].Rows[0]["D_Time"].ToString();
model.Distance=ds.Tables[0].Rows[0]["Distance"].ToString();
model.P1=ds.Tables[0].Rows[0]["P1"].ToString();
return model;
}
else
{
return null;
}
}

/// <summary>
/// 获得数据列表
/// </summary>
public DataSet GetList(string strWhere)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("select ID,P2,P3,P4,Type,Station,S_No,Day,A_Time,D_Time,Distance,P1 ");
strSql.Append(" FROM Train ");
if(strWhere.Trim()!="")
{
strSql.Append(" where "+strWhere);
}
return DbHelperOleDb.Query(strSql.ToString());
}

/*
/// <summary>
/// 分页获取数据列表
/// </summary>
public DataSet GetList(int PageSize,int PageIndex,string strWhere)
{
OleDbParameter[] parameters = {
new OleDbParameter("@tblName", OleDbType.VarChar, 255),
new OleDbParameter("@fldName", OleDbType.VarChar, 255),
new OleDbParameter("@PageSize", OleDbType.Integer),
new OleDbParameter("@PageIndex", OleDbType.Integer),
new OleDbParameter("@IsReCount", OleDbType.Bit),
new OleDbParameter("@OrderType", OleDbType.Bit),
new OleDbParameter("@strWhere", OleDbType.VarChar,1000),
};
parameters[0].Value = "Train";
parameters[1].Value = "ID";
parameters[2].Value = PageSize;
parameters[3].Value = PageIndex;
parameters[4].Value = 0;
parameters[5].Value = 0;
parameters[6].Value = strWhere;
return DbHelperOleDb.RunProcedure("UP_GetRecordByPage",parameters,"ds");
}*/

#endregion 成员方法
}
}

Answer 2

1、新建一个类：譬如：DBCon.cs.添加代码如下
public class DBcon
{
public DBcon()
{
//
// TODO: 在此处添加构造函数逻辑
//
}
public static SqlConnection create()
{
String str = ConfigurationManager.ConnectionStrings["con"].ToString();
SqlConnection sql = new SqlConnection(str);
return sql;
}
2、在Web.config中添加代码如下：
<connectionStrings>
<add name="con" connectionString="链接数据库语句"
providerName="System.Data.SqlClient" />
</connectionStrings>
3、在需要链接数据库的地方写如下代码，即完成连接数据库：
SqlConnection sqlConn = DBcon.create();
sqlConn.Open();

Answer 3

新建一个类，内容如下:
using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.SqlClient;

public class db
{
public db()
{
}
public static SqlConnection CreateConnection()
{
SqlConnection con = new SqlConnection("Data Source=localhost;Initial Catalog=ZSJ_DAS;User ID=sa;pwd=");
return con;
}
}

然后在项目里就可以直接:
SqlConnection mycon = db.CreateConnection();
mycon.Open();

Answer 4

class db
{
public static string strCon="数据库连接字符串"
public db(){}
public void Open(){1.获取连接 2.打开连接}
public void close(){关闭连接}
public DataTable GetDataTable(sql)
{
DataTable dt=new DataTable();
try{
1.Open();
2.dt=执行SQL语句;

}
catch(异常){抛出异常}
finaly{close();}
return dt;
}
}

Answer 5

model项目 每个表都写一个类，将表的字段写成私有属性，并用get，set来读取和写入