1.在web.config中配置
<configuration>
<appSettings>
<add key="ConnectionString" value="server=192.168.1.101;database=aa;UId=sa;password='920503'"/>
</appSettings>
</configuration>
2.获取配置文件中与数据库连接的字符串
public static SqlConnection GetConnection() {
string mystr = ConfigurationManager.AppSettings["ConnectionString"].ToString();
SqlConnection myConn = new SqlConnection(mystr);
if (myConn.State == ConnectionState.Closed)
{
myConn.Open();
}
// myConn.Open();(每次调用GetConnection方法时做判断,如果关闭则打开)
return myConn;
}
使用Command对象操作数据库:
使用Connection对象与数据源建立连接后,可使用Command对象对数据源执行查询、添加、删除和修改等各种操作。
Command对象常用方法:
ExecuteNonQuery:执行SQL语句并返回受影响的行数
ExecuteReader:执行返回数据集的Select语句
ExecuteScalar:执行查询,并返回查询所返回的结果集中第一行的第一列
protect void btnSelect_Click(object sender, EventArgs e)
{
SqlConnection myConn = GetConnection();
string sql = "select * from table where name = @name";
SqlCommand cmd = new SqlCommand(sql, myConn );
cmd.Parameters.Add("@name",SqlDbType.VarChar,20).Value = this.txtName.Text.Trim();
SqlDataAdapter myDa = new SqlDataAdapter(cmd );
DataSet myDs = new DataSet();
myDa .Fill(myDs );//通过SqlDataAdapter 对象填充DataSet 对象
if(myDs .Table[0].Rows.Count>0)
{
相关操作
}
myDa.Dispose();
myDs .Dispose();
myConn .Dispose();
}
向数据库中添加记录时,调用SqlCommand对象的ExecuteNonQuery方法执行记录的添加操作
protect void btnAdd_Click(object sender, EventArgs e)
{
SqlConnection myConn = GetConnection();
string sql = "insert into table(name) value ('"+this.txtClass.Text.Trim()+"')";
SqlCommand cmd = new SqlCommand(sql, myConn );
cmd.ExecuteNonQuery();
myConn .Dispose();
使用DataReader对象读取数据
调用Command对象的ExecuteReader方法将返回DataReader对象:
SqlConnection myConn = GetConnection();
string sql = "select * from table where name = @name";
SqlCommand cmd = new SqlCommand(sql, myConn );
SqlDataReader sdr = cmd.ExecuteReader();
通过sdr["列名"]取值