七皇弟,乖乖上榻.txt_:ASP.NET中SqlClient的应用(C#)

说实话我感觉怎么表达出来也不如看MSDN来得到位。

http://msdn2.microsoft.com/zh-cn/library/system.data.sqlclient.sqldataadapter.aspx

当然完整的案例呢，你可以看看
http://lovecherry.cnblogs.com/archive/2006/07/02/440840.html
这个是翻译的asp.net上的文章，还在工作中

以下是我自己的代码,截几段，自己看
public class DataBase
{
private string strConn;
private SqlConnection con;

//取得数据库连接
public DataBase(string type) //type 指定连到哪个数据库 {
strConn = ConfigurationSettings.AppSettings[type];
con=new SqlConnection(strConn);
}
public int Open()
{
try
{
con.Open();
return 0;
}
catch(System.Data.SqlClient.SqlException e)
{
con.Close();
throw new Exception(e.Message);
}
finally
{
}
}
public int Close()
{
try
{
con.Close();
return 0;
}
catch(System.Data.SqlClient.SqlException e)
{
con.Close();
throw new Exception(e.Message);
}
finally
{
}
}
//根据SqlCommand得到reader--需要先Open() 读完后再Close()
public SqlDataReader ExecuteCmd4Reader(SqlCommand cmd)
{
cmd.Connection=con;
try
{
SqlDataReader sdr=cmd.ExecuteReader();
return sdr;
}
catch(System.Data.SqlClient.SqlException e)
{
cmd.Dispose();
con.Close();
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
}

}

//
public int ExecuteCmdNonQuery(SqlCommand cmd)
{
//SqlConnection con = new SqlConnection(strConn);
cmd.Connection=con;
try
{
con.Open();

return cmd.ExecuteNonQuery();
}
catch(System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
con.Close();
}
}
//执行SqlCommand得到结果集
public DataSet ExecuteCmd4DataSet(SqlCommand cmd)
{
SqlConnection con = new SqlConnection(strConn);
cmd.Connection=con;
try
{
con.Open();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
catch(System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);

}
finally
{
cmd.Dispose();
con.Close();
}
}
//执行SqlCommand得到唯一值
public object ExecuteCmdScalar(SqlCommand cmd)
{
SqlConnection con = new SqlConnection(strConn);
cmd.Connection=con;
try
{
con.Open();

return cmd.ExecuteScalar();;
}
catch(System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);

}
finally
{
cmd.Dispose();
con.Close();
}
}

//执行几个语句组成的事务
public int ExecuteCmds(SqlCommand[] cmds)
{
SqlConnection con = new SqlConnection(strConn);

try
{
con.Open();
}
catch(System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
SqlTransaction trans = con.BeginTransaction();

try
{
foreach(SqlCommand cmd in cmds)
{
cmd.Connection = con;
cmd.Transaction = trans;

cmd.ExecuteNonQuery();
}
trans.Commit();
return 0;
}
catch(System.Data.SqlClient.SqlException e)
{
trans.Rollback();
throw new Exception(e.Message);
}
finally
{
con.Close();
}
}

//执行数据库操纵语句
public int ExecuteNonQuery(string strSQL)
{
SqlConnection con = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand(strSQL,con);
try
{
con.Open();
cmd.ExecuteNonQuery();
return 0;
}
catch(System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
con.Close();
}
}

//执行几个语句组成的事务
public int ExecuteSqls(string[] strSQLs)
{
SqlConnection con = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand();
//int j=strSQLs.Length;

try
{
con.Open();
}
catch(System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);
}
SqlTransaction trans = con.BeginTransaction();

try
{
cmd.Connection = con;
cmd.Transaction = trans;

foreach(string str in strSQLs)
{
cmd.CommandText = str;
cmd.ExecuteNonQuery();
}
trans.Commit();
return 0;
}
catch(System.Data.SqlClient.SqlException e)
{
trans.Rollback();
throw new Exception(e.Message);
}
finally
{
cmd.Dispose();
con.Close();
}
}

//执行查询语句得到结果集
public DataSet Execute4DataSet(string strSQL)
{
SqlConnection con = new SqlConnection(strConn);
try
{
con.Open();
SqlDataAdapter sda = new SqlDataAdapter(strSQL,con);
DataSet ds = new DataSet();
sda.Fill(ds);
return ds;
}
catch(System.Data.SqlClient.SqlException e)
{
throw new Exception(e.Message);

}
finally
{
con.Close();
}
}

}

///////////////////////////////////////////////////
使用：
DataBase db=new DataBase("....连接串");
string sql="select last_login,last_login_fail from admin where uid=@uid";
SqlCommand cmd=new SqlCommand(sql);
cmd.Parameters.Add("@uid",SqlDbType.Int).Value=Session["xxx"];
db.Open();
SqlDataReader sdr=db.ExecuteCmd4Reader(cmd);
if(sdr.Read())
{
this.lbl_lastlogin.Text=sdr.GetSqlDateTime(0).ToString();
this.lbl_lastfail.Text=sdr.GetSqlDateTime(1).ToString();
}
db.Close();

///////////////////////////////////////////
DataBase db=new DataBase("....连接串");
string sql;

int t;
t=page_size*(cur_page-1);
sql="查询语句"
+" and order_id< all (select top "+t.ToString()+" order_id from orders where uid=@uid order by order_id desc) order by order_id desc";

SqlCommand cmd=new SqlCommand(sql);
cmd.Parameters.Add("@uid",SqlDbType.Int).Value=Session["xxx"];
DataSet ds=db.ExecuteCmd4DataSet(cmd);
this.dgOrders.DataSource=ds;
this.dgOrders.DataBind();

/////////////////////
DataBase db=new DataBase("ConHouse");
string sql="update cor_user set page=@page where uid=@uid";
SqlCommand cmd=new SqlCommand(sql);
cmd.Parameters.Add("@page",SqlDbType.VarChar,32).Value=this.ddlTemplate.SelectedItem.Value;
cmd.Parameters.Add("@uid",SqlDbType.Int).Value=Session["xxx"];
db.ExecuteCmdNonQuery(cmd);

///////////////////
DataBase db=new DataBase("ConHouse");
SqlCommand[] cmds=new SqlCommand[2];
cmds[0]=new SqlCommand("delete from cor_color_template where uid=@uid");
cmds[0].Parameters.Add("@uid",SqlDbType.Int).Value=Session["xxx"];
cmds[1]=new SqlCommand("insert into cor_color_template values(@uid,@color_template)");
cmds[1].Parameters.Add("@uid",SqlDbType.Int).Value=Session["xxx"];
cmds[1].Parameters.Add("@color_template",SqlDbType.VarChar,32).Value=this.ddlTemplate.SelectedValue;
db.ExecuteCmds(cmds);
Response.Write("<script language=JavaScript>alert('颜色模板设置成功！');</script>");