C# 增删改查应用（一）

 

DBbase类：

    public class DBbase
    {
        //读取配置文件，连接数据库语句
        public static string strCon = System.Configuration.ConfigurationManager.ConnectionStrings["Family"].ConnectionString;

        //实例化连接对象 con  
        SqlConnection con = new SqlConnection(strCon);

        //检测连接是否打开  
        public void Connection()
        {
            if (this.con.State == ConnectionState.Closed)
            {
                this.con.Open();
            }
        }

        //根据提供的strSQL语句 访问数据库，返回List集合
        public List<User> GetDataSet(string strSQL)
        {
            Connection();
            try
            {
                SqlDataAdapter da = new SqlDataAdapter(strSQL, con);
                DataSet ds = new DataSet();
                da.Fill(ds);
                List<User> list = new List<User>();
                if (ds.Tables.Count > 0)
                {
                    for (int i = 0; i < ds.Tables.Count; i++)
                    {
                        foreach (DataRow dr in ds.Tables[i].Rows)
                        {
                            User obj = new User();
                            if (ds.Tables[i].Columns.Contains("UserID")) obj.UserID = Convert.ToString(dr["UserID"]);
                            if (ds.Tables[i].Columns.Contains("UserName")) obj.UserName = Convert.ToString(dr["UserName"]);
                            if (ds.Tables[i].Columns.Contains("UserSet")) obj.UserSet = Convert.ToString(dr["UserSet"]);
                            if (ds.Tables[i].Columns.Contains("Userphone")) obj.Userphone = Convert.ToString(dr["Userphone"]);
                            if (ds.Tables[i].Columns.Contains("UserworkType")) obj.UserworkType = Convert.ToString(dr["UserworkType"]);
                            if (ds.Tables[i].Columns.Contains("WorkDetailsSituation")) obj.WorkDetailsSituation = Convert.ToString(dr["WorkDetailsSituation"]);
                            if (ds.Tables[i].Columns.Contains("UserleverlType")) obj.UserleverlType = Convert.ToString(dr["UserleverlType"]);
                            if (ds.Tables[i].Columns.Contains("UserType")) obj.UserType = Convert.ToString(dr["UserType"]);
                            if (ds.Tables[i].Columns.Contains("UserCreationtime")) obj.UserCreationtime = Convert.ToDateTime(dr["UserCreationtime"]);
                            if (ds.Tables[i].Columns.Contains("Userhobby")) obj.Userhobby = Convert.ToString(dr["Userhobby"]);
                            list.Add(obj);
                        }
                    }
                }
                return list;
            }
            catch (Exception)
            {

                throw;
            }
        }







    }

调用：

        //----------------------------------------DBbase类调用---------------------------------
        //实例化一个DBbase对象
        static DBbase db = new DBbase();

        //查询用户数据 
        public static List<User> User()
        {
            //通过实体中的属性访问 拼接一个你需要的SQL语句
            StringBuilder strSQL = new StringBuilder();
            strSQL.Append("Select Theserialnumber, UserID, UserName, UserSet, Userphone, work.UserworkType,Details.WorkDetailsSituation,[level].UserleverlType,[type].UserType, UserCreationtime, hobby.Userhobby from [User]  ");
            strSQL.Append("inner join Work on Work.UserworkID=[User].UserworkID  ");
            strSQL.Append("inner join [level] on [level].UserlevelID=[user].UserlevelID  ");
            strSQL.Append("inner join UserType as [type] on [type].UserTypeID=[USER].UserTypeID  ");
            strSQL.Append("inner join WorkDetails as Details on Details.WorkDetailsID=Work.WorkDetailsID  ");
            strSQL.Append("inner join Userhobby as hobby on hobby.UserhobbyID=[user].UserhobbyID");
            return db.GetDataSet(strSQL.ToString());
        }