Question

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

本人初步接触ASP三层,我知道如果在后台直接写代码
如: sqlconnection con=new sqlconnection("server=.;database=db;integrated security=true");
con.Open();
以上写出代码后确定了登录方式,数据库名称,及安全度
但是如果在三层的DAL层中创建一个DB类,这个类中写一个连接数据库的方法,将如何写?
可以举个小例子或者写段非常精简而且又实用的例子吗?
比如我们用ASP.NET只做一个网站的注册
1.注册页面有 txtName,txtPwd,txtEmail,txtQQ 和 btnLogin
2.创建 Model,BLL,DAL层
3.Model层有User类
private string txtName;
......(定义 txtPwd,txtEmail,txtQQ ---直接省略 明白的一看就懂)
public User
{
this.txtName=String.Empty;
......
}
public string TxtName
{
get{return this.txtName;}
set{this.txtName=Value;}
}
......
4.BLL层调用DAL层方法这个谁都知道
5.DAL层就开始写数据库的连接数据库及增删改查的方法了
我这里不明白的就是 如何在DAL层里面的DB类中写连接数据库的语句
写出来是不是能直接连接数据库
6.还有就是如何在DAL层定义了 Select类 利用Model层的值查找数据库中是否存在的信息
7.希望写的详细点 按照我的思路写出这个小例子的Model,BLL,DAL各层的代码
虽然说精简但是一定不要省略任何代码,
8.真的非常感谢 这次出100分以示对高手你的尊重和我学三层的决心!
希望写出以上思路的整体的例子,代码量那是肯定不多的,精简但不要省略任何代码,谢谢!
如:我新建了一个 website1 三个类库 Model BLL DAL
请将 DAL的 数据库查询类Select.cs,数据库连接类DB.cs里面的所有代码都写出来 数据库需要连接,最主要的是怎么连接,前提工作?命名空间? 详细但不失精简
谢谢啊!

Answer 1

假设数据库连接字符串在web.config里配置如下
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.;Initial Catalog=Database;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>

对你的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);
}
}

Answer 2

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

namespace SQLServerDAL
{
/// <summary>
/// 数据操作类DBHelper.cs
/// 创建于：2009-06-08 03-31-09
/// 作者： Administrator
/// </summary>
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;
}
}
}

Answer 3

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];

}

}
}

Answer 4

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();
}
}
}
}

Answer 5

进错地方了 赶紧撤