这是一本书上的,但是我调好了没问题。
用的数据库是SQL2000自带的pubs库。
在App_Code下新建类文件AuthorDB.cs,内容如下:
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;
/// <summary>
/// AuthorDB の概要の説明です
/// </summary>
public class AuthorDB
{
public AuthorDB()
{
//
// TODO: コンストラクタ ロジックをここに追加します
//
}
//获取state集合,返回DataSet,并通过DropDownList显示
public static DataSet GetStates()
{
//获取连接字符串
string connectionString = ConfigurationManager.ConnectionStrings["PubsConnectionString"].ConnectionString;
//创建并设置SqlConnection
SqlConnection dbConnection = new SqlConnection(connectionString);
//定义SQL查询语句
string queryString = "Select distinct state from authors";
//创建并设置SqlCommand
SqlCommand dbCommand = new SqlCommand();
dbCommand.Connection = dbConnection;
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = queryString;
//创建SqlDataAdapter,并获取数据
SqlDataAdapter dataAdapter = new SqlDataAdapter(dbCommand);
DataSet ds = new DataSet();
dataAdapter.Fill(ds);
//返回数据
return ds;
}
//获取state参数,获取数据记录,返回DataSet,返回DataSet,并通过GridView显示
public static DataSet GetAuthorsByState(string state)
{
//获取连接字符串
string connectionString = ConfigurationManager.ConnectionStrings["PubsConnectionString"].ConnectionString;
//创建并设置SqlConnection
SqlConnection dbConnection = new SqlConnection(connectionString);
//定义SQL查询语句
string queryString = "Select au_id,au_fname,au_lname,state from authors where state=@state";
//创建并设置SqlCommand
SqlCommand dbCommand = new SqlCommand();
dbCommand.Connection = dbConnection;
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = queryString;
//设置SqlParameter
SqlParameter dbParameter_state = new SqlParameter();
dbParameter_state.ParameterName = "@state";
dbParameter_state.Value = state;
dbParameter_state.DbType = DbType.StringFixedLength;
//向SqlCommand中添加SqlParameter
dbCommand.Parameters.Add(dbParameter_state);
//创建SqlDataAdapter,并获取数据
SqlDataAdapter dataAdapter = new SqlDataAdapter(dbCommand);
DataSet ds = new DataSet();
dataAdapter.Fill(ds);
//返回数据
return ds;
}
//更新数据记录
public static int UpdateAuthor(string au_id,string au_lname,string au_fname,string state)
{
//获取连接字符串
string connectionString = ConfigurationManager.ConnectionStrings["PubsConnectionString"].ConnectionString;
//创建并设置SqlConnection
SqlConnection dbConnection = new SqlConnection(connectionString);
//定义SQL查询语句
string queryString = "UPDATE authors SET au_fname=@au_fname,au_lname=@au_lname,state=@state WHERE au_id = @au_id";
//创建并设置SqlCommand
SqlCommand dbCommand = new SqlCommand();
dbCommand.Connection = dbConnection;
dbCommand.CommandType = CommandType.Text;
dbCommand.CommandText = queryString;
//设置参数@au_id
SqlParameter dbParameter_au_id = new SqlParameter();
dbParameter_au_id.ParameterName = "@au_id";
dbParameter_au_id.Value = au_id;
dbParameter_au_id.DbType = DbType.String;
//向SqlCommand中添加@au_id
dbCommand.Parameters.Add(dbParameter_au_id);
//设置参数@au_lname
SqlParameter dbParameter_au_lname = new SqlParameter();
dbParameter_au_lname.ParameterName = "@au_lname";
dbParameter_au_lname.Value = au_lname;
dbParameter_au_lname.DbType = DbType.String;
//向SqlCommand中添加@au_lname
dbCommand.Parameters.Add(dbParameter_au_lname);
//设置参数@au_fname
SqlParameter dbParameter_au_fname = new SqlParameter();
dbParameter_au_fname.ParameterName = "@au_fname";
dbParameter_au_fname.Value = au_fname;
dbParameter_au_fname.DbType = DbType.String;
//向SqlCommand中添加@au_fname
dbCommand.Parameters.Add(dbParameter_au_fname);
//设置参数@state
SqlParameter dbParameter_state = new SqlParameter();
dbParameter_state.ParameterName = "@state";
dbParameter_state.Value = state;
dbParameter_state.DbType = DbType.StringFixedLength;
//向SqlCommand中添加@state
dbCommand.Parameters.Add(dbParameter_state);
//执行SQL语句,并且返回受影响的行数
int rowsAffected = 0;
dbConnection.Open();
try
{
rowsAffected = dbCommand.ExecuteNonQuery();
}
finally
{
dbConnection.Close();
}
return rowsAffected;
}
}
然后在配置文件web.config中加上连接程序就可以了。
最后当然是在表示层用objectdatasourse组件连接了,绑定在服务器组件上就可以运行了。
写的比较匆忙,如有问题请留言,一块解决。