1.Connection 对象
web.config配置
<
connectionStrings
>
< add name ="connectionString" providerName ="System.Data.SqlClient" connectionString ="Data Source=.;database=cx_data;Persist Security Info=True;User ID=sa;Password=123123;" />
</ connectionStrings >
< add name ="connectionString" providerName ="System.Data.SqlClient" connectionString ="Data Source=.;database=cx_data;Persist Security Info=True;User ID=sa;Password=123123;" />
</ connectionStrings >
调用:
SqlConnection con
=
new
SqlConnection();
con.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings[ " connectionString " ].ConnectionString;
con.Open();
con.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings[ " connectionString " ].ConnectionString;
con.Open();
2.Command对象
string
sql
=
string
.Format(
"
insert into Test (username,userpwd) values('{0}','{1}')
"
,
this
.txtusername.Text,
this
.txtpwd.Text);
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = System.Data.CommandType.Text;
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = System.Data.CommandType.Text;
3.DataReader对象
示例1
SqlConnection con
=
new
SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings[
"
connectionString
"
].ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand( " select * from Members " , con);
try
{
SqlDataReader dr = cmd.ExecuteReader();
this .GridView1.DataSource = dr;
this .GridView1.DataBind();
}
catch (Exception ex)
{
this .lblinfo.Text = ex.Message;
}
finally
{
con.Close();
}
con.Open();
SqlCommand cmd = new SqlCommand( " select * from Members " , con);
try
{
SqlDataReader dr = cmd.ExecuteReader();
this .GridView1.DataSource = dr;
this .GridView1.DataBind();
}
catch (Exception ex)
{
this .lblinfo.Text = ex.Message;
}
finally
{
con.Close();
}
示例2
SqlConnection con
=
new
SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings[
"
connectionString
"
].ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand( " select * from Members " , con);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
this .TextBox1.Text += dr[ 5 ].ToString() + " \t " + dr[ " user_account " ].ToString() + " \t " + dr.GetString( 6 ).ToString() + " \r\n " ;
}
con.Open();
SqlCommand cmd = new SqlCommand( " select * from Members " , con);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
this .TextBox1.Text += dr[ 5 ].ToString() + " \t " + dr[ " user_account " ].ToString() + " \t " + dr.GetString( 6 ).ToString() + " \r\n " ;
}
4.DataAdapter对象
SqlDataAdapter da
=
new
SqlDataAdapter(
"
select * from Members
"
, System.Web.Configuration.WebConfigurationManager.ConnectionStrings[
"
connectionString
"
].ConnectionString);
DataSet ds = new DataSet();
da.Fill(ds, " 学生信息 " );
this .GridView1.DataSource = ds.Tables[ 0 ];
this .GridView1.DataBind();
DataSet ds = new DataSet();
da.Fill(ds, " 学生信息 " );
this .GridView1.DataSource = ds.Tables[ 0 ];
this .GridView1.DataBind();
5.参数
//
创建数据库连接
SqlConnection con = new SqlConnection();
con.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings[ " connectionString " ].ConnectionString;
con.Open();
// 创建Command
string sql = " update test set username=@username where userpwd=@userpwd " ;
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = System.Data.CommandType.Text;
SqlParameter spname = new SqlParameter();
spname.ParameterName = " @username " ;
spname.DbType = DbType.String;
spname.SqlDbType = SqlDbType.NVarChar;
spname.Size = 255 ;
spname.Value = this .txtusername.Text;
cmd.Parameters.Add(spname); // 方式1
cmd.Parameters.Add( " @userpwd " , SqlDbType.NVarChar).Value = this .txtpwd.Text; // 方式2
int count = cmd.ExecuteNonQuery();
con.Close();
this .lblinfo.Text = " 操作成功! " ;
SqlConnection con = new SqlConnection();
con.ConnectionString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings[ " connectionString " ].ConnectionString;
con.Open();
// 创建Command
string sql = " update test set username=@username where userpwd=@userpwd " ;
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = System.Data.CommandType.Text;
SqlParameter spname = new SqlParameter();
spname.ParameterName = " @username " ;
spname.DbType = DbType.String;
spname.SqlDbType = SqlDbType.NVarChar;
spname.Size = 255 ;
spname.Value = this .txtusername.Text;
cmd.Parameters.Add(spname); // 方式1
cmd.Parameters.Add( " @userpwd " , SqlDbType.NVarChar).Value = this .txtpwd.Text; // 方式2
int count = cmd.ExecuteNonQuery();
con.Close();
this .lblinfo.Text = " 操作成功! " ;