webCofig 中配置连接数据库
<connectionStrings>
<!-- 数据库连接-->
<add name="stz" connectionString="Data Source=.;Initial Catalog=MyBookShop;uid=sa;pwd=123;"/>
</connectionStrings>
//当用windows 登陆时
<add name="stz" connectionString="Data Source=.;Initial Catalog=MyBookShop;Integrated Security=SSPI;"/>
</connectionStrings>
连接数据库用的DBHelp
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace MyBookShop.DAL
{
public static class DBHelper
{
static string connectionString = ConfigurationManager.ConnectionStrings["stz"].ConnectionString ;
public static int ExecuteCommand(string sql, params SqlParameter[] values)
{
SqlConnection Connection = new SqlConnection(connectionString);
Connection.Open();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = cmd.ExecuteNonQuery();
Connection.Close();
return result;
}
public static int GetScalar(params SqlParameter[] values)
{
SqlConnection Connection = new SqlConnection(connectionString);
Connection.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = Connection;
cmd.CommandText = "Pro_InsertOrder";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
Connection.Close();
return result;
}
public static int GetScalar(string sql, params SqlParameter[] values)
{
SqlConnection Connection = new SqlConnection(connectionString);
Connection.Open();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
int result = Convert.ToInt32(cmd.ExecuteScalar());
Connection.Close();
return result;
}
//返回DataReader
public static SqlDataReader GetReader(string sql, params SqlParameter[] values)
{
SqlConnection Connection = new SqlConnection(connectionString);
Connection.Open();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return reader;
}
//返回dataTatble
public static DataTable GetDataSet(string sql, params SqlParameter[] values)
{
SqlConnection Connection = new SqlConnection(connectionString);
DataSet ds = new DataSet();
SqlCommand cmd = new SqlCommand(sql, Connection);
cmd.Parameters.AddRange(values);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
return ds.Tables[0];
}
}
}
---当调用返回来的是Reader时
public static List<User> GetAllUser(string sql)
{
List<User> list=new List<User> ();
SqlDataReader reader= DBHelper.GetReader(sql);
while (reader.Read())
{
User user = new User();
user.Address = reader["address"].ToString();
user.Id = int.Parse(reader["id"].ToString());
user.LoginId=reader ["loginId"].ToString();
user.LoginPwd=reader ["loginPwd"].ToString();
user.Mail=reader ["Mail"].ToString();
user.Name=reader ["Name"].ToString();
user.Phone=reader ["phone"].ToString();
user.UserRoleId =reader["userRoleId"].ToString();
user.UserStateId=reader ["userStateId"].ToString ();
user.UserState = UserStateServer.GetStateById(reader ["userStateId"].ToString ());
user.UserRole = UserRoleServer.GetUserRoleById(reader["userRoleId"].ToString());
list.Add(user);
}
reader.Close();
return list;
}
---------当调用的是返回DataTable时
private static IList<User> GetUsersBySql(string safeSql)
{
List<User> list = new List<User>();
using (DataTable table = DBHelper.GetDataSet(safeSql))
{
foreach (DataRow row in table.Rows)
{
User user = new User();
user.Id = (int)row["Id"];
user.LoginId = (string)row["LoginId"];
user.LoginPwd = (string)row["LoginPwd"];
user.Name = (string)row["Name"];
user.Address = (string)row["Address"];
user.Phone = (string)row["Phone"];
user.Mail = (string)row["Mail"];
user.UserState = UserStateService.GetUserStateById((int)row["UserStateId"]); //FK
user.UserRole = UserRoleService.GetUserRoleById((int)row["UserRoleId"]); //FK
list.Add(user);
}
return list;
}
}