填入表名列名条件自动生成SQL语句的工具?

发布网友 发布时间：2022-04-24 09:24

我来回答

共2个回答

懂视网 时间：2022-05-03 03:43

using System; 2 using System.Collections.Generic; 3 using System.IO; 4 using System.Linq; 5 using System.Text; 6 using System.Threading.Tasks; 7 using Newtonsoft.Json.Serialization; 8 using Newtonsoft.Json.Linq; 9 using Newtonsoft.Json.Converters; 10 using Newtonsoft.Json.Utilities; 11 using Newtonsoft.Json; 12 13 namespace ConsolePro 14 { 15 public class FileHelper 16 { 17 #region txt 给txt 文本 批量加‘,‘ 作为sql查询条件 18 public static void CreateSQLContidion() 19 { 20 var filePath = "D:\rt.txt"; 21 int TotalCountInEveryFile = 10000; 22 List<string> gotStrings = GetStreamMethod(filePath); 23 //Console.WriteLine(string.Join(" ", gotStrings.ToArray())); 24 if (gotStrings != null) 25 { 26 int fileCount = 1; 27 for (int i = 0; i < gotStrings.Count; i++) 28 { 29 StringBuilder sb = new StringBuilder(); 30 sb.AppendFormat("{0} ", ("‘" + gotStrings[i].Trim() + "‘,")); 31 if (i + 1 < gotStrings.Count) 32 { 33 i++; 34 } 35 else 36 { 37 WriteStreamMethod(string.Format("d:\Document{0}.txt", fileCount), sb.ToString()); 38 return; 39 } 40 while (i % TotalCountInEveryFile != 0) 41 { 42 sb.AppendFormat("{0} ", ("‘" + gotStrings[i].Trim() + "‘,")); 43 if (i != gotStrings.Count - 1) 44 { 45 i++; 46 } 47 else 48 { 49 WriteStreamMethod(string.Format("d:\Document{0}.txt", fileCount), sb.ToString()); 50 return; 51 } 52 } 53 //Console.WriteLine(sb.ToString()); 54 WriteStreamMethod(string.Format("d:\Document{0}.txt", fileCount), sb.ToString()); 55 if (i % TotalCountInEveryFile == 0) 56 { 57 i--; 58 } 59 fileCount++; 60 } 61 } 62 } 63 #endregion 65 public static List<string> GetStreamMethod(string path) 66 { 67 List<string> list = new List<string>(); 68 StreamReader sr = new StreamReader(path); 69 String line; 70 while ((line = sr.ReadLine()) != null) 71 { 72 list.Add(line.ToString()); 73 } 74 return list; 75 } 76 77 public static string GetStrMethod(string path) 78 { 79 StringBuilder list = new StringBuilder(); 80 StreamReader sr = new StreamReader(path); 81 String line; 82 while ((line = sr.ReadLine()) != null) 83 { 84 list.Append(line.ToString()); 85 } 86 return list.ToString(); 87 } 88 public static void WriteStreamMethod(string path, string content) 90 { 91 FileStream fs = new FileStream(path, FileMode.Create); 92 StreamWriter sw = new StreamWriter(fs); 93 sw.Write(content); 94 sw.Flush(); 95 sw.Close(); 96 fs.Close(); 97 } 98 99 public static string GetCustomNo() 100 { 101 var filePath = "D:\cus.txt"; 102 string gotStrings = GetStrMethod(filePath); 103 var jsonParse = gotStrings.ToString(); 104 //JObject JsonObj = JObject.Parse(jsonParse); 105 JArray list = JArray.Parse(jsonParse); 106 IList<JToken> delList = new List<JToken>(); 107 List<string> tempStr = new List<string>(); 108 StringBuilder s = new StringBuilder(); 109 foreach (var ss in list) //查找某个字段与值 110 { 111 if (((JObject)ss)["CustosNo"].ToString() != "aa") 112 //tempStr.Add(((JObject)ss)["CustosNo"].ToString()); 113 s.Append("‘"+((JObject)ss)["CustosNo"].ToString()+"‘,"); 114 //delList.Add(ss); 115 } 116 117 //var purchaseInfoes = JsonConvert.DeserializeObject<string>(JsonObj["CustosNo"].ToString()); 118 return s.ToString().TrimEnd(‘,‘); 119 120 } 121 } 122 } 文件实现

 

SQL查询条件生成小工具

标签：sys   span   ade   tostring   字符串   src   gen   custom   AMM   

热心网友 时间：2022-05-03 00:51

ALTER proc [dbo].[proc_insert] (@tablename varchar(256),@where varchar(max))
as
begin
set nocount on
declare @sqlstr varchar(MAX)
declare @sqlstr1 varchar(MAX)
declare @sqlstr2 varchar(MAX)
select @sqlstr='select ''INSERT '+@tablename
select @sqlstr1=''
select @sqlstr2=' ('
select @sqlstr1= ' VALUES ( ''+'
select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from (select case
-- when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype =36 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =127 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(20),'+a.name +')'+' end'
when a.xtype =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end'
when a.xtype =175 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =61 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
when a.xtype =106 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
when a.xtype =62 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
when a.xtype =56 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'
when a.xtype =60 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
when a.xtype =239 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =108 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =59 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
when a.xtype =58 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
when a.xtype =52 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'
when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
when a.xtype =48 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end'
-- when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype =167 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
else '''NULL'''
end as col,a.colid,a.name
from syscolumns a
where a.id = object_id(@tablename) and a.xtype <>1 and a.xtype <>34 and a.xtype <>35 -- and a.xtype <>36
AND COLUMNPROPERTY(a.id, a.name, 'IsIdentity') <> 1
)t order by colid
select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename + ' where '+@where
-- print @sqlstr
exec(@sqlstr)
set nocount off
end