巫霜框架：通过工具页面生成SQL脚本

2017-07-23 09:48:45  访问(991) 赞(0) 踩(0)

• 内容
• 生成的脚本

• 
  
  1、新闻栏目的数据被误删了
  2、sqlserver不知道怎么去找到符合条件的脚本
  3、用巫霜的常用代码工具，生成该脚本
  
  

  DataSet转成新增SQL脚本的代码

  
      /// <summary>
      ///  SqlServer 版本DataSet和TableName获得新增SQL脚本的代码
      /// </summary>
      /// <param name="tableName">
      /// <param name="ds">
      /// <returns></returns>
      protected string GetInsertSQL(string tableName, DataSet ds)
      {
          DataTable dt = ds.Tables[0];
  
          DataColumnCollection dcc = dt.Columns;
  
          StringBuilder theResult = new StringBuilder();
          StringBuilder sqlItem = null;
          StringBuilder sqlParams = null;
  
          Type typeofDateTime = typeof(DateTime);
          Type typeofint = typeof(int);
          Type typeoflong = typeof(long);
          Type typeoffloat = typeof(float);
          Type typeofdecimal = typeof(decimal);
          Type typeofdouble = typeof(double);
  
  
          bool isFirst = true;
          object oValue = null;
          string strValue = "";
          foreach (DataRow dr in dt.Rows)
          {
              if (dr == null)
                  continue;
  
              isFirst = true;
              sqlItem = new StringBuilder();
              sqlParams = new StringBuilder();
  
              foreach (DataColumn dc in dcc)
              {
                  if (isFirst)
                  {
                      isFirst = false;
                  }
                  else
                  {
                      sqlItem.Append(",");
                      sqlParams.Append(",");
                  }
  
                  oValue = dr[dc.ColumnName];
  
                  if (oValue == null || oValue == DBNull.Value)
                      strValue = "null";
                  else if (dc.DataType == typeofDateTime)
                  {
                      strValue = "convert(datetime, '" + Convert.ToDateTime(oValue).ToString("yyyy-MM-dd HH:mm:ss") + "', 20)";
                  }
                  else if (dc.DataType == typeofint || dc.DataType == typeoflong
                      || dc.DataType == typeoffloat || dc.DataType == typeofdouble
                      || dc.DataType == typeofdecimal)
                  {
                      strValue = oValue.ToString();
                  }
                  else
                  {
                      strValue = "'" + oValue.ToString().Replace("'", "''") + "'";
                  }
  
                  sqlItem.Append(dc.ColumnName);
                  sqlParams.Append(strValue);
              }
  
              theResult.AppendLine("insert into " + tableName + " (" + sqlItem.ToString() + ") values (" + sqlParams.ToString() + ") ;");
          }
  
          return theResult.ToString();
      }
  
  

  逻辑代码

   
  
      protected string ToInsert(DBHelper xdbHelper)
      {
          string theResult = "";
  
          bool bIsCreate = true;
  
          if (xdbHelper == null)
          {
              xdbHelper
                  =
                  SlowX.DAL.Helpers.DBHelper.CreateDBHelper
                  (
                      "server=xxxx;database=xxx;user id=xx;pwd=xxxx;", 
                      SlowX.DAL.Enums.DataBaseHelper.EmDataBaseHelper.SQLServer,
                      1
                  );
          }
          else
          {
              // 没有打开链接 //
              bIsCreate = xdbHelper.IsNotOpen();
          }
  
          try
          {
              if (bIsCreate)
                  xdbHelper.OpenDBHelper();
  
              DataSet ds = xdbHelper.ExecuteDataSet("select * from UTB_SLOWX_NEWS_SORT t");
  
              theResult = GetInsertSQL("UTB_SLOWX_NEWS_SORT", ds);
  
              if (bIsCreate)
                  xdbHelper.EndDBHelper();
  
          }
          catch (Exception err)
          {
              if (bIsCreate)
                  xdbHelper.TranDBHelper();
  
              throw err;
          }
          finally
          {
              if (bIsCreate)
                  xdbHelper.FinallyDBHelper();
          }
  
          return theResult;
  
      }
  
      
  

• insert into UTB_SLOWX_NEWS_SORT (ID,TheName,TheCode,PID,Layer,Path,ShowSeq,ListUrl,DetailUrl,IsDelete,IsShow,Remark,CreateTime,UpdateTime,MetaKeywords,MetaDescription,LinkTarget,EmNewsSortTypeValue,Hits,HitTime,UrlRewriterDetailUrl,UrlRewriterListUrl,ImageUrl) values (65,'科研成果','科研成果',73,2,',0,73,65,',1,'~/newslist.aspx?sortid={0}|id|','~/newslist.aspx?id={0}|id|','0','1',null,convert(datetime, '2015-12-29 21:47:20', 20),convert(datetime, '2015-12-29 21:47:20', 20),null,null,null,1,0,null,'~/page_newsdetail{0}.html|id|','~/page_newslist{0}.html|id|',null) ;
  insert into UTB_SLOWX_NEWS_SORT (ID,TheName,TheCode,PID,Layer,Path,ShowSeq,ListUrl,DetailUrl,IsDelete,IsShow,Remark,CreateTime,UpdateTime,MetaKeywords,MetaDescription,LinkTarget,EmNewsSortTypeValue,Hits,HitTime,UrlRewriterDetailUrl,UrlRewriterListUrl,ImageUrl) values (66,'通知公告','通知公告',0,1,',0,66,',1,'~/newslist.aspx?sortid={0}|id|','~/newslist.aspx?id={0}|id|','0','1',null,convert(datetime, '2015-12-29 23:07:33', 20),convert(datetime, '2015-12-29 23:07:33', 20),null,null,null,1,0,null,'~/page_newsdetail{0}.html|id|','~/page_newslist{0}.html|id|',null) ;
  

上一条：

下一条：

相关评论

发表评论

类型：	留言 建议 疑问
内容：
	(Alt+Enter)