您的当前位置: 源码爱好者 » 网页制作教程(编程教程) » C#技术教程

C#文章管理中的数据库操作类

  • 标签:数据库操作类 更新时间:2014-02-19
  • 这个是我从一个文章管理程序中整理出来的C#数据库操作类,觉得里面的功能挺实用,注释也非常多,对使用者有会很方便。这个类可以删除信息类别的同时,遍历其下分的各个信息子类,同时删除信息子类的数据源和采集周期,根据信息类别编号,获取所有的信息类别对应的数据源编号DataSourceIds,递归获取某类信息的所有子类,根据数据源编号获取该数据源配置信息,删除该采集周期信息,根据编号删除详细配置信息等。

    using System;
    using System.Collections.Generic;
    using System.Text;
    using System.Data;
    using System.Data.OleDb;
    using DataEntity;
    using System.IO;
    using System.Net;
    namespace DataFactory
    {
        public class PageCommon
        {
            public PageCommon()
            { }
            #region 变量定义
            /// <summary>
            /// 数据库操作类
            /// </summary>
            DataFactory.DataOperater Run = new DataOperater();
            #region 实体类引用
            //private Gather_Article Article = new Gather_Article();
            //private Gather_BasicConfig BasicConfig = new Gather_BasicConfig();
            private Gather_DataSourceConfig DataSourceConfig = new Gather_DataSourceConfig();
            //private Gather_Message Message = new Gather_Message();
            //private Gather_OperationKind OperationKind = new Gather_OperationKind();
            private Gather_ParticularConfig ParticularConfig = new Gather_ParticularConfig();
            //private Gather_Period Period = new Gather_Period();
            #endregion
            #endregion 
            #region 信息分类配置函数
            public int AddMessage(Gather_Message entity1)  // 新增
            {
                StringBuilder builder = new StringBuilder();
                builder.Append("INSERT INTO Gather_Message (MgPId,MgName,DataSourceIds,OrderId,Remark) Values (");
                builder.Append(entity1.MgPId + ",");
                builder.Append("'" + entity1.MgName.Replace("'","''") + "',");
                builder.Append("'" + entity1.DataSourceIds.Replace("'","''") + "',");
                builder.Append(entity1.OrderId + ",");
                builder.Append("'" + entity1.Remark.Replace("'","''")  + "'");
                builder.Append(")");
                try
                {
                    return (Run.RunSql_Int(builder.ToString()));
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message, ex);
                }
            }
            //说明: 右键点击信息分类父节点时,弹出窗体,增加信息分类。
            private bool Update(Gather_Message entity) // 修改
            {
                StringBuilder builder = new StringBuilder();
                builder.Append("Update Gather_Message Set MgPId=" + entity.MgPId);
                builder.Append(",MgName='" + entity.MgName.Replace("'","''")  + "'");
                builder.Append(",DataSourceIds='" + entity.DataSourceIds.Replace("'","''")  + "'");
                builder.Append(",OrderId=" + entity.OrderId);
                builder.Append(",Remark='" + entity.Remark.Replace("'","''")  + "'");
                builder.Append(";RETURN 1");
                String sqlStr = builder.ToString();
                try
                {
                    int rev = Run.RunSql_Int(sqlStr);
                    if (rev == 1)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message, ex);
                }
            }
            /// <summary>
            /// 删除信息类别的同时,遍历其下分的各个信息子类,同时删除信息子类的数据源和采集周期,此处需要修改
            /// </summary>
            /// <param name="intId"></param>
            /// <returns></returns>
            public int DeleteMessage(int intId)
            {
                //递归获取某类信息的所有子类MgIds
                string MgIds = intId.ToString() + ",";
                MgIds += GetMgIds(intId.ToString());
                string[] MgId = MgIds.Split(',');
                //根据信息类别编号,获取所有的信息类别对应的数据源编号DataSourceIds
                string DataSourceIds = string.Empty;
                for (int i = 0; i < MgId.Length - 1; i++)
                {
                    DataSourceIds += this.GetMessageModel(int.Parse(MgId[i].ToString())).DataSourceIds.ToString();
                }
                if (DataSourceIds != "")
                {
                    try
                    {
                        //删除数据源编号DataSourceIds
                        int Num = Run.RunSql_Int("delete from Gather_DataSourceConfig where DataSourceId in (" + DataSourceIds.Substring(0, DataSourceIds.Length - 1) + ")");
                    }
                    catch(Exception ex)
                    { 
                        throw new Exception(ex.Message, ex);
                    }
                }
                //删除信息类别
                try
                {
                    //删除数据源编号DataSourceIds
                    int Num = Run.RunSql_Int("delete from Gather_Message where MgId in (" + MgIds.Substring(0, MgIds.Length - 1) + ")");
                    return 1;
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message, ex);
                }
                return 0;
            }
            /// <summary>
            /// 递归获取某类信息的所有子类
            /// </summary>
            /// <param name="pNodeId"></param>
            /// <returns></returns>
            private string GetMgIds(string pNodeId)
            {
                string MgIds = string.Empty;
                DataSet ds = new DataSet();
                ds = this.GetMessage("MgPId=" + pNodeId);
                if (ds != null)
                {
                    if (ds.Tables.Count > 0)
                    {
                        if (ds.Tables[0].Rows.Count > 0)
                        {
                            foreach (DataRow myRow in ds.Tables[0].Rows)
                            {
                                MgIds += myRow["MgId"].ToString() + "," + GetMgIds(myRow["MgId"].ToString());
                            }
                        }
                    }
                }
                return MgIds;
            }
            //说明:获取所有信息分类
            public DataSet GetMessage()                   // 获取所有信息分类
            {
                String sqlStr = "SELECT * FROM Gather_Message Order By MgId";
                DataSet ds = null;
                try
                {
                    ds = Run.RunSql_DataSet(sqlStr);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message, ex);
                }
                return ds;
            }
            //说明:获取信息分类信息
            public DataSet GetMessage(string strWhere)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("SELECT *  ");
                strSql.Append(" FROM Gather_Message ");
                if (strWhere.Trim() != "")
                {
                    strSql.Append(" where " + strWhere);
                }
                strSql.Append("  Order By MgId");
                return Run.RunSql_DataSet(strSql.ToString());
            }
            /// <summary>
            /// 获取所有父类信息类别
            /// </summary>
            /// <returns></returns>
            public DataSet GetMessageParent()
            {
                return GetMessage("MgPId=0");
            }
            /// <summary>
            /// 更新一条数据
            /// </summary>
            public int UpdateMessage(Gather_Message model)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update Gather_Message set ");
                strSql.Append("MgPId=" + model.MgPId + ",");
                strSql.Append("MgName='" + model.MgName.Replace("'","''") + "',");
                strSql.Append("DataSourceIds='" + model.DataSourceIds.Replace("'","''") + "',");
                strSql.Append("OrderId=" + model.OrderId + ",");
                strSql.Append("Remark='" + model.Remark.Replace("'","''")  + "'");
                strSql.Append(" where MgId=" + model.MgId + "");
                return Run.RunSql_Int(strSql.ToString());
            }
            /// <summary>
            /// 得到一个对象实体
            /// </summary>
            public Gather_Message GetMessageModel(int MgId)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * from Gather_Message ");
                strSql.Append(" where MgId=" + MgId);
                Gather_Message model = new Gather_Message();
                DataSet ds = Run.RunSql_DataSet(strSql.ToString());
                model.MgId = MgId;
                if (ds.Tables[0].Rows.Count > 0)
                {
                    if (ds.Tables[0].Rows[0]["MgPId"].ToString() != "")
                    {
                        model.MgPId = int.Parse(ds.Tables[0].Rows[0]["MgPId"].ToString());
                    }
                    model.MgName = ds.Tables[0].Rows[0]["MgName"].ToString();
                    model.DataSourceIds = ds.Tables[0].Rows[0]["DataSourceIds"].ToString();
                    if (ds.Tables[0].Rows[0]["OrderId"].ToString() != "")
                    {
                        model.OrderId = int.Parse(ds.Tables[0].Rows[0]["OrderId"].ToString());
                    }
                    model.Remark = ds.Tables[0].Rows[0]["Remark"].ToString();
                    return model;
                }
                else
                {
                    return null;
                }
            }        
            //说明:取得所有的信息分类集合。
            public DataSet GetDataSourceIds(int intId)  // 获取该信息类别下的所有数据源
            {
                OleDbDataReader oDr = null;
                String nDataSourceIds = ""; //该分类下的所有数据源ID
                try
                {
                    String dscSqlstr = "SELECT DataSourceIds FROM Gather_Message Where MgId=" + intId;
                    oDr = Run.RunSql_DataReader(dscSqlstr);
                    if (oDr.Read())
                    {
                        nDataSourceIds = oDr["DataSourceIds"].ToString();
                    }
                    oDr.Close();
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message, ex);
                }
                if (nDataSourceIds != "")
                {
                    String sqlStr1 = "SELECT * FROM Gather_DataSourceConfig Where DataSourceId in (" + nDataSourceIds.Remove(nDataSourceIds.Length-1,1) + ") Order By OrderId";
                    DataSet ds = null;
                    try
                    {
                        ds = Run.RunSql_DataSet(sqlStr1);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message, ex);
                    }
                    return ds;
                }
                else
                {
                    return null;
                }
            }
           
            #endregion
            #region Web 数据源配置
            /// <summary>
            /// 删除一条数据
            /// </summary>
            public int DeleteDataSourceConfig(int DataSourceId)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete Gather_DataSourceConfig ");
                strSql.Append(" where DataSourceId=" + DataSourceId);
                return Run.RunSql_Int(strSql.ToString());
            }
            /// <summary>
            /// 更新一条数据
            /// </summary>
            public int UpdateDataSourceConfig(Gather_DataSourceConfig model)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update Gather_DataSourceConfig set ");
                strSql.Append("DataSourceName='" + model.DataSourceName.Replace("'","''") + "',");
                strSql.Append("DataSourceUrl='" + model.DataSourceUrl.Replace("'","''") + "',");
                strSql.Append("DataSourceEncoding='" + model.DataSourceEncoding.Replace("'","''") + "',");
                strSql.Append("PeriodId=" + model.PeriodId + ",");
                strSql.Append("OrderId=" + model.OrderId + ",");
                strSql.Append("Remark='" + model.Remark.Replace("'","''")  + "'");
                strSql.Append(" where DataSourceId=" + model.DataSourceId + "");
                return Run.RunSql_Int(strSql.ToString());
            }
            /// <summary>
            /// 增加一条数据
            /// </summary>
            public int AddDataSourceConfig(Gather_DataSourceConfig model)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into Gather_DataSourceConfig(");
                strSql.Append("DataSourceName,DataSourceUrl,DataSourceEncoding,PeriodId,OrderId,Remark");
                strSql.Append(")");
                strSql.Append(" values (");
                strSql.Append("'" + model.DataSourceName.Replace("'","''") + "',");
                strSql.Append("'" + model.DataSourceUrl.Replace("'","''") + "',");
                strSql.Append("'" + model.DataSourceEncoding.Replace("'","''") + "',");
                strSql.Append("" + model.PeriodId + ",");
                strSql.Append("" + model.OrderId + ",");
                strSql.Append("'" + model.Remark.Replace("'","''")  + "'");
                strSql.Append(");select @@IDENTITY;");
                OleDbDataReader dr;
                dr = Run.RunSql_DataReader(strSql.ToString());
                int Id = 0;
                if (dr.Read())
                {
                    Id = int.Parse(dr[0].ToString());
                }
                dr.Close();
                return Id;
            }
            /// <summary>
            /// 根据数据源编号获取该数据源配置信息
            /// </summary>
            /// <param name="intDataSourceId">数据源编号</param>
            /// <returns>数据源配置信息</returns>
            public Gather_DataSourceConfig GetDataSourceConfig(int intDataSourceId)
            {
                try
                {
                    string strSql = "select * from Gather_DataSourceConfig where DataSourceId = " + intDataSourceId;
                    DataTable dtDataSourceConfig = new DataTable();
                    dtDataSourceConfig = Run.RunSql_DataSet(strSql).Tables[0];
                    for (int i = 0; i < dtDataSourceConfig.Rows.Count; i++)
                    {
                        DataSourceConfig.DataSourceId = int.Parse(dtDataSourceConfig.Rows[i][0].ToString());
                        DataSourceConfig.DataSourceName = dtDataSourceConfig.Rows[i]["DataSourceName"].ToString();
                        DataSourceConfig.DataSourceUrl = dtDataSourceConfig.Rows[i]["DataSourceUrl"].ToString();
                        DataSourceConfig.DataSourceEncoding = dtDataSourceConfig.Rows[i]["DataSourceEncoding"].ToString();
                        DataSourceConfig.PeriodId = int.Parse(dtDataSourceConfig.Rows[i]["PeriodId"].ToString());
                        DataSourceConfig.OrderId = int.Parse(dtDataSourceConfig.Rows[i]["OrderId"].ToString());
                        DataSourceConfig.Remark = dtDataSourceConfig.Rows[i]["Remark"].ToString();
                    }
                    return DataSourceConfig;
                }
                catch
                {
                    return null;
                }
            }
            /// <summary>
            /// 获得数据列表
            /// </summary>
            public DataSet GetDataSourceConfigList(string strWhere)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * from Gather_DataSourceConfig ");
                if (strWhere.Trim() != "")
                {
                    strSql.Append(" where " + strWhere);
                }
                strSql.Append(" order by DataSourceId ");
                return Run.RunSql_DataSet(strSql.ToString());
            }
            /// <summary>
            /// 获取所有数据源列表
            /// </summary>
            /// <returns></returns>
            public DataSet GetDataSourceConfigList()
            {
                return GetDataSourceConfigList("");
            }
            #endregion
            #region 采集周期设置
            /// <summary>
            /// 获取所有周期设置
            /// </summary>
            /// <returns>所有周期设置</returns>
            public DataSet GetPeriodList()
            {
                string strsql = "select * from Gather_Period";
                try
                {
                    return Run.RunSql_DataSet(strsql);
                }
                catch (Exception myException)
                {
                    throw new Exception(myException.Message, myException);
                }
            }
            /// <summary>
            /// 新增采集周期
            /// </summary>
            /// <param name="description">周期名称</param>
            /// <param name="timeGather">定时采集时间点</param>
            /// <param name="periodGather">周期采集隔间时间(单位:分钟)</param>
            /// <param name="startTime">开始时间</param>
            /// <param name="endTime">结束时间</param>
            /// <returns>周期编号/0</returns>
            public int AddPeriod(Gather_Period gather_Period)
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("insert into Gather_Period (Description,TimeGather,PeriodGather,StartTime,EndTime) values (");
                sb.Append("'" + gather_Period.Description.Replace("'","''")  + "'" + ",");
                sb.Append("'" + gather_Period.TimeGather.Replace("'","''")  + "'" + ",");
                sb.Append("" + gather_Period.PeriodGather + ",");
                sb.Append("'" + gather_Period.StartTime.Replace("'","''")  + "'" + ",");
                sb.Append("'" + gather_Period.EndTime.Replace("'","''")  + "'");
                sb.Append(");select @@IDENTITY;");
                OleDbDataReader odr = Run.RunSql_DataReader(sb.ToString());
                if (odr.Read())
                {
                    return Convert.ToInt32(odr[0].ToString());
                }
                else
                {
                    return 0;
                }
             
            }
            /// <summary>
            /// 删除该采集周期信息
            /// </summary>
            /// <param name="periodId">周期编号</param>
            /// <returns>True/False</returns>
            public bool DeletePeriod(int periodId)
            {
                string strsql = "delete from Gather_Period where PeriodId = '" + periodId + "'";
                try
                {
                    return Run.RunSql_Int(strsql) > 0 ? true : false;
                }
                catch
                {
                    return false;
                }
            }
            /// <summary>
            /// 
            /// </summary>
            /// <param name="periodId">周期编号</param>
            /// <param name="description">周期名称</param>
            /// <param name="timeGather">定时采集时间点</param>
            /// <param name="periodGather">周期采集隔间时间(单位:分钟)</param>
            /// <param name="startTime">开始时间</param>
            /// <param name="endTime">结束时间</param>
            /// <returns>True/False</returns>
            public int UpdatePeriod(Gather_Period model)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update Gather_Period set ");
                strSql.Append("Description='" + model.Description.Replace("'","''") + "',");
                strSql.Append("TimeGather='" + model.TimeGather.Replace("'","''") + "',");
                strSql.Append("PeriodGather=" + model.PeriodGather + ",");
                strSql.Append("StartTime='" + model.StartTime.Replace("'","''") + "',");
                strSql.Append("EndTime='" + model.EndTime.Replace("'","''")  + "'");
                strSql.Append(" where PeriodId=" + model.PeriodId + "");
                return Run.RunSql_Int(strSql.ToString());
            }
            /// <summary>
            /// 得到一个对象实体
            /// </summary>
            public Gather_Period GetPeriodModel(int PeriodId)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select * from Gather_Period ");
                strSql.Append(" where PeriodId=" + PeriodId);
                Gather_Period model = new Gather_Period();
                DataSet ds = Run.RunSql_DataSet(strSql.ToString());
                model.PeriodId = PeriodId;
                if (ds.Tables[0].Rows.Count > 0)
                {
                    model.Description = ds.Tables[0].Rows[0]["Description"].ToString();
                    model.TimeGather = ds.Tables[0].Rows[0]["TimeGather"].ToString();
                    if (ds.Tables[0].Rows[0]["PeriodGather"].ToString() != "")
                    {
                        model.PeriodGather = int.Parse(ds.Tables[0].Rows[0]["PeriodGather"].ToString());
                    }
                    model.StartTime = ds.Tables[0].Rows[0]["StartTime"].ToString();
                    model.EndTime = ds.Tables[0].Rows[0]["EndTime"].ToString();
                    return model;
                }
                else
                {
                    return null;
                }
            }
            #endregion
            #region 详细信息配置
            
            /// <summary>
            /// 获取操作类型
            /// </summary>
            /// <returns></returns>
            public DataSet GetOperationKind()
            {
                string strSql = "select * from Gather_OperationKind";
                return Run.RunSql_DataSet(strSql);
            }
            /// <summary>
            /// 新增详细信息配置
            /// </summary>
            /// <param name="entity">详细信息配置实体</param>
            /// <returns>新增详细信息配置的编号</returns>
            public int AddParticularConfig(Gather_ParticularConfig entity)
            {
                string strSql = "insert into Gather_ParticularConfig(DataSourceId,BasicConfigId,OparationId,StartString," 
                    + "EndString,NewString,IsContainHeader,IsContainTail,Remark)"
                    + " values(" + entity.DataSourceId + "," + entity.BasicConfigId + "," + entity.OparationId + ",'"
                    + entity.StartString.Replace("'", "''") + "','" + entity.EndString.Replace("'", "''") + "','" + entity.NewString.Replace("'", "''") + "',"
                    + entity.IsContainHeader + "," + entity.IsContainTail + ",'" + entity.Remark.Replace("'", "''") + "')"; //;select @@identity
                return Run.RunSql_Int(strSql);
            }
            /// <summary>
            /// 修改详细信息配置
            /// </summary>
            /// <param name="entity">详细信息配置实体</param>
            /// <returns></returns>
            public int UpdateParticularConfig(Gather_ParticularConfig entity)
            {
                string strSql = "update Gather_ParticularConfig set DataSourceId = " + entity.DataSourceId + ","
                    + " BasicConfigId = " + entity.BasicConfigId + ",OparationId = " + entity.OparationId + ","
                    + " StartString = '" + entity.StartString.Replace("'", "''") + "',EndString = '" + entity.EndString.Replace("'", "''") + "',"
                    + " NewString = '" + entity.NewString.Replace("'", "''") + "',IsContainHeader = " + entity.IsContainHeader + ","
                    + " IsContainTail = " + entity.IsContainTail + ", Remark = '" + entity.Remark.Replace("'","''")  + "'"
                    + " where ParticularConfigId = " + entity.ParticularConfigId;
                return Run.RunSql_Int(strSql);
            }
            /// <summary>
            /// 根据编号删除详细配置信息
            /// </summary>
            /// <param name="intParticularConfigId">详细信息配置编号</param>
            /// <returns></returns>
            public int DeleteParticularConfig(int intParticularConfigId)
            {
                string strSql = "delete from Gather_ParticularConfig where ParticularConfigId = " + intParticularConfigId;
                return Run.RunSql_Int(strSql);
            }
            /// <summary>
            /// 获得数据列表
            /// </summary>
            public DataSet GetParticularConfigList(string strWhere)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select [ParticularConfigId],[DataSourceId],[BasicConfigId],[OparationId],[StartString],[EndString],[NewString],[IsContainHeader],[IsContainTail],[Remark] ");
                strSql.Append(" FROM Gather_ParticularConfig ");
                if (strWhere.Trim() != "")
                {
                    strSql.Append(" where " + strWhere);
                }
                return Run.RunSql_DataSet(strSql.ToString());
            }
            /// <summary>
            /// 根据数据源编号,获取该编号下的所有详细信息配置的编号
            /// </summary>
            /// <param name="intDataSourceId">数据源编号</param>
            /// <returns>详细信息配置</returns>
            public DataSet GetPCIdsByDataSourceId(int intDataSourceId)
            {
                return GetParticularConfigList("DataSourceId=" + intDataSourceId);
            }
            /// <summary>
            /// 获取所有的详细信息配置
            /// </summary>
            /// <returns></returns>
            public DataSet GetAllParticularConfig()
            {
                string strSql = "select * from Gather_ParticularConfig";
                return Run.RunSql_DataSet(strSql);
            }
            /// <summary>
            /// 根据数据源编码和基本配置编码获得详细配置信息
            /// </summary>
            /// <param name="intDataSourceId">数据源编码</param>
            /// <param name="intBasicConfigId">基本配置信息编码</param>
            /// <returns>详细信息配置集合</returns>
            public DataSet GetParticularConfig(int intDataSourceId, int intBasicConfigId)
            {
                string strSql = "select * from Gather_ParticularConfig where datasourceid = " + intDataSourceId
                    + " and BasicConfigId = " + intBasicConfigId + " order by ParticularConfigId ";
                DataSet dsPaticularConfig = new DataSet();
                dsPaticularConfig = Run.RunSql_DataSet(strSql);
                return dsPaticularConfig;
            }
            /// <summary>
            /// 根据数据源编码和基本配置编码获得详细配置信息
            /// </summary>
            /// <param name="intDataSourceId">数据源编码</param>
            /// <param name="intBasicConfigId">基本配置信息编码</param>
            /// <returns>详细信息配置集合</returns>
            public DataSet GetParticularConfigEdit(int intDataSourceId, int intBasicConfigId)
            {
                //string strSql = "select ParticularConfigId,DataSourceId,BasicConfigId,"
                //    + " '操作类型' = case OparationId when 1 then '截取' when 2 then '删除段落'when 3 then '删除字符串'when 4 then '字符串前缀'when 5 then '段落前缀'when 6 then '字符串后缀'when 7 then '段落后缀'when 8 then '替换字符串'else '替换段落' end,"
                //    + " StartString as '开始字符串',EndString as '结束字符串',NewString as '替换字符串',"
                //    + " '包含开始字符串' = case IsContainHeader when 1 then '是' else '否' end,"
                //    + " '包含结束字符串'  = case IsContainTail when 1 then '是' else '否' end ,Remark as '备注'  "
                //    + " from Gather_ParticularConfig where datasourceid = " + intDataSourceId
                //    + " and BasicConfigId = " + intBasicConfigId + " order by ParticularConfigId ";
                string strSql = "select ParticularConfigId,DataSourceId,BasicConfigId,b.operationkind as '操作类型', "
                    + " StartString as '开始字符串',EndString as '结束字符串',NewString as '替换字符串',"
                    + " IsContainHeader as '包含开始字符串',IsContainTail as '包含结束字符串',Remark as '备注' "
                    + " from gather_particularconfig a, gather_operationkind b "
                    + " where datasourceid = " + intDataSourceId + " and BasicConfigId = " + intBasicConfigId
                    + " and a.oparationid = b.operationid order by ParticularConfigId ";
                DataSet dsPaticularConfig = new DataSet();
                dsPaticularConfig = Run.RunSql_DataSet(strSql);
                return dsPaticularConfig;
            }
            /// <summary>
            /// 根据详细信息配置编号获取其详细信息配置
            /// </summary>
            /// <param name="intParticularConfigId">详细信息配置编号</param>
            /// <returns>详细信息配置</returns>
            public Gather_ParticularConfig GetParticularConfigModel(int intParticularConfigId)
            {
                string strSql = "select * from Gather_ParticularConfig where ParticularConfigId = " + intParticularConfigId;
                DataTable dtParticularConfig = new DataTable();
                dtParticularConfig = Run.RunSql_DataSet(strSql).Tables[0];
                foreach (DataRow dr in dtParticularConfig.Rows)
                {
                    ParticularConfig.ParticularConfigId = int.Parse(dr["ParticularConfigId"].ToString());
                    ParticularConfig.DataSourceId = int.Parse(dr["DataSourceId"].ToString());
                    ParticularConfig.BasicConfigId = int.Parse(dr["BasicConfigId"].ToString());
                    ParticularConfig.OparationId = int.Parse(dr["OparationId"].ToString());
                    ParticularConfig.StartString = dr["StartString"].ToString();
                    ParticularConfig.EndString = dr["EndString"].ToString();
                    ParticularConfig.NewString = dr["NewString"].ToString();
                    ParticularConfig.IsContainHeader = int.Parse(dr["IsContainHeader"].ToString());
                    ParticularConfig.IsContainTail = int.Parse(dr["IsContainTail"].ToString());
                    ParticularConfig.Remark = dr["Remark"].ToString();
                }
                return ParticularConfig;
            }
            #endregion
            #region 文章采集结果存储操作
            /// <summary>
            /// 是否存在该记录
            /// </summary>
            public bool CheckArticleExists(string Title)
            {
                bool Exists = false;
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select count(*) from Gather_Article where Title='" + Title.Replace("'","''")  + "'");
                OleDbDataReader dr = Run.RunSql_DataReader(strSql.ToString());
                if (dr.Read())
                {
                    if (int.Parse(dr[0].ToString()) > 0)
                    {
                        Exists = true;
                    }
                    else
                    {
                        Exists = false;
                    }
                }
                dr.Close();
                return Exists;
            }
            /// <summary>
            /// 增加一条数据
            /// </summary>
            public int AddArticle(Gather_Article model)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("insert into Gather_Article(");
                strSql.Append("MgId,Title,Resource,Content");
                strSql.Append(")");
                strSql.Append(" values (");
                strSql.Append(model.MgId + ",");
                strSql.Append("'" + model.Title.Replace("'","''") + "',");
                strSql.Append("'" + model.Resource.Replace("'","''") + "',");
                strSql.Append("'" + model.Content.Replace("'","''") + "'");
                strSql.Append(")");
                return Run.RunSql_Int(strSql.ToString());
            }
            /// <summary>
            /// 更新一条数据
            /// </summary>
            public int Update(Gather_Article model)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("update Gather_Article set ");
                strSql.Append("MgId=" + model.MgId + ",");
                strSql.Append("Title='" + model.Title.Replace("'","''") + "',");
                strSql.Append("Resource='" + model.Resource.Replace("'","''") + "',");
                strSql.Append("Content='" + model.Content.Replace("'","''") + "',");
                strSql.Append("IsShow=" + model.IsShow + ",");
                strSql.Append("Picture='" + model.Picture.Replace("'","''")  + "'");
                strSql.Append(" where MgId=" + model.MgId + "");
                return Run.RunSql_Int(strSql.ToString());
            }
            /// <summary>
            /// 删除一条数据
            /// </summary>
            public int DeleteArticle(int MgId)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("delete Gather_Article ");
                strSql.Append(" where MgId=" + MgId);
                return Run.RunSql_Int(strSql.ToString());
            }
            /// <summary>
            /// 得到一个对象实体
            /// </summary>
            public Gather_Article GetModel(int MgId)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select  ");
                strSql.Append("[MgId],[Title],[Resource],[Content],[IsShow],[Picture] ");
                strSql.Append(" from Gather_Article ");
                strSql.Append(" where MgId=" + MgId);
                Gather_Article model = new Gather_Article();
                DataSet ds = Run.RunSql_DataSet(strSql.ToString());
                if (ds.Tables[0].Rows.Count > 0)
                {
                    if (ds.Tables[0].Rows[0]["MgId"].ToString() != "")
                    {
                        model.MgId = int.Parse(ds.Tables[0].Rows[0]["MgId"].ToString());
                    }
                    model.Title = ds.Tables[0].Rows[0]["Title"].ToString();
                    model.Resource = ds.Tables[0].Rows[0]["Resource"].ToString();
                    model.Content = ds.Tables[0].Rows[0]["Content"].ToString();
                    if (ds.Tables[0].Rows[0]["IsShow"].ToString() != "")
                    {
                        model.IsShow = int.Parse(ds.Tables[0].Rows[0]["IsShow"].ToString());
                    }
                    model.Picture = ds.Tables[0].Rows[0]["Picture"].ToString();
                    return model;
                }
                else
                {
                    return null;
                }
            }
            /// <summary>
            /// 获得数据列表
            /// </summary>
            public DataSet GetArticleList(string strWhere)
            {
                StringBuilder strSql = new StringBuilder();
                strSql.Append("select [MgId],[Title],[Resource],[Content],[IsShow],[Picture] ");
                strSql.Append(" FROM Gather_Article ");
                if (strWhere.Trim() != "")
                {
                    strSql.Append(" where " + strWhere);
                }
                return Run.RunSql_DataSet(strSql.ToString());
            }
            #endregion
            #region 其它
            
            /// <summary>
            /// 根据采集url地址以及编码方式获得网页源代码
            /// </summary>
            /// <param name="strUrl">信息采集地址 eg:www.sina.com</param>
            /// <param name="strEncoding">编码方式 eg:gb2312,utf-8 etc</param>
            /// <returns>网页源代码字符串</returns>
            public string getUrlSource(string strUrl,string strEncoding)
            {
                string lsResult;
                try
                { 
                    HttpWebRequest req = (HttpWebRequest)WebRequest.Create(strUrl);
                    HttpWebResponse rep = (HttpWebResponse)req.GetResponse();
                    StreamReader sr = new StreamReader(rep.GetResponseStream(), Encoding.GetEncoding(strEncoding));
                    lsResult = sr.ReadToEnd();
                }
                catch(Exception ex)
                {
                    lsResult = "";
                    Console.WriteLine(ex.Message);
                }
                return lsResult;
            }
            
            /// <summary>
            /// 根据各个操作类型处理字符串
            /// </summary>
            /// <param name="str">要处理的字符串</param>
            /// <param name="startstr">开始字符串</param>
            /// <param name="endstr">结束字符串</param>
            /// <param name="Newstr">替换/前缀/后缀的字符串</param>
            /// <param name="operate">操作类型</param>
            /// <param name="inHead">包含头1,不包含头0</param>
            /// <param name="inTail">包含尾1,不包含尾0</param>
            /// <returns>返回处理过后的字符串</returns>
            private string OperateStr(string str, string startstr, string endstr, string Newstr, int operate, int inHead, int inTail)
            {
                int intLength = str.Length;
                int startIndex = str.IndexOf(startstr);
                int endIndex = str.IndexOf(endstr);
                switch (operate)
                {
                    case 1://截取
                        if (endIndex <= startIndex || startIndex == -1)
                            return "";
                        if (inHead == 1 && inTail == 1 )//表示包含头尾,保证能找到尾
                        {
                            return str.Substring(startIndex, endIndex - startIndex + endstr.Length);
                            
                        }
                        if (inHead == 1 && inTail == 0)//表示包含头,不包含尾
                        {
                            return str.Substring(startIndex, endIndex - startIndex);
                        }
                        if (inHead == 0 && inTail == 1)//表示不包含头,包含尾
                        {
                            return str.Substring(startIndex + startstr.Length, endIndex - startIndex - startstr.Length + endstr.Length);
                        }
                        if (inHead == 0 && inTail == 0)//表示不包含头,也不包含尾
                        {
                            return str.Substring(startIndex + startstr.Length, endIndex - startIndex - startstr.Length);
                        }
                        break;
                    case 2://删除段
                        if (endIndex <= startIndex || startIndex == -1)
                            return str;
                        if (inHead == 1 && inTail == 1)//表示包含头尾
                        {
                            return str.Replace(str.Substring(startIndex, endIndex - startIndex + endstr.Length), "");
                        }
                        if (inHead == 1 && inTail == 0)//表示包含头,不包含尾
                        {
                            return str.Replace(str.Substring(startIndex, endIndex - startIndex ), "");
                        }
                        if (inHead == 0 && inTail == 1)//表示不包含头,包含尾
                        {
                            return str.Replace(str.Substring(startIndex + startstr.Length, endIndex - startIndex - startstr.Length + endstr.Length), "");
                        }
                        if (inHead == 0 && inTail == 0)//表示不包含头,也不包含尾
                        {
                            return str.Replace(str.Substring(startIndex + startstr.Length, endIndex - startIndex - startstr.Length), "");
                        }
                        break;
                    case 3://删除字符串
                        return str.Replace(startstr, "");
                        break;
                    case 4://字符串前缀
                        return str.Replace(startstr, Newstr + startstr);
                        break;
                    case 5://段落前缀
                        if (endIndex <= startIndex || startIndex == -1)
                            return str;
                        if (inHead == 1)
                            return str.Replace(str.Substring(startIndex, endIndex - startIndex), Newstr + str.Substring(startIndex, endIndex - startIndex));
                        if (inHead == 0)
                            return str.Replace(str.Substring(startIndex + startstr.Length, endIndex - startIndex - startstr.Length), Newstr + str.Substring(startIndex + startstr.Length, endIndex - startIndex - startstr.Length));
                        break;
                    case 6://字符串后缀
                        return str.Replace(startstr, startstr + Newstr);
                        break;
                    case 7://段落后缀
                        if (endIndex <= startIndex || startIndex == -1)
                            return str;
                        if (inTail == 1)
                            return str.Replace(str.Substring(startIndex, endIndex - startIndex + endstr.Length), str.Substring(startIndex, endIndex - startIndex + endstr.Length) + Newstr);
                        if (inTail == 0)
                            return str.Replace(str.Substring(startIndex, endIndex - startIndex), Newstr + str.Substring(startIndex, endIndex - startIndex) + Newstr);
                        break;
                    case 8://替换字符串
                        return str.Replace(startstr, Newstr);
                        break;
                    case 9://替换段落
                        if (inHead == 1 && inTail == 1)//表示包含头尾
                        {
                            return str.Replace(str.Substring(startIndex, endIndex - startIndex + endstr.Length), Newstr);
                        }
                        if (inHead == 1 && inTail == 0)//表示包含头,不包含尾
                        {
                            return str.Replace(str.Substring(startIndex, endIndex - startIndex), Newstr);
                        }
                        if (inHead == 0 && inTail == 1)//表示不包含头,包含尾
                        {
                            return str.Replace(str.Substring(startIndex + startstr.Length, endIndex - startIndex - startstr.Length + endstr.Length), Newstr);
                        }
                        if (inHead == 0 && inTail == 0)//表示不包含头,也不包含尾
                        {
                            return str.Replace(str.Substring(startIndex + startstr.Length, endIndex - startIndex - startstr.Length), Newstr);
                        }
                        break;
                }
                return str;
            }
            /// <summary>
            /// 获取处理过后的字符串(信息列表、信息内容、信息标题、信息来源、信息正文等)
            /// </summary>
            /// <param name="str">要处理的字符串</param>
            /// <param name="dsParticularConfig">详细信息配置</param>
            /// <returns>处理后的字符串</returns>
            public string GetOperatedString(string str,DataSet dsParticularConfig)
            {
                foreach (DataRow dr in dsParticularConfig.Tables[0].Rows)
                {
                    str = OperateStr(str, dr["StartString"].ToString(), dr["EndString"].ToString(), dr["NewString"].ToString(),
                            int.Parse(dr["OparationId"].ToString()), int.Parse(dr["IsContainHeader"].ToString()), int.Parse(dr["IsContainTail"].ToString()));
                }
                return str;
            }
            /// <summary>
            /// 通过 信息列表以及单个url信息的详细配置 来获取单个url信息的集合
            /// </summary>
            /// <param name="OperatedStr">信息列表(处理过后的字符串)</param>
            /// <param name="dsSingleUrlConfig">单个url配置信息集合</param>
            /// <returns>单个url信息集合</returns>
            public DataTable GetAllSingleUrl(string Str,DataSet dsSingleUrlConfig)
            {
                DataTable dtSingleUrls = new DataTable();
                DataColumn dc = new DataColumn("SingleUrl");
                dtSingleUrls.Columns.Add(dc);
                while(Str != "")
                {
                    string str1 = string.Empty;//要得到的单个url临时变量
                    str1 = Str;
                    string strTemp = string.Empty;
                    int RowNum = 0;
                    foreach (DataRow dr in dsSingleUrlConfig.Tables[0].Rows)
                    {
                        //进行处理,获得单一的url信息
                        str1 = OperateStr(str1, dr["StartString"].ToString(), dr["EndString"].ToString(), dr["NewString"].ToString(),
                            int.Parse(dr["OparationId"].ToString()), int.Parse(dr["IsContainHeader"].ToString()), int.Parse(dr["IsContainTail"].ToString()));
                        // 判断是否是截取操作,截取操作时,开始字符串与结束字符串都不为空
                        if (RowNum==0 && dr["OparationId"].ToString() == "1" && str1.IndexOf(dr["StartString"].ToString()) != -1 && str1.IndexOf(dr["EndString"].ToString()) != -1)
                        {
                            strTemp = str1;
                        }
                        RowNum++;
                    }
                    if (str1 != "" && str1 != Str)
                    {
                        Str = Str.Replace(strTemp, "");  // 从信息列表中删除掉已经获取到的url
                        DataRow dr = dtSingleUrls.NewRow();
                        dr[0] = str1;
                        dtSingleUrls.Rows.Add(dr);
                        int count = dtSingleUrls.Rows.Count;
                    }
                    else
                        Str = ""; // 当获取不到单一的url地址后,则将字符串设置为空,作为退出循环的条件
                }
                return dtSingleUrls;
            }
            #endregion
        }
    }

    类中的方法可以单独摘出来用,不用的代码你自己可以删除。

  • 已读: 次 收藏本文关闭本文打印本文复制链接