public class datasource
{
//声明对象
private string Connstring;
private Sqlconnection Sqlconn;
private Sqlcommand Sqlcmd;
private SqlDataAdapter SqlAdapter;
private DataSet ds;
private SqlDataReader SqlReader;
public class datasource() //构造函数
{
//初始化所有的实例
ConnString=ConfigurationManager.ConnectionStrings["pubsConn"].ConnectionString;//webconfig中调用的连接字符串
SqlConn=new SqlConnection(ConnString);
SqlCmd=new SqlCommand();
SqlAdapter=new SqlDataAdapter();
ds=new DataSet();
}
public DataSet TdataSet(string QueryString)//创建一个带参数的dataset方法
{
SqlAdapter.SelectCommand=new SqlCommand(QueryString,SqlConn);
SqlAdapter.Fill(ds,"temp");
return ds;
}
public DataSet TdataSet()//创建一个不带参数的dataset
{
SqlAdapter.SelectCommand=new SqlCommand("select au_id,Au_lnam from XXX");
SqlAdapter.Fill(ds,"temp");
return ds;
}
public SqlDataReader Tsdr()//返回一个只读,向前的数据datareader数据源.不支持服务端的分页,而且需要和数据源一直连接.
{
SqlCmd.CommandText="select au_id,Au_lnam from XXX";
SqlCmd.Connection=SqlConn;
SqlConn.Open();
SqlReader=SqlCmd.ExecuteReader(commandBehavior.CloseConnection);//当datareader关闭时,同时也关闭数据库的连接.
return SqlReader;
}
public void Update(string city,string au_lame)//更新表
{
SqlCmd.CommandText="update [表名] set [au_lname]='" + au_lname +"',[city]='" + city + "'"; //[]内为字段名
SqlCmd.Connection=SqlConn;
SqlConn.Open();
SqlCmd.ExecuteNonQuery();
}
public void Update(string city,string au_lame)//另一种方法更新表,可防止字段值含有单引号出错.
{
SqlCmd.CommandText="update [表名] set [au_lname]=@lname,[city]='" + city + "'"; //[]内为字段名
SqlCmd.Parameters.AddWithValue("@lname", au_lname);
SqlCmd.Connection=SqlConn;
SqlConn.Open();
SqlCmd.ExecuteNonQuery();
}
public Void Delete(string au_id)
{
SqlCmd.CommandText="Delete from [表名] where [au_id]='" + au_id +"' ";
Sqlcmd.connection=SqlConn;
Sqlconn.Open();
SqlCmd.ExecuteNonQuery();
}
}
----------------------------------------------------------------------------------------------
ExecuteNonQuery 执行sqlcommand命令,并返回受影响的行数(INT类型)
ExecuteReader 返回一个只读且只进的数据流,每次只返回一条数据(返回对象类型)
ExecuteScalar 执行查询,并返回查询所返回的结果集中第一行的第一列.忽略其他列或行.
----------------------------------------------------------------------------------------------
一个与GridView绑定的程序
protectde void Page_load(object sender, EventArgs e)
{
if(!IsPostBack)
Bind();
}
private void Bind()
{
SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionString["ConnStr"].connectionString);
SqlDataAdapter sda=new SqlDataAdapter("select * from name",con);
DataSet ds=new DataSet();
sda.Fill(ds,"temp");
con.Close();
GridView1.DataSource=ds.Tables["temp"].DefaultView;
Gridview1.DataBind();
}
private void fill(int id,string name, int age)
{
SqlConnection con=new Sqlconnection(ConfigurationManager.ConnectionString["ConnStr"].connectionString);
SqlDataAdapter sda=new SqlDataAdapter("select * from name",con);
SqlCommandBuilder scbld=new SqlCommandBuilder(sda);
DataSet ds=new DataSet();
try
{
sda.Fill(ds,"temp");
//设置内存表中以id字段排序
ds.Tables["temp"].DefaultView.Sort="id";
//找到我们要的数据所在行的索引
int index=ds.tables["temp"].DefaultView.Find(id);
//修改字段值
ds.Tables["temp"].Rows[index]["name"]=name;
ds.Tables["temp"].Rows[index]["age"]=age;
//得到此次更新影响的行数
int rows=sda.Update(ds,"temp");
Response.Write("成功更新了" + rows + "行数据");
}
catch(Exception e)
{
Response.Write("出现错误, 原因是" + e.Message);
}
finally
{
}
}
-----------------------------------------------------------------------------
DataSet的讲解
给DataSet内存表添加主键
UniqueConstraint uc=new UniqueConstraint("uthc",ds.Tables["user"].Columns["dc_id"],true);//true表示定义为主键,如果是false则不是主键
ds.Tables["user"].Constraints.Add(uc);
给DataSet内存表添加外键
ForeignKeyConstraint fkc=new ForeignKeyConstraint("fthc",ds.Tables["user"].Columns["dc_id"],ds.Tables["book"].Columns["dc_id"]);//["user"]为主表,["book"]为从表.
ds.Tables["book"].Constraints.Add(fkc);//将约束规则加到从表中
-------------------------------------------------------------------------
SqlDataAdapter的用法
1/
SqlDataAdapter da=new SqlDataAdapter("select * from authors", ConfigurationManager.ConnectionString["connstring"].ConnectionString);//注意,第二个参数是调用的数据库连接字符串,而不是Connection连接对象,这是因为在下面,SqlDataAdapter对象在调用Fill方法时会自动创建Connection连接对象,并打开连接.
DataSet ds=new DataSet();
da.Fill(ds,"table");
2/
SqlConnection conn=new SqlConnection(ConfigurationManager.ConnectionString["connstring"].ConnectionString);
SqlDataAdapter da=new SqlDataAdapter();
da.SelectCommand=new SqlCommand("select * from authors",conn);
DataSet ds=new DataSet();
da.Fill(ds,"table");
------------------------------------------------------------------------------------
外键关联
if (!IsPostBack)
{
Sqlconnection conn=new SqlConnection(ConfigurationManager.ConnectionStrings["pubsconn"].ConnectionString);
SqlDataAdapter sda=new SqlDataAdapter();
sda.SelectCommand=new SqlCommand("select title_id,title,type from titles",conn);//主表
DataSet ds=new DataSet();
sda.Fill(ds,"t1");
sda.SelectCommand=new SqlCommand("select stor_id,ord_num,title_id from sales",conn);//从表
sda.Fill(ds,"t2");
UnipueConstraint uc=new UniqueConstraint("t1_uc",ds.Tables["t1"].Columns["title_id"],true);//定义内存主表的主键约束
ds.Tables["t1"].Constraints.Add(uc);
ForeignKeyConstraint fkc=new ForeignKeyConstraint("fkc_au",ds.Tables["t1"].Columns["title_id"],ds.Tables["t2"].Columns["title_id"]);//定义主键外键关联
ds.Tables["t2"].Constraints.Add(fkc);
//或使用这种方法来定义外键关联也可以:
//ds.Relations.Add("fkc_au",ds.Tables["t1"].Columns["title_id"],ds.Tables["t2"].Columns["title_id"])
Session["ds"]=ds;//将ds赋给session对象
Response.Write(ds.Relations.Count);//在这个dataSet中有几个限制条件
GridView1.DataSource=ds.Tables["t1"];
GridView1.DataBind();
GridView2.DataSource=ds.Tables["t2"];
GridView2.DataBind();
}
//当按下GridView表中的删除键时发生的事件
protected void GridView1_RowDeleting(object sender,GridViewDeleteEventArgs e)
{
DataSet ds=(DataSet)Session["ds"];//将保存ds的session对象强制转换为dataset对象
//得到当前选中行的主键的值(前提是在GridView中设置DataKeyNames的属性值为主键名title_id)
string keys=GridView1.DataKeys[e.RowIndex].value.Tostring();
//找到对应主键值的当前行
DataRow dr=ds.Tables["t1"].Rows.Find(keys);
ds.Tables["t1"].Rows.Remove(dr);
GridView1.DataSource = ds.Tables["t1"];
GridView1.DataBind
GridView2.DataSource = ds.Tables["t2"];
GridView2.DataBind
}
--------------------------------------------------------------------------------
Merge方法,他能够把一个dataset, datatable,datarow等合并到一个dataset中去
如两个表合并前如下:
table1: table2:
title_id(设为主键) title type stor_id ord_num title_id
合并后为:
title_id title type stor_id ord_num
例:
if (!IsPostBack)
{
Sqlconnection conn=new SqlConnection(ConfigurationManager.ConnectionStrings["pubsconn"].ConnectionString);
SqlDataAdapter sda=new SqlDataAdapter();
sda.SelectCommand=new SqlCommand("select title_id,title,type from titles",conn);//主表
DataSet ds=new DataSet();
sda.Fill(ds,"t1");
DataSet ds=new DataSet();
sda.SelectCommand=new SqlCommand("select stor_id,ord_num,title_id from sales",conn);
sda.Fill(ds1,"t1");
UnipueConstraint uc=new UniqueConstraint("t1_uc",ds.Tables["t1"].Columns["title_id"],true);//定义内存主表的主键约束
ds.Tables["t1"].Constraints.Add(uc);
Session["ds"]=ds;
Session["ds1"]=ds1;
Response.Write(ds.Relations.Count);
GridView1.DataSource=ds.Tables["t1"];
GridView1.DataBind();
GridView2.DataSource=ds1.Tables["t1"];
GridView2.DataBind();
//合并按钮发生的事件:
DataSet ds=(DataSet)Session["ds"];
DataSet ds1=(DataSet)Session["ds1"];
ds.Merge(ds1);
GridView1.DataSource=ds.Tables["t1"];
GridView1.DataBind();