练习............

最新推荐文章于 2024-05-26 21:07:50 发布

dianlixiao2608

最新推荐文章于 2024-05-26 21:07:50 发布

阅读量57

点赞数

文章标签：数据库 xhtml ui

原文链接：http://www.cnblogs.com/jianlinglo/archive/2009/01/15/1376371.html

版权

DataBaseClassTest.aspx

Code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DataBaseClassTest.aspx.cs" Inherits="DataBaseClassTest" %>

<!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>
</head>
<body>
 <form id="form1" runat="server">
 <asp:GridView ID="GridView1" runat="server">
 </asp:GridView>
 <hr />
 <asp:LinkButton ID="LinkButton6" runat="server" OnClick="LinkButton1_Click">调用存储过程</asp:LinkButton> //一个简单的例子(查找students中所有信息) 
 
 以下是DataBase.cs的测试： 
 
 <asp:LinkButton ID="LinkButton1" runat="server" OnClick="LinkButton2_Click">public bool GetRecord(string SqlString)方法</asp:LinkButton>
    //检查数据库中是否有信息(符合sql语句) 
 <asp:LinkButton ID="LinkButton3" runat="server" OnClick="LinkButton3_Click">public int GetRecordCount(string SqlString)方法</asp:LinkButton>
   //返回数据条数(符合sql语句) 
 
 <asp:LinkButton ID="LinkButton2" runat="server" OnClick="LinkButton4_Click">public DataSet GetDataSetBySql(string SqlString)</asp:LinkButton>
 //根据sql语句参数，将信息绑定到gridview 

 <asp:LinkButton ID="LinkButton7" runat="server" OnClick="LinkButton5_Click"> public DataSet AdvancedSearch(string TableName, Hashtable HT)方法</asp:LinkButton>
   //任意hashtable，高级查询 
 
    
 <asp:LinkButton ID="LinkButton5" runat="server" OnClick="LinkButton6_Click">public int RunProc(string ProcName)方法</asp:LinkButton>
     //向数据库中进行插入操作，调用存储过程，，专门来执行存储过程Update,Insert 
 <asp:LinkButton ID="LinkButton9" runat="server" OnClick="LinkButton7_Click"> public int RunProc(string ProcName, SqlParameter[] Params)</asp:LinkButton>
   //带参数的存储过程，返回值类型为int，专门来执行存储过程， 
 <asp:LinkButton ID="LinkButton10" runat="server" OnClick="LinkButton8_Click">public int RunProcGetCount(string ProcName, SqlParameter[] Params)</asp:LinkButton>//-----error！使用后注意调用SqlDataReader.Close()方法，，也删除了，就是出错 
 
 
 
 <asp:LinkButton ID="LinkButton4" runat="server" OnClick="LinkButton9_Click"> public DataSet GetDataSet(string ProcName)</asp:LinkButton>//调用存储过程，显示数据 
 <asp:LinkButton ID="LinkButton8" runat="server" OnClick="LinkButton10_Click"> public DataSet GetDataSet(string ProcName, SqlParameter[] Params)</asp:LinkButton>
  //带参数的存储过程，返回值类型为DataSet 
 
 
  
 </form>
</body>
</html>

DataBaseClassTest.aspx.cs

