C#08.03.10 数据库连接

来源：互联网发布：网络侦探巴尔巴兽编辑：程序博客网时间：2024/05/29 10:00

public DataSet GetEmployee()
    {
        //创建连接
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString;
        connection.Open();
        //创建命令
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandText="SELECT [EmployeeID], [LastName], [FirstName], [Title], [City] FROM [Employees]";
        //获取DataSet
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        //关闭连接
        connection.Close();
        //返回数据
        return ds;
    }

public DataSet GetEmployee(string lastname)
    {
        //创建连接
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString;
        connection.Open();
        //创建命令
        SqlCommand command = new SqlCommand();
        command.Connection = connection;
        command.CommandText = "SELECT [EmployeeID], [LastName], [FirstName], [Title], [City] FROM [Employees] WHERE [lastname] LIKE '%'+@lastname+'%'";
        //command.Parameters["@lastname"].Value=lastname;
        command.Parameters.AddWithValue("@lastname",lastname);
        //获取DataSet
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        //关闭连接
        connection.Close();
        //返回数据
        return ds;
    }

DAL：

public class DataAccess
{
public DataAccess(string connectionstring)
{
        connection = new SqlConnection();
        connection.ConnectionString = connectionstring;
        command = new SqlCommand();
        command.Connection = connection;
}

    public void AddWithValue(string name,string value)
    {
        command.Parameters.AddWithValue(name,value);
    }

    public DataSet GetDataSet(string cmdtext)
    {
        command.CommandText = cmdtext;
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        connection.Open();
        DataSet ds = new DataSet();
        adapter.Fill(ds);
        connection.Close();
        return ds;
    }
    SqlConnection connection;
    SqlCommand command;
}

public DataSet GetEmployee()
    {
         DataAccess da=new DataAccess(ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString);
        return da.GetDataSet("SELECT [EmployeeID], [LastName], [FirstName], [Title], [City] FROM [Employees]");
    }

    public DataSet GetEmployee(string lastname)
    {
        DataAccess da=new DataAccess(ConfigurationManager.ConnectionStrings["NORTHWNDConnectionString"].ConnectionString);
        da.AddWithValue("@lastname", lastname);
        return da.GetDataSet("SELECT [EmployeeID], [LastName], [FirstName], [Title], [City] FROM [Employees] WHERE [lastname] LIKE '%'+@lastname+'%'");

    }

BLL:

public DataSet GetEmployee()
    {
        EmployeeDAL dal = new EmployeeDAL();
        return dal.GetEmployee();
    }

    public DataSet GetEmployee(string lastname)
    {
        EmployeeDAL dal = new EmployeeDAL();
        return dal.GetEmployee(lastname);
    }