构造SQL语句两种方法

  #region "构造SQL语句"
  //通过数组
  public string CreateSqlByArry(string TableName,object []Ary,string type)
  {
   string Sql="";
   
   switch(type)
   {
    case "update":
    {
     if(Ary.Length % 2>0)
     {
      return Sql="Fileds,Values Not Matching";
     }
     for(int i=0;i<Ary.Length;i=i+2)
     {
      if(Ary[i+1].GetType().Name=="String" || Ary[i+1].GetType().Name=="DateTime" || Ary[i+1].GetType().Name=="Date")
      {
       Sql = Sql + "," + Ary[i].ToString() + "='" + Ary[i+1].ToString()+"'";
      }
      else
      {
       Sql = Sql + "," +Ary[i].ToString() + "=" + Ary[i+1].ToString();
      }
     }
     Sql=Sql.Remove(0,1);
     Sql= "Update " + TableName + " Set " + Sql;
     break;
    }
    case "insert":
    {
  
     string Field="",Value="";
   
     if(Ary.Length%2>0)
     {
      return Sql="Fileds,Values Not Matching";
     }
   
     for(int i=0;i<Ary.Length;i=i+2)
     {
      if(Ary[i+1].GetType().Name=="String" || Ary[i+1].GetType().Name=="DateTime" || Ary[i+1].GetType().Name=="Date")
      {
       Field = Field + "," + Ary[i].ToString();
       Value = Value + ",'" + Ary[i+1].ToString() + "'";
      }
      else
      {
       Field = Field + "," + Ary[i].ToString();
       Value = Value + "," + Ary[i+1].ToString();
      }
     }
     Field=Field.Remove(0,1);
     Value=Value.Remove(0,1);
     Sql= "Insert Into " + TableName + "(" + Field + ")Values(" + Value + ")" ;
     break;
    }
   }
   return Sql;
   
  }
  

  /// <summary>
  /// //通过Xml
  /// </summary>
  /// <param name="strXml"></param>
  /// <param name="type"></param>
  /// <param name="flag">当flag=="1"时insert,updat 语句值为字符串</param>
  /// <returns></returns>
  public string CreateSqlByXml(string strXml,string type,string flag)
  {
   string Sql="";
   switch(type)
   { 
    case "insert":
    {
     return GetInsertSQL( strXml, flag);
     
    }
    case "update":
    {
     return GetUpdateSQL( strXml, flag);
     
    }   
   }
   return Sql;
  }

  public string GetInsertSQL(string strXML,string flag)
  {
   string SQL = "",str1="",str2="";
   XmlDocument doc = new XmlDocument();
   doc.LoadXml(strXML);
   string first_str = doc.DocumentElement.Name;
   SQL = "insert into "+first_str;
   foreach(XmlNode xn in doc.FirstChild)
   {
    str1 = str1.Length>0?str1+","+xn.Name:xn.Name;
    if(flag.Equals("1"))
    {
     str2 = str2.Length>0?str2+",'"+xn.InnerText+"'":"'"+xn.InnerText+"'";
    }
    else
    {
     str2 = str2.Length>0?str2+","+xn.InnerText:xn.InnerText;
    }
   }
   SQL =SQL+ "("+str1+")values("+str2+")\nselect @@IDENTITY";
   return SQL;
  }

  public string GetUpdateSQL(string strXML,string flag)
  {
   string SQL="",str1="";
   XmlDocument doc = new XmlDocument();
   doc.LoadXml(strXML);
   string first_str = doc.DocumentElement.Name;
   SQL = "update "+first_str+" set ";
   foreach(XmlNode xn in doc.FirstChild)
   {
    if(flag.Equals("1"))
    {
     str1 = str1.Length>0?str1+","+xn.Name+"='"+xn.InnerText+"'":xn.Name+"='"+xn.InnerText+"'";
    }
    else
    {
     str1 = str1.Length>0?str1+","+xn.Name+"="+xn.InnerText:xn.Name+"="+xn.InnerText;
    }
   }
   SQL = SQL+str1;
   return SQL;
  }

  #endregion  

转载于:https://www.cnblogs.com/discoverx/archive/2007/04/25/726382.html