public void UpdateSql(string username, string userpwd,string id)
{
string constr = "";
SqlConnection cnn = new SqlConnection(constr);
cnn.Open();
string sql = " update InsertToSql set username = @username ,password = @userpwd where id = @id ";
SqlCommand cmd = new SqlCommand(sql, cnn);
SqlParameter par1 = new SqlParameter("@username", username);
cmd.Parameters.Add(par1);
SqlParameter par2 = new SqlParameter("@userpwd",userpwd);
cmd.Parameters.Add(par2);
SqlParameter par3 = new SqlParameter("@id",id);
cmd.Parameters.Add(par3);
//int result = cmd.ExecuteNonQuery();
cmd.ExecuteNonQuery();
cnn.Close();
cmd.Dispose();
}
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
public int CpInsertToSql(string hpnum,string fxr) {
string constr = ConfigurationManager.ConnectionStrings["cnnstr"].ConnectionString;
SqlConnection cnn = new SqlConnection(constr);
string sql = " insert into 错票信息 (货票号, 发现人) values (@hpnum,@fxr)";
SqlCommand cmd = new SqlCommand(sql, cnn);
try
{
cnn.Open();
SqlParameter par1 = new SqlParameter("@hpnum", hpnum);
cmd.Parameters.Add(par1);
SqlParameter par8 = new SqlParameter("@fxr", fxr);
cmd.Parameters.Add(par8);
int result = cmd.ExecuteNonQuery();
return result;
}
catch (Exception e)
{
throw e;
}
finally
{
cnn.Close();
cmd.Dispose();
}
}
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
public void UpdateSql(string username, string userpwd,string id)
{
string constr = "server = server;Initial Catalog = db ;User Id = sa ; Pwd = ******";
SqlConnection cnn = new SqlConnection(constr);
cnn.Open();
string sql = " update InsertToSql set username = @username ,password = @userpwd where id = @id ";
SqlCommand cmd = new SqlCommand(sql, cnn);
SqlParameter par1 = new SqlParameter("@username", username);
cmd.Parameters.Add(par1);
SqlParameter par2 = new SqlParameter("@userpwd",userpwd);
cmd.Parameters.Add(par2);
SqlParameter par3 = new SqlParameter("@id",id);
cmd.Parameters.Add(par3);
//int result = cmd.ExecuteNonQuery();
cmd.ExecuteNonQuery();
cnn.Close();
cmd.Dispose();
//return result;
}
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/// DataReader
在应用程序需要以下功能时使用DataReader:
l 不需要缓冲数据。
2 正在处理的结果集太大而不能全部放入内存中。
3 需要迅速一次性访问数据,采用只向前的只读的方式
4、需要数据库连接一直连接,不能断开
string sql = "select * from OT_User where ObjectID = @userid";
SqlConnection Connection = new SqlConnection("server = server;Initial Catalog = db ;User Id = sa ; Pwd = ******");
SqlCommand command = new SqlCommand(sql, Connection);
SqlParameter param = new SqlParameter("userid", userid);
command.Parameters.Add(param);
SqlDataReader read = null;
try
{
if (Connection.State != ConnectionState.Open)
{
Connection.Open();
}
read = command.ExecuteReader();
if (read.Read())
{
string Appellation = read["Appellation"].ToString();
}
}
catch (Exception ex)
{
}
finally
{
read.Close();
if (Connection.State != ConnectionState.Closed)
{
Connection.Close();
}
}
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
using(SqlConnection conn = new SqlConnection("server = server;Initial Catalog = db ;User Id = sa ; Pwd = ******")){
conn.Open();
using(SqlCommand cmd = conn.CreateCommand()){
cmd.CommandText ="select * from Users";
using(SqlDataReader read = cmd.ExcuteReader()){
while(read.read()){
string username =read.GetString(read.GetOrdinal("UserName"));
}
}
}
}
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
///DataSet
使用DataSet是为了实现应用程序的下述功能:
l 操作结果中的多个分离的表。
2 操作来自多个源(例如来自多个数据库、XML文件和电子表格的混合数据)的数据。
3 在层之间交换数据或使用XML Web服务。与DataReader 不同,DataSet能被传递到远程客户端。
4 通过缓冲重复使用相同的行集合以提高性能(例如排序、搜索或过滤数据)。
5 每行执行大量的处理。在使用DataReader返回的行上进行扩展处理将使连接存在的时间比必要的更长,从而降低效率。
6使用XML操作(例如XSLT转换和Xpath查询)维护数据。
string conn = ConfigurationManager.ConnectionStrings["cnnstr"].ConnectionString;
using(SqlConnection conn = new SqlConnection(conn))
{
conn.Open();
using(SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from Users";
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
DataTable tab = ds.Table[0];
for(int i =0 ; i< tab.Rows.Count;i++)
{
DataRow row = tab.Rows[i];
string name =Convert.ToString(row["Name"]);
}
}
}
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
//DataAdapter 对数据的更新,删除和新建
string conn = ConfigurationManager.ConnectionStrings["cnnstr"].ConnectionString;
using(SqlConnection conn = new SqlConnection(conn))
{
conn.Open();
using(SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from Users";
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
DataTable tab = ds.Table[0];
DataRow row = tab.Rows[0];
//下面的三个操作都只是对本地内存中的数据集ds进行操作,数据库服务器还没有更新
row["Name"] ="jason"; //更新数据
tab.Rows.RemoveAt(1); //删除
DataRow dr = tab.NewRow();//新建
//新建一个builder,将更改同步到数据库服务器
SqlCommandBuilder builder = new SqlCommandBuilder(da);
da.Update(ds);
}
}
}