asp.net建立保持连接的数据库连接的步骤

来源:百度文库 编辑:神马文学网 时间:2021/01/19 02:58:58
1.建立数据库连接对象
SqlConnection conn = new SqlConnection();2.设定连接字符串
conn.ConnectionString = "Data Source=PC-200909161907\\SQLEXPRESS;Initial Catalog=Northwind;User ID=sa;Password=123456";
3.打开数据库连接
conn.Open();4.建立数据库命令对象
SqlCommand comm = new SqlCommand();5.设定数据库命令字符串
第一种方法:字符串
comm.CommandText = "select EmployeeID as 员工编号,LastName+' '+FirstName as 姓名,Title as 职务,Address as 地址,City as 城市 from Employees where City=@city";
第二种方法:存储过程
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "GetEmployeesByCity";
//
 /*
   存储过程在数据库中创建
   create procedure GetEmployeesByCity
   @city nvarchar(15)=null
   as
   select EmployeeID as 员工编号,LastName +' '+ FirstName as 姓名,
   Title as 职务,Address as 地址,City as 城市 from Employees where City=@city
 */6.设置参数
SqlParameter parmCity = new SqlParameter("@city", SqlDbType.NVarChar, 15);
parmCity.Direction = ParameterDirection.Input;
parmCity.Value = txtCity.Text;
comm.Parameters.Add(parmCity);7.将数据库命令对象和数据库连接对象关联
comm.Connection = conn;8.建立数据库读取器对象
SqlDataReader data;9.执行数据库命令
data = comm.ExecuteReader();10.显示数据库执行命令结果
lblDisplayResult1.Text =data.GetValue(0).ToString();
lblDisplayResult2.Text =data.GetValue(1).ToString();
lblDisplayResult3.Text =data.GetValue(2).ToString();
lblDisplayResult4.Text =data.GetValue(3).ToString();
lblDisplayResult5.Text =data.GetValue(4).ToString();11.关闭数据库读取器对象
data.Close();12.关闭数据库连接对象并释放资源
conn.Close();
conn.Dispose(); 
注:
(1)执行数据操作的字符串命令
comm.CommandText = "update Employees set Country='UK' where Country='英国'";
int rows = comm.ExecuteNonQuery();
(1)返回单个值
comm.CommandText = "select AVG(UnitPrice) from Products as 平均价格 where SupplierID=(select SupplierID from Suppliers where CompanyName='Ma Maison')";
float avgPrice = float.Parse(comm.ExecuteScalar().ToString());