Code
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using chinafanbu.DataAccessLayer;
using System.Data.SqlClient;
public partial class DataBaseClassTest : System.Web.UI.Page
{
    DataBase b = new DataBase();
    protected void Page_Load(object sender, EventArgs e)
    {
    }
    //调用存储过程的一个例子
    protected void LinkButton1_Click(object sender, EventArgs e)
    {
        SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=test;Integrated Security=True;User Id=sa;Password=sa");
        SqlCommand cmd = new SqlCommand("test123", con);
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            Response.Write(dr["name"]);//成功
        }
        con.Close();
    }

    //bool 查看数据表中是否有数据
    protected void LinkButton2_Click(object sender, EventArgs e)
    {
        string a = "select * from students";

        bool boo = b.GetRecord(a);
        Response.Write(boo);
    }
    //int 返回数据的条数
    protected void LinkButton3_Click(object sender, EventArgs e)
    {
        string a = "select count(*) from students";
        int ad = b.GetRecordCount(a);
        Response.Write(ad);
    }
    //DataSet,根据sql返回DataSet
    protected void LinkButton4_Click(object sender, EventArgs e)
    {
        string a = "select top 3 * from students";
        DataSet dsDataSet = b.GetDataSetBySql(a);
        GridView1.DataSource = dsDataSet;
        GridView1.DataBind();
    }
    //运用Hashtable表，高级查询
    protected void LinkButton5_Click(object sender, EventArgs e)
    {
        Hashtable ht = new Hashtable(); //创建一个Hashtable实例

        string asdf = "age";
        string lkj = "12";
        string asdf1 = "num";
        string lkj1 = "1";

        ht.Add(asdf, lkj);//添加key/value键值对
        ht.Add(asdf1, lkj1);//添加key/value键值对key/value键值对
        DataSet dsDataSet = b.AdvancedSearch("students", ht);
        GridView1.DataSource = dsDataSet;
        GridView1.DataBind();
    }

    //int RunProc  对Update,Inset,Delete 操作返回影响到的行数，其他情况为-1；
    protected void LinkButton6_Click(object sender, EventArgs e)
    {
        int asd = b.RunProc("testcharu");//插入成功了
        Response.Write(asd);
    }
    //int RunProc  对Update,Inset,Delete 操作返回影响到的行数，其他情况为-1；
    protected void LinkButton7_Click(object sender, EventArgs e)
    {
        string age = "100";
        SqlParameter[] parameters ={ new SqlParameter("@age ", SqlDbType.Char, 10) };
        parameters[0].Value = age;
        int ad = b.RunProc("test9", parameters);
        Response.Write(ad);
    }
    //int RunProcGetCount,,,返回查询第一行信息。注意使用后注意调用SqlDataReader.Close()方法
    protected void LinkButton8_Click(object sender, EventArgs e)
    {
        string age = "100";
        SqlParameter[] parameters ={ new SqlParameter("@age ", SqlDbType.Char, 10) };
        parameters[0].Value = age;
        int ad = b.RunProcGetCount("test9", parameters);
        Response.Write(ad);
    }
    //DataSet
    protected void LinkButton9_Click(object sender, EventArgs e)
    {
        DataSet dsDataSet = b.GetDataSet("test123");
        GridView1.DataSource = dsDataSet;
        GridView1.DataBind();
    }
    //DataSet
    protected void LinkButton10_Click(object sender, EventArgs e)
    {
        string name = "李四";
        SqlParameter[] parameters ={new   SqlParameter( "@name ",   SqlDbType.Char,10)
        };
        parameters[0].Value = name;
        DataSet dsDataSet = b.GetDataSet("test2", parameters);
        GridView1.DataSource = dsDataSet;
        GridView1.DataBind();

    }

}

DataBase.c

Code
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Collections;

namespace chinafanbu.DataAccessLayer
{
 /// <summary>
 /// 数据库接口类
 /// </summary>
 public class DataBase
 {
 //私有变量，数据库连接
 protected SqlConnection Connection;
 protected string ConnectionString;

 //构造函数
 public DataBase()
 {
 ConnectionString = ConfigurationManager.ConnectionStrings["SqlConnectionStrings"].ConnectionString;
 }

 //保护方法，打开数据库连接
 private void Open()
 {
 //判断数据库连接是否存在
 if (Connection == null)
 {
 //不存在，新建并打开
 Connection = new SqlConnection(ConnectionString);
 Connection.Open();
 }
 else
 {
 //存在，判断是否处于关闭状态 如果处于关闭状态，重新打开
 if (Connection.State.Equals(ConnectionState.Closed))
 Connection.Open();
 }
 }

 //公有方法，关闭数据库连接
 public void Close()
 {
 if (Connection.State.Equals(ConnectionState.Open))
 {
 Connection.Close(); //连接处于打开状态，关闭连接
 }
 }

 /// <summary>
 /// 析构函数，释放非托管资源
 /// </summary>
 ~DataBase()
 {
 try
 {
 if (Connection != null)
 Connection.Close();
 }
 catch
 {

 }
 try
 {
 Dispose();
 }
 catch
 {

 }
 }

 /// <summary>
 /// 公有方法，释放资源
 /// </summary>
 public void Dispose()
 {
 if (Connection != null) //确保连接被关闭
 {
 Connection.Dispose();
 Connection = null;
 }
 }

 //根据Sql语句，返回是否查询到数据
 public bool GetRecord(string SqlString)
 {
 Open();
 SqlDataAdapter adapter = new SqlDataAdapter(SqlString,Connection );
 DataSet dataset = new DataSet();
 adapter.Fill(dataset );
 Close();

 if (dataset.Tables[0].Rows.Count > 0)
 {
 return true;
 }
 else
 {
 return false;
 }
 }

 //根据Sql语句，得到数据库表中数据的条数
 public int GetRecordCount(string SqlString)
 {
 string SCount;

 Open();
 SqlCommand cmd = new SqlCommand(SqlString, Connection);
 cmd.CommandTimeout = 80000;
 SCount = cmd.ExecuteScalar().ToString().Trim();
 if (SCount == "")
 {
 SCount = "0";
 }
 Close();
 return Convert.ToInt32(SCount);
 }

 //根据Sql语句,得到DataSet数据集
 public DataSet GetDataSetBySql(string SqlString)
 {
 Open();
 SqlDataAdapter adapter = new SqlDataAdapter(SqlString ,Connection );
 DataSet dataset = new DataSet();
 adapter.Fill(dataset );
 Close();

 return dataset;
 }

 //运用Hashtable实现高级检索，返回DataSet数据集
 public DataSet AdvancedSearch(string TableName, Hashtable HT)
 {
 int Count = 0;

 string Fields = "";
 foreach (DictionaryEntry Item in HT)
 {
 if (Count != 0)
 {
 Fields += " and ";
 }
 Fields += Item.Key.ToString();
 Fields += " like '%";
 Fields += Item.Value.ToString();
 Fields += "%'";
 Count++;
 }
 Fields += " ";

 string SqlString = "select * from " + TableName + " where " + Fields;
 Open();
 SqlDataAdapter adapter = new SqlDataAdapter(SqlString,Connection );
 DataSet dataset = new DataSet();
 adapter.Fill(dataset );
 Close();

 return dataset;

 }

 /// <summary>
 /// 公有方法，调用存储过程（不带参数）
 /// </summary>
 /// <param name="ProcName">存储过程名</param>
 /// <returns>对Update,Inset,Delete 操作返回影响到的行数，其他情况为-1；</returns>
 public int RunProc(string ProcName)
 {
 int Count = -1;
 SqlCommand cmd = CreateCommand(ProcName,null );
 cmd.CommandTimeout = 80000;
 Count = cmd.ExecuteNonQuery();
 Close();

 return Count;
 }

 /// <summary>
 /// 公有方法，调用存储过程（带参数）
 /// </summary>
 /// <param name="ProcName">存储过程名</param>
 /// <param name="Params">用来调用存储过程的参数表</param>
 /// <returns>对Update,Inset,Delete 操作返回影响到的行数，其他情况为-1；</returns>
 public int RunProc(string ProcName, SqlParameter[] Params)
 {
 int Count = -1;

 SqlCommand cmd = CreateCommand(ProcName,Params );
 cmd.CommandTimeout = 80000;
 Count = cmd.ExecuteNonQuery();
 Close();
 return Count;
 }

 /// <summary>
 /// 公有方法，调用存储过程（带参数）
 /// </summary>
 /// <param name="ProcName">存储过程名</param>
 /// <param name="Params">存储过程需要的参数</param>
 /// <returns>返回查询第一行信息。注意使用后注意调用SqlDataReader.Close()方法</returns>
 public int RunProcGetCount(string ProcName, SqlParameter[] Params)
 {
 SqlCommand cmd = CreateCommand(ProcName, Params);
 cmd.CommandTimeout = 80000;
 string SCount;
 SCount = cmd.ExecuteScalar().ToString().Trim();
 if (SCount == "")
 SCount = "0";
 Close();
 return Convert.ToInt32(SCount);
 }

 /// <summary>
 /// 公有方法，调用存储过程（不带参数）
 /// </summary>
 /// <param name="ProcName">存储过程名</param>
 /// <returns>将执行结果以DataSet返回</returns>
 public DataSet GetDataSet(string ProcName)
 {
 Open();
 SqlDataAdapter adapter = new SqlDataAdapter(ProcName, Connection);
 DataSet dataset = new DataSet();
 adapter.Fill(dataset);
 Close();
 return dataset;
 }

 /// <summary>
 /// 公有方法，调用存储过程(带参数)
 /// </summary>
 /// <param name="ProcName">存储过程名</param>
 /// <param name="Params">将执行结果以DataSet对象返回</param>
 /// <returns></returns>
 public DataSet GetDataSet(string ProcName, SqlParameter[] Params)
 {
 Open();
 SqlCommand cmd = CreateCommand(ProcName, Params);
 cmd.CommandTimeout = 80000;
 SqlDataAdapter adapter = new SqlDataAdapter(cmd);
 DataSet dataset = new DataSet();
 adapter.Fill(dataset);
 Close();
 return dataset;
 }

 //-----------xianmanasdsadf--------------------------------------------------------------------------------------------------

 /// <summary>
 /// 私有方法，获取一个用来调用存储过程的SqlCommand对象
 /// </summary>
 /// <param name="ProcName">存储过程名</param>asdfaasdf
 /// <param name="Prams">用来调用存储过程的参数表</param>
 /// <returns>返回SqlCommand对象</returns>
 private SqlCommand CreateCommand(string ProcName, SqlParameter[] Prams)
 {
 Open();
 SqlCommand cmd = new SqlCommand(ProcName, Connection);
 cmd.CommandTimeout = 80000;
 cmd.CommandType = CommandType.StoredProcedure;

 if (Prams != null)
 {
 foreach (SqlParameter Parameter in Prams)
 {
 cmd.Parameters.Add(Parameter);
 }
 }

 return cmd;
 }

 /// <summary>
 /// 公有方法，实例化一个用于调用存储过程的参数
 /// </summary>
 /// <param name="ParamName">参数名称</param>
 /// <param name="DbType">参数类型</param>
 /// <param name="Size">参数大小</param>
 /// <param name="Direction">传递方向</param>
 /// <param name="Value">值</param>
 /// <returns>返回一个SqlParameter</returns>
 public SqlParameter MakeParam(string ParamName, SqlDbType DbType, Int32 Size, ParameterDirection Direction, object Value)
 {
 SqlParameter Param;

 if (Size > 0)
 Param = new SqlParameter(ParamName, DbType, Size);
 else
 Param = new SqlParameter(ParamName, DbType);

 Param.Direction = Direction;

 if (Value != null)
 Param.Value = Value;

 return Param;
 }

 /// <summary>
 /// 公有方法，实例化一个用于调用存储过程的输入参数
 /// </summary>
 /// <param name="ParamName">参数名称</param>
 /// <param name="DbType">参数类型</param>
 /// <param name="Size">参数大小</param>
 /// <param name="Value">参数值</param>
 /// <returns>返回SqlParameter</returns>
 public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, int Size, object Value)
 {
 return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value);
 }

 public SqlDataReader GetReaderBySql(string strsql)
 {
 Open();
 SqlCommand cmd = new SqlCommand(strsql, Connection);
 cmd.CommandTimeout = 80000;
 cmd.CommandType = CommandType.Text;
 SqlDataReader dr = cmd.ExecuteReader();
 return dr;
 }

 /// <summary>
 /// 公有方法，调用存储过程（不带参数）
 /// </summary>
 /// <param name="ProcName">存储过程名</param>
 /// <returns>将执行结果以SqlDataReader返回。使用后注意调用SqlDataReader.Close()方法</returns>
 public SqlDataReader RunProcGetReader(string ProcName)
 {
 SqlCommand cmd = CreateCommand(ProcName,null );
 cmd.CommandTimeout = 80000;
 return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection );
 }

 /// <summary>
 /// 公有方法，调用存储过程（带参数）
 /// </summary>
 /// <param name="ProcName">存储过程名</param>
 /// <param name="Params">参数</param>
 /// <returns>将执行结果以SqlDataReader返回。使用后注意调用SqlDataReader.Close()方法</returns>
 public SqlDataReader RunProcGetReader(string ProcName, SqlParameter[] Params)
 {
 SqlCommand cmd = CreateCommand(ProcName,Params );
 cmd.CommandTimeout = 80000;
 return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection );
 }
 }
}

Web.Config

Code
<?xml version="1.0"?>
<configuration>
 <connectionStrings>
 <add name="SqlConnectionStrings" connectionString="Data Source=(local);Initial Catalog=test;Integrated Security=True" providerName="System.Data.SqlClient"/>
 <add name="AccessConnectionStrings" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\test.mdb;Persist Security Info=True" providerName="System.Data.OleDb"/>
 </connectionStrings>
 <system.web>
 <compilation debug="true">
 <assemblies>
 <add assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
 <add assembly="CrystalDecisions.Shared, Version=10.2.3600.0, Culture=neutral, PublicKeyToken=692FBEA5521E1304"/>
 <add assembly="Microsoft.Office.Tools.Word, Version=8.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A"/></assemblies></compilation>
 <authentication mode="Windows"/>
 </system.web>
 <location>
 <appSettings>
 <add key="SqlAppSettings" value="Data Source=(local);Initial Catalog=test;Integrated Security=True;User Id=sa;Password=sa"/>
 <add key="AccessAppSettings" value="provider=Microsoft.Jet.OleDb.4.0;Data Source=|DataDirectory|\test.mdb;"/>
 </appSettings>
 </location>
</configuration>

jigeshagua pro:

Code
CREATE PROCEDURE [dbo].[test123] AS
Select top 3 * from students;
GO

CREATE PROCEDURE [dbo].[test2]
@name char(10)
AS
select * from students where name=@name;
GO

CREATE PROCEDURE [dbo].[test9]
@age char(10)
AS
update students set age=@age where name='成龙';
GO

CREATE PROCEDURE [dbo].[testcharu] AS
INSERT INTO students VALUES ('churu','张三','5','3');
GO

转载于:https://www.cnblogs.com/jianlinglo/archive/2009/01/15/1376371.html

dianlixiao2608

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
练习............

DataBaseClassTest.aspxCode<%@PageLanguage="C#"AutoEventWireup="true"CodeFile="DataBaseClassTest.aspx.cs"Inherits="DataBaseClassTest"%><!DOCTYPEhtmlPUBLIC"-//W3C//DTDXHTML1.0...
复制链接

扫一扫