【學習筆記】ASP.NET簡易的SQLHelper幫助類

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即可。

使用者感覺刪除了，但是資料庫還在，防止誤刪和以後維護的問題！