ASP.NET三层架构DAL层连接数据库的方法

假设数据库连接字符串在web.config里配置如下

providerName="System.Data.SqlClient" />


对你的DAL项目右键添加System.Configuration引用(必须步骤),
添加Model项目引用
然后在DB类(假设为UserDAL.cs)
using System.Configuration;//这个必须.
using Model那个项目
public class UserDAL
{
public const string ConnectionString = ConfigurationManager.ConnectionStrings [ "ConnectionString" ].ConnectionString;
public int Insert(UserInfo user)// Model
{

SqlConnection sqlcon=new SqlConnection ( ConnectionString );
.................
}
}

BLL
添加DB和Model项目引用
using 那个DB
public class UserBLL
{
public int Insert(UserInfo user)
{
int i = UserDAL.Insert(user);
}
}

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace SQLServerDAL
{
///

/// 数据操作类DBHelper.cs
/// 创建于：2009-06-08 03-31-09
/// 作者： Administrator
///

class DBHelper
{
static SqlConnection cnn;
static string cnnString ;
static DBHelper()
{
cnnString= ConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString;
}

public static SqlConnection Connection
{
get
{
if(cnn==null)
{
cnn = new SqlConnection(cnnString);
}
if (cnn.State==ConnectionState.Closed)
{
cnn.Open();
}
if (cnn.State==ConnectionState.Broken)
{
cnn.Close();
cnn.Open();
}
return cnn;
}
}

public static int ExecuteCommand(string sql)
{
SqlCommand cmd = Connection.CreateCommand();
cmd.CommandText = sql;
int x = cmd.ExecuteNonQuery();
Connection.Close();
return x;
}

public static int ExecuteCommand(string procName,SqlParameter[] ps)
{
SqlCommand cmd = Connection.CreateCommand();
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(ps);
int x = cmd.ExecuteNonQuery();
Connection.Close();
return x;
}

public static DataTable GetTable(string sql)
{
SqlCommand cmd = Connection.CreateCommand();
cmd.CommandText = sql;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}

public static DataTable GetTable(string procName, SqlParameter[] ps)
{
SqlCommand cmd = Connection.CreateCommand();
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(ps);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}

public static object GetScalar(string sql)
{
SqlCommand cmd = Connection.CreateCommand();
cmd.CommandText = sql;
object x = cmd.ExecuteScalar();
Connection.Close();
return x;
}

public static object GetScalar(string procName, SqlParameter[] ps)
{
SqlCommand cmd = Connection.CreateCommand();
cmd.CommandText = procName;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(ps);
object x = cmd.ExecuteScalar();
Connection.Close();
return x;
}
}
}

using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace StuDAL
{
public static class DBHelper
{

private static SqlConnection connection;
public static SqlConnection Connection
{
get
{
string connectionString = ConfigurationManager.ConnectionStrings["MyOfficeConnectionString"].ConnectionString;
connection = new SqlConnection(connectionString);
if (connection == null)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Closed)
{
connection.Open();
}
else if (connection.State == System.Data.ConnectionState.Broken)
{
connection.Close();
connection.Open();
}
return connection;
}
}

public static int ExecuteCommand(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
int result = cmd.ExecuteNonQuery();
return result;
}

public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
return cmd.ExecuteNonQuery();
}

public static string ReturnStringScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
try
{
string result = cmd.ExecuteScalar().ToString();
return result;
}
catch (Exception e)
{
return "0";
}
connection.Close();
}

public static int GetScalar(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
try
{
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
}
catch (Exception e)
{
return 0;
}
connection.Close();
}

public static int GetScalar(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
return result;
connection.Close();
}

public static SqlDataReader GetReader(string safeSql)
{
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
reader.Close();
reader.Dispose();
}

public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader();
return reader;
reader.Close();
reader.Dispose();

}

public static DataTable GetDataSet(string safeSql)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(safeSql, Connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
connection.Close();
connection.Dispose();
return ds.Tables[0];
}

public static DataTable GetDataSet(string sql, params SqlParameter[] values)
{
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
connection.Close();
connection.Dispose();
return ds.Tables[0];

}

}
}

using System.Data.SqlClient;
using System.Text;
public int Add(Model.User model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into User(");
strSql.Append("Name,pwd,Email");
strSql.Append(")");
strSql.Append(" values (");
strSql.Append("'" + model.TxtName+ "',");
strSql.Append("'" + model.txtPwd+ "',");
strSql.Append("'" + model.txtEmail+ "'");
strSql.Append(")");
return SQLHelper.getInt(strSql.ToString());
}

SQLHelper.cs页面
using System.Data.SqlClient;
public class SQLHelper
{

protected static SqlConnection connection = new SqlConnection("server=.;database=db;integrated security=true");
public int getInt(string sql)
{
try
{
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
SqlCommand command = new SqlCommand(sql, connection);
int Int = Convert.ToInt32(command.ExecuteNonQuery());
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
return Int;
}
finally
{
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
}
}
}

进错地方了 赶紧撤