asp.net 前台调用后操作数据库方法

 

//(一)创建数据库存储过程脚本

create proc myProc
as
begin
BEGIN TRANSACTION

--select id,date1 from Date1 where id=2
insert into Date1(date1)values(GETDATE())
SELECT @@ERROR -1

end

 

//(二)SqlHelper

 

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

namespace personSys
{
    public static class SqlHelper
    {
        //准备一个连接字符串
        private static string connStr = ConfigurationManager.ConnectionStrings["myconnStr"].ConnectionString;

        /// <summary>
        /// (1)执行一个存储过程得到一个DataTable对象
        /// </summary>
        /// <param name="Procname"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable ExecuteProc(string Procname, params SqlParameter[] paras)
        {
            SqlConnection con = new SqlConnection(SqlHelper.connStr);
            SqlDataAdapter da = new SqlDataAdapter(Procname, con);
            da.SelectCommand.Parameters.AddRange(paras);

            da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置使用存储过程
            DataTable table = new DataTable();

            da.Fill(table);//把得到的DataTable对象存放到适配器中
            return table;//返回得到的DataTable对象

        }

        /// <summary>
        /// (2)执行一个没有参数的存储过程得到一个DataTable对象
        /// </summary>
        /// <param name="Procname"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable ExecuteProcNoQuery(string Procname)
        {
            SqlConnection con = new SqlConnection(SqlHelper.connStr);
            SqlDataAdapter da = new SqlDataAdapter(Procname, con);
            //da.SelectCommand.Parameters.AddRange(paras);

            da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置使用存储过程
            DataTable table = new DataTable();

            da.Fill(table);//把得到的DataTable对象存放到适配器中
            return table;//返回得到的DataTable对象

        }

 

        /// <summary>
        /// (3)执行一个没有参数的存储过程得到一个int返回值
        /// (之所以用返回值是int类型是为了在执行增删改的时候做个判断)
        /// </summary>
        /// <param name="Procname"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int ExecuteProcNoQueryInt(string Procname)
        {
            SqlConnection con = new SqlConnection(SqlHelper.connStr);
            SqlDataAdapter da = new SqlDataAdapter(Procname, con);
            //da.SelectCommand.Parameters.AddRange(paras);

            da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置使用存储过程
            DataTable table = new DataTable();

            da.Fill(table);//把得到的DataTable对象存放到适配器中
            return table.Rows.Count;//返回得到的DataTable中数据行数

        }

 

        /// <summary>
        /// (4)执行一个有参数的存储过程得到一个int返回值
        /// (之所以用返回值是int类型是为了在执行增删改的时候做个判断)
        /// </summary>
        /// <param name="Procname"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int ExecuteProcQueryInt(string Procname, params SqlParameter[] paras)
        {
            SqlConnection con = new SqlConnection(SqlHelper.connStr);
            SqlDataAdapter da = new SqlDataAdapter(Procname, con);
            da.SelectCommand.Parameters.AddRange(paras);

            da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置使用存储过程
            DataTable table = new DataTable();

            da.Fill(table);//把得到的DataTable对象存放到适配器中
            return table.Rows.Count;//返回得到的DataTable中数据行数

        }

 

 

        /// <summary>
        /// (5)如果从数据库中取到的数据是NULL的时候，则转化为C#中的null类型
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static object FromDbValue(object value)
        {
            if (value == DBNull.Value)
            {
                return null;
            }
            else
            {
                return value;
            }
        }

 

        /// <summary>
        /// (6)如果数据为Null的话，则把数据转化为数据库中的DBNULL类型
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        public static object ToDbValue(object value)
        {
            if (value == null)
            {
                return DBNull.Value;
            }
            else
            {
                return value;
            }
        }

 

        //(7)执行增删改操作
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
            }
        }

       
        /// <summary>
        /// (8)执行带参数的查询操作
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteScalar();
                }
            }
        }

 

        /// <summary>
        /// (9)执行不带参数的查询操作
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    //cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteScalar();
                }
            }
        }

 

        //(10)返回一个DataTable的方法
        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    DataSet dataset = new DataSet();
                    SqlDataAdapter apdater = new SqlDataAdapter(cmd);
                    apdater.Fill(dataset);
                    return  dataset.Tables[0];
                }
            }
        }

 

        /// <summary>
        /// (13)执行存储过程，返回一个数据表
        /// </summary>
        /// <param name="procName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static DataTable ExcuteProctest(string procName, params SqlParameter[] paras)
        {
            SqlConnection conn = new SqlConnection(SqlHelper.connStr);
            SqlDataAdapter da = new SqlDataAdapter(procName, conn);
            da.SelectCommand.Parameters.AddRange(paras);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;//设置 使用存储过程

            DataTable dt = new DataTable();
            //查询数据，并将返回的数据装入数据表dt
            da.Fill(dt);
            return dt;
        }

    }
}

 

//(三)后台代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;

namespace personSys
{
    public partial class 前台取后台调用数据库的方法 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        //前台调用的公共方法
        public int getValue()
        {
            DataTable table = SqlHelper.ExecuteProcNoQuery("myProc");
            return Convert.ToInt32(table.Rows[0][0].ToString());
        }

    }
}

 

//(四)前台JS调用的示例代码

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="前台取后台调用数据库的方法.aspx.cs" Inherits="personSys.前台取后台调用数据库的方法" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>

    <script type="text/javascript">
        function myClick() {
          var num=<%= getValue() %>;
          document.getElementById("testTxt").value=num;
        }

   
    </script>

</head>
<body>
    <form id="form1" runat="server">
    <div>
   
    <input type="button" value="点击" id="btnClick" οnclick="myClick()" />
    <br />
    <input  type="text" id="testTxt" />

    </div>
    </form>
</body>
</html>