连接sql 数据库

来源：互联网发布：想在淘宝买情趣用品编辑：程序博客网时间：2024/06/05 02:11

webCofig 中配置连接数据库

//当用windows 登陆时

连接数据库用的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;
            }
        }