SqlHelper.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

namespace LingJuDAL

{

public class SqlHelper

{

    //定义只读静态连接字符串变量



    public static readonly string connStr = ConfigurationManager.ConnectionStrings["LingJuDBConn"].ConnectionString;



    //ExecuteNonQurey 



    public static int ExecuteNonQuery(CommandType cmdType, string sqlCmdText, params SqlParameter[] parameters)

    {

        using (SqlConnection conn = new SqlConnection(connStr))

        {

            conn.Open();

            using (SqlCommand cmd = conn.CreateCommand())

            {

                cmd.CommandType = cmdType;

                cmd.CommandText = sqlCmdText;

                cmd.Parameters.Clear();

                cmd.Parameters.AddRange(parameters);



                return cmd.ExecuteNonQuery();

            }  

        }

    }



    //ExecuteScalar



    public static object ExecuteScalar(CommandType cmdType, string sqlCmdText, params SqlParameter[] parameters)

    {

        using (SqlConnection conn = new SqlConnection(connStr))

        {

            conn.Open();

            using (SqlCommand cmd = conn.CreateCommand())

            {

                cmd.CommandType = cmdType;

                cmd.CommandText = sqlCmdText;

                cmd.Parameters.Clear();

                cmd.Parameters.AddRange(parameters);



                return cmd.ExecuteScalar();

            }

        }



    }



    //DataTable

    public static DataTable ExecuteDataTable(CommandType cmdType, string sqlCmdText, params SqlParameter[] parameters)

    {

        using (SqlConnection conn = new SqlConnection(connStr))

        {

            conn.Open();



            using (SqlCommand cmd = conn.CreateCommand())

            {

                cmd.CommandType = cmdType;

                cmd.CommandText = sqlCmdText;

                cmd.Parameters.Clear();

                cmd.Parameters.AddRange(parameters);

                SqlDataAdapter adapter = new SqlDataAdapter(cmd);

                DataSet ds = new DataSet();

                adapter.Fill(ds);

                return ds.Tables[0];

            }

        }



    }



    //DataSet

    public static DataSet GetDataSet(CommandType cmdType, string sqlCmdText, params SqlParameter[] parameters)

    {

        using (SqlConnection conn = new SqlConnection(connStr))

        {

            conn.Open();



            using (SqlCommand cmd = new SqlCommand())

            {

                cmd.CommandType = cmdType;

                cmd.CommandText = sqlCmdText;

                cmd.Parameters.AddRange(parameters);



                using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))

                {

                    DataSet ds = new DataSet();

                    try

                    {

                        adapter.Fill(ds);

                        cmd.Parameters.Clear();

                    }

                    catch (System.Data.SqlClient.SqlException ex)

                    {

                        conn.Close();

                        throw new Exception(ex.Message);

                    }

                    return ds;

                }

            }

        }

    }

}

}

DAL层Admin.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.SqlClient;

using System.Data;

namespace LingJuDAL

{

public class LJAdmin

{

    //登录



    public bool AdminLogin(LingJuModel.AdminModel admin)

    {             //使用的是存储过程

        int flag= (int)SqlHelper.ExecuteScalar(System.Data.CommandType.StoredProcedure, "LingJuAdminSelect",  

                                   new SqlParameter("@adminName", admin.adminName),

                                   new SqlParameter("@password", admin.password));



        if (flag == 1)

        {

            return true;

        }

        else

        {

            return false;

        }

    }



    //删除管理员(软删除)



    public void DeleteAdminById(long id)

    {

        SqlHelper.ExecuteNonQuery(CommandType.Text, "updata Lj_admin set lj_isdel=1 where id=@id",

                                    new SqlParameter("@id", id));

    }



}

}

存储过程:LingJuAdminSelect.sql

USE [LingJuSchoolDB]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[LingJuAdminSelect]

@lj_admin_name nvarchar(20),

@lj_password nvarchar(20)

AS

BEGIN

SET NOCOUNT ON;

SELECT COUNT(*) FROM lj_admin

WHERE lj_admin_name=@lj_admin_name AND lj_password=@lj_password AND lj_isdel='False' AND lj_islock='False';

END

//实现软删除的SQL存储过程

//软删除实质是在表中加入一个标志字段,记录是否被“删除”,如果用户想删除用户,那isDel=ture,

//登录时候查询条件写上isDel=false即可。

用户感觉删除了,但是数据库还在,防止误删和以后维护的问题!