三层调用

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

namespace DBAccess
{
    public class SqlConn
    {
        private static string str = "server=.;database=Panmery;uid=sa;pwd=123";
        public static SqlConnection CreaterConnection()
        {
            SqlConnection con = new SqlConnection(str);
            con.Open();
            return con;
        }
    }
}

 

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

namespace DBAccess
{
    public class SqlHelper
    {
        SqlConnection con;//数据库连接对象
        SqlCommand cmd;//增删改查操作对象
        SqlDataAdapter sda;//数据库适配器
        DataSet ds;//数据集

        /// <summary>
        /// 返回数据库操作后的受影响行数
        /// </summary>
        /// <param name="sqlstr">sql操作语句或存储过程名</param>
        /// <param name="s">数据操作方式</param>
        /// <param name="param">语句参数数组</param>
        /// <returns>受影响行数</returns>
        public int ExcuteNoneQuery(string sqlstr, CommandType s, params SqlParameter[] param)
        {
            try
            {
                con = SqlConn.CreaterConnection();
                cmd = new SqlCommand(sqlstr, con);
                cmd.CommandType = s;
                foreach (SqlParameter p in param)
                {
                    cmd.Parameters.Add(p);
                }
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return -1;
            }
            finally
            {
                con.Dispose();
            }
        }

        /// <summary>
        /// 返回查询的数据库记录集
        /// </summary>
        /// <param name="sqlname">sql查询语句或存储过程名</param>
        /// <param name="type">数据读取方式</param>
        /// <param name="param">语句参数数组</param>
        /// <returns>数据集</returns>
        public DataSet ExcuteDataSet(string sqlname, CommandType type, params SqlParameter[] param)
        {
            try
            {
                con = SqlConn.CreaterConnection();
                sda = new SqlDataAdapter(sqlname, con);
                sda.SelectCommand.CommandType = type;
                foreach (SqlParameter p in param)
                {
                    sda.SelectCommand.Parameters.Add(p);
                }
                ds = new DataSet();
                sda.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                con.Dispose();
            }
        }
    }
}

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

namespace DAL
{
 /// <summary>
 /// Create By Softeem
 /// 2010/1/21 17:36:36
 /// </summary>
 public class EmpmainDAL
 {
  const string TOTAL_FINDALL = "SELECT COUNT(*) FROM EmpMain";
  const string SQL_FINDALL = "SELECT * FROM EmpMain";
  const string SQL_FINDALL_WITH_PAGE = "SELECT TOP {0} * FROM EmpMain WHERE EmpMainId NOT IN (SELECT TOP {1} EmpMainId FROM EmpMain)";

  const string SQL_GET_BY_ID = "SELECT EmpMainPwd,EmpMainId FROM EmpMain WHERE EmpMainId=@EmpMainId";
  const string SQL_DELETE = "DELETE EmpMain WHERE EmpMainId=@EmpMainId";
  const string SQL_INSERT = "INSERT INTO EmpMain VALUES(@EmpMainPwd)";
  const string SQL_UPDATE = "UPDATE EmpMain SET EmpMainPwd=@EmpMainPwd WHERE EmpMainId=@EmpMainId";
  SqlHelper helper=new SqlHelper();

  /// <summary>
  /// 查询所有的记录
  /// </summary>
  /// <returns>查询后的记录集</returns>
  public DataSet FindAll()
  {
   return helper.ExcuteDataSet(SQL_FINDALL, CommandType.Text);
  }

  public int Insert(Empmain empmain)
  {
   SqlParameter[] param = new SqlParameter[1];
   param[0] = new SqlParameter("@EmpMainPwd",SqlDbType.VarChar);
   param[0].Value = empmain.Empmainpwd;
   return helper.ExcuteNoneQuery("EmpMain_Insert", CommandType.StoredProcedure,param);
  }

  public int Update(Empmain empmain)
  {
   //Empmain empmain = new Empmain();
   //empmain.Empmainpwd = ;
   //empmain.Empmainid = ;
   SqlParameter[] param = new SqlParameter[2];
   param[0] = new SqlParameter("@EmpMainPwd",SqlDbType.VarChar);
   param[0].Value = empmain.Empmainpwd;
   param[1] = new SqlParameter("@EmpMainId",SqlDbType.Int);
   param[1].Value = empmain.Empmainid;
   return helper.ExcuteNoneQuery(SQL_UPDATE, CommandType.Text,param);
  }

  public int Delete(int id)
  {
   SqlParameter[] param = {new SqlParameter("@EmpMainId", SqlDbType.Int)};
   param[0].Value = id;
   return helper.ExcuteNoneQuery(SQL_DELETE, CommandType.Text,param);
  }

  public Empmain GetById(int id)
  {
   Empmain empmain = new Empmain() ;
   SqlParameter param = new SqlParameter("@EmpMainId", SqlDbType.Int);
   param.Value = id;
   DataSet ds =helper.ExcuteDataSet(SQL_GET_BY_ID, CommandType.Text,param);
   object temp = null;
   temp = ds.Tables[0].Rows[0][0];
   empmain.Empmainpwd =(temp is DBNull) ? "": (string)temp;
   temp = ds.Tables[0].Rows[0][1];
   empmain.Empmainid =(temp is DBNull) ? 0: (int)temp;
   return empmain;
  }

        public DataSet GetLastId()
        {
            return helper.ExcuteDataSet("GetLastMainId", CommandType.StoredProcedure);
        }
 }
}