练习............
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中所有信息)<br />
<br />
以下是DataBase.cs的测试：<br />
<br />
<asp:LinkButton ID="LinkButton1" runat="server" OnClick="LinkButton2_Click">public bool GetRecord(string SqlString)方法</asp:LinkButton>
//检查数据库中是否有信息(符合sql语句)<br />
<asp:LinkButton ID="LinkButton3" runat="server" OnClick="LinkButton3_Click">public int GetRecordCount(string SqlString)方法</asp:LinkButton>
//返回数据条数(符合sql语句)<br />
<br />
<asp:LinkButton ID="LinkButton2" runat="server" OnClick="LinkButton4_Click">public DataSet GetDataSetBySql(string SqlString)</asp:LinkButton>
//根据sql语句参数，将信息绑定到gridview<br />
<asp:LinkButton ID="LinkButton7" runat="server" OnClick="LinkButton5_Click"> public DataSet AdvancedSearch(string TableName, Hashtable HT)方法</asp:LinkButton>
//任意hashtable，高级查询<br />
<br />
<br />
<asp:LinkButton ID="LinkButton5" runat="server" OnClick="LinkButton6_Click">public int RunProc(string ProcName)方法</asp:LinkButton>
//向数据库中进行插入操作，调用存储过程，，专门来执行存储过程Update,Insert<br />
<asp:LinkButton ID="LinkButton9" runat="server" OnClick="LinkButton7_Click"> public int RunProc(string ProcName, SqlParameter[] Params)</asp:LinkButton>
//带参数的存储过程，返回值类型为int，专门来执行存储过程，<br />
<asp:LinkButton ID="LinkButton10" runat="server" OnClick="LinkButton8_Click">public int RunProcGetCount(string ProcName, SqlParameter[] Params)</asp:LinkButton>//-----<span
style="background-color: #ff66ff">error！使用后注意调用SqlDataReader.Close()方法，，也删除了，就是出错<br />
</span>
<br />
<br />
<asp:LinkButton ID="LinkButton4" runat="server" OnClick="LinkButton9_Click"> public DataSet GetDataSet(string ProcName)</asp:LinkButton>//调用存储过程，显示数据<br />
<asp:LinkButton ID="LinkButton8" runat="server" OnClick="LinkButton10_Click"> public DataSet GetDataSet(string ProcName, SqlParameter[] Params)</asp:LinkButton>
//带参数的存储过程，返回值类型为DataSet<br />
<br />
<br />
</form>
</body>
</html>
DataBaseClassTest.aspx.cs
Codeusing 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
Codeusing 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:
CodeCREATE 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
