C# DbHelper常用方法

一： 查询单个字段

DBUtility.DbHelperSQLP dbHelper = new DBUtility.DbHelperSQLP(PubConstant.GetDragonSourceConnectionString);
            SqlParameter[] paras = {
                                        new SqlParameter("@titleid",SqlDbType.VarChar)
                                    };
            paras[0].Value = titleid;
            SqlDataReader reader = dbHelper.ExecuteReader("select TitleIDNew=cast([ChannelId] as varchar)+'/'+cast([Id] as varchar),TitleID from [XueShuMedia].[dbo].[siteserver_20230918_1] where TitleID=@titleid;", paras);
            string result = reader != null && reader.Read() ? reader["TitleIDNew"].ToString() : string.Empty;
            reader.Close();
            return result;

二：添加

DBUtility.DbHelperSQLP dbHelper = new DBUtility.DbHelperSQLP(PubConstant.GetDragonSourceConnectionString);
            SqlParameter[] paras = {
                                        new SqlParameter("@comboid",SqlDbType.Int),
                                        new SqlParameter("@userguid",SqlDbType.VarChar),
                                         new SqlParameter("@endDate",SqlDbType.VarChar)
                                    };
            paras[0].Value = Convert.ToInt32(comboid);
            paras[1].Value = userguid;
            paras[2].Value = endDate;
            int reader = dbHelper.ExecuteSql("INSERT INTO UserPackageRelation VALUES(@comboid,@userguid,getdate(),@endDate,getdate(),getdate(),0);", paras);
            return reader;

三：修改

DBUtility.DbHelperSQLP dbHelper = new DBUtility.DbHelperSQLP(PubConstant.GetDragonSourceConnectionString);
            SqlParameter[] paras = {
                                        new SqlParameter("@endDate",SqlDbType.VarChar),
                                        new SqlParameter("@UserPackageRelationid",SqlDbType.Int)
                                    };
            paras[0].Value = endDate;
            paras[1].Value =Convert.ToInt32(packageid);
            int reader = dbHelper.ExecuteSql("UPDATE UserPackageRelation SET enddate=@endDate WHERE UserPackageRelationid=@UserPackageRelationid;", paras);
            return reader;  

四：执行存储过程

DBUtility.DbHelperSQLP helper = new DBUtility.DbHelperSQLP(DBUtility.PubConstant.GetDragonSourceConnectionString);
            helper.ExecuteSql("P_AUTO_UpdateMagazineLatestIssue");

五：查询总数

DBUtility.DbHelperSQLP dbHelpers = new DBUtility.DbHelperSQLP(PubConstant.GetDragonSourceConnectionString);
            SqlParameter[] paras = {
                                    
                                          new SqlParameter("@userGuid",SqlDbType.VarChar)
                                    };
            paras[0].Value = userGuid;
            int  reader = Convert.ToInt32(dbHelpers.GetSingle("select count(*) from User_LoginToken where userCode=@userGuid", paras));        
            return reader;

六：删除

DBUtility.DbHelperSQLP dbHelpers = new DBUtility.DbHelperSQLP(PubConstant.GetDragonSourceConnectionString);
            SqlParameter[] paras = {
                                          new SqlParameter("@userToken",SqlDbType.VarChar),
                                          new SqlParameter("@userGuid",SqlDbType.VarChar)
                                    };
            paras[0].Value = userToken;
            paras[1].Value = userGuid;
           dbHelpers.ExecuteSql(" delete from User_LoginToken where userCode=@userGuid and userToken=@userToken", paras);

七：执行多条，事务控制

DBUtility.DbHelperSQLP dbHelper = new DBUtility.DbHelperSQLP(PubConstant.GetDragonSourceConnectionString);
            SqlParameter[] paras = {
                                        new SqlParameter("@orderid",orderid)
                                    };
            string sql1 = "delete from User_Order_ReadingCard where orderid=@orderid";
            string sql2 = "delete from User_Order_Journal where orderid=@orderid";
            string sql3 = "delete from User_OrderList where orderid=@orderid";
            System.Collections.Hashtable hash = new System.Collections.Hashtable();
            hash.Add(sql1, paras);
            hash.Add(sql2, paras);
            hash.Add(sql3, paras);
            dbHelper.ExecuteSqlTran(hash);

八：查询单条数据，并获取指定字段

DBUtility.DbHelperSQLP dbHelper = new DBUtility.DbHelperSQLP(PubConstant.GetDragonSourceConnectionString);
            SqlParameter[] paras = {
                                        new SqlParameter("@email",SqlDbType.VarChar)
                                    };
            paras[0].Value = email;
            SqlDataReader reader = dbHelper.ExecuteReader("select top 1 Mobile,BindMobile   from User_AccessControl as a where Email=@email and  create_date<'2019-1-1' and create_date>'2015-1-1' and (select count(*) from ActiveResponseUser where ActivePhone=Mobile)=0 and (select count(*) from ActiveResponseUser where ActivePhone=BindMobile)=0 ", paras);
            string result = "";
            if (reader != null && reader.Read())
            {
                result = reader["Mobile"].ToString() != null && reader["Mobile"].ToString() != "" ? reader["Mobile"].ToString() : reader["BindMobile"].ToString();
            }
            reader.Close();
            return result;

九：查询列

SqlParameter[] parms ={new SqlParameter("@unit",unit.Text),
                                     new SqlParameter("@price",price.Text),
                                     new SqlParameter("@validity",validity.Text),
                                     new SqlParameter("@Num",Num.Text),
                                     new SqlParameter("@Types",Types.Text),
                                     new SqlParameter("@packageID",PackageID.Text)};
            DbHelperSQLP DBHelper = new DbHelperSQLP(ConfigurationManager.ConnectionStrings["Ds_Database1"].ToString());
            DataSet ds = DBHelper.Query("select userguid,resourceguid,resourceid,createdate from [dbo].[UserCollection] where resourcetype=3 and sitename='dy.qikan.com' order by createdate", parms);

十：Dataset转List

// 使用LINQ语句查询数据
var query = from row in dataSet.Tables[0].AsEnumerable()
            select new
            {
                Column1 = row.Field<int>("Column1"),
                Column2 = row.Field<string>("Column2"),
                // ...
            };

// 将查询结果转换为List
List<object> list = query.ToList();

// 创建List对象
List<object> list = new List<object>();

// 遍历数据表和行
foreach (DataTable table in dataSet.Tables)
{
    foreach (DataRow row in table.Rows)
    {
        // 创建对象，将行数据赋值给对象的属性
        object obj = new object();
        obj.Column1 = row["Column1"];
        obj.Column2 = row["Column2"];
        // ...

        // 将对象添加到List中
        list.Add(obj);
    }
}