/// <summary> /// 插入或更新 /// </summary> /// <param name="table"></param> /// <param name="insertParam">insertParam为null不插入</param> /// <param name="updateParam">updateParam为null不更新</param> /// <param name="whereParam"></param> /// <returns></returns> public static SqlCommand InsertOrUpdate(string table, object insertParam, object updateParam, object whereParam) { var wps = HelperCommon.GetProperty(whereParam.GetType()).Select(p => p.Name); string Sql; Sql = string.Format("MERGE INTO {0} A USING (SELECT {1}) B ON {2}" , table , string.Join(",", wps.Select(p => p + "=@2_" + p)) , string.Join(" and ", wps.Select(p => "A." + p + "=B." + p))); if (insertParam != null) { var ips = HelperCommon.GetProperty(insertParam.GetType()).Select(p => p.Name); //插入 Sql += string.Format(" WHEN NOT MATCHED THEN INSERT ({0})VALUES({1})" , string.Join(",", ips.Select(p => p)) , string.Join(",", ips.Select(p => "@0_" + p))); } if (updateParam != null) { var ups = HelperCommon.GetProperty(updateParam.GetType()).Select(p => p.Name); //更新 Sql += string.Format(" WHEN MATCHED THEN UPDATE SET {0}" , string.Join(",", ups.Select(p => p + "=@1_" + p))); } //结束标记 Sql += ";"; return(new SqlCommand(Sql, insertParam, updateParam, whereParam)); }
public static SqlCommand Update <T, TAtt>(T tableParam, string where, object whereParam, string[] Exclude = null, string[] Include = null) where TAtt : Attribute { Dictionary <PropertyInfo, TAtt> dic = HelperCommon.GetPropertyAttribute <TAtt>(typeof(T), Exclude, Include); var wps = HelperCommon.GetProperty(whereParam.GetType()).Select(p => p.Name); if (string.IsNullOrWhiteSpace(where)) { where = string.Join(",", wps.Select(p => p + "=@" + p)); } if (dic.Count == 0) { return(null); } List <string> ps; if (typeof(TAtt) == typeof(SDAttribute)) { ps = dic.Where(p => ((p.Value as SDAttribute).Action & SDAction.Update) == SDAction.Update).Select(p => p.Key.Name).ToList(); } else { ps = dic.Keys.Select(p => p.Name).ToList(); } string Sql = $@"update {typeof(T).Name} set {string.Join(",", ps.Select(p => p + "=@" + p))} where {where}"; return(new SqlCommand(Sql, tableParam, whereParam)); }
/// <summary> /// 插入或更新 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="tableParam"></param> /// <param name="where"></param> /// <param name="action"></param> /// <returns></returns> public static SqlCommand InsertOrUpdate <T>(T tableParam, string where, SDAction action = SDAction.Insert | SDAction.Update) { var ps = HelperCommon.GetProperty(typeof(T)).Select(p => p.Name); string s1 = string.Join(",", ps); string s2 = string.Join(",", ps.Select(p => "@" + p)); string s3 = string.Join(",", ps.Select(p => p + "=@" + p)); string Sql; Sql = string.Format("MERGE INTO {0} A USING (SELECT {1}) B ON {2}" , typeof(T).Name , s3 , where); if ((action & SDAction.Insert) == SDAction.Insert) { //插入 Sql += string.Format(" WHEN NOT MATCHED THEN INSERT ({0})VALUES({1})" , s1 , s2); } if ((action & SDAction.Update) == SDAction.Update) { //更新 Sql += string.Format(" WHEN MATCHED THEN UPDATE SET {0}" , s3); } //结束标记 Sql += ";"; return(new SqlCommand(Sql, tableParam)); }
public static SqlCommand Insert <T>(T tableParam, string[] Exclude = null, string[] Include = null) { var ps = HelperCommon.GetProperty(typeof(T), Exclude, Include).Select(p => p.Name); string Sql = $@"insert into {typeof(T).Name} ({string.Join(",", ps)}) values({string.Join(",", ps.Select(s => $"@{s}"))})"; return(new SqlCommand(Sql, tableParam)); }
public static SqlCommand Insert(string table, object tableParam, string[] exclude = null, string[] include = null) { var ps = HelperCommon.GetProperty(tableParam.GetType(), exclude, include).Select(s => s.Name); string sql = $@"Insert into {table} ({string.Join(",", ps)}) values({string.Join(",", ps.Select(s => $"@{s}"))})"; return(new SqlCommand(sql, tableParam)); }
public static SqlCommand Update <T>(T tableParam, string where, object whereParam, string[] Exclude = null, string[] Include = null) { var pps = HelperCommon.GetProperty(typeof(T), Exclude, Include).Select(p => p.Name); var wps = HelperCommon.GetProperty(whereParam.GetType()).Select(p => p.Name); if (string.IsNullOrEmpty(where)) { where = string.Join(",", wps.Select(p => p + "=@" + p)); } string Sql = $@"update {typeof(T).Name} set {string.Join(",", pps.Select(p => p + "=@" + p))} where {where}"; return(new SqlCommand(Sql, tableParam, whereParam)); }
public static List <SqlCommand> Insert(string table, IEnumerable arrayParam, string[] Exclude = null, string[] Include = null) { int maxParamSize = 2099; List <SqlCommand> rs = new List <SqlCommand>(); Dictionary <string, List <object> > dic = new Dictionary <string, List <object> >(); foreach (var item in arrayParam) { if (item == null) { continue; } var ps = HelperCommon.GetProperty(item.GetType(), Exclude, Include).Select(p => p.Name); if (ps.Count() == 0) { continue; } string key = string.Join(",", ps); if (!dic.ContainsKey(key)) { dic.Add(key, new List <object>()); } dic[key].Add(item); } foreach (var item in dic) { var col = item.Key; var list = item.Value; var ps = col.Split(','); int batchSize = (int)Math.Floor(maxParamSize * 1.0 / ps.Length); while (list.Count > 0) { int index = -1; var arr = list.Take(batchSize); string Sql = $@"insert into {table} ({col}) values({string.Join(",", arr.Select(s => { index++; return $"({string.Join(",", ps.Select(m => $"@{index.ToString() + SqlHelper.specialChar + m}"))})"; }))})"; rs.Add(new SqlCommand(Sql, arr.ToArray())); list.RemoveRange(0, (new int[] { list.Count, batchSize }).Min()); } } return(rs); }
public static SqlCommand Insert <T, TAtt>(T tableParam, string[] Exclude = null, string[] Include = null) where TAtt : Attribute { Dictionary <PropertyInfo, TAtt> dic = HelperCommon.GetPropertyAttribute <TAtt>(typeof(T), Exclude, Include); if (dic.Count == 0) { return(null); } List <string> ps; if (typeof(TAtt) == typeof(SDAttribute)) { ps = dic.Where(p => ((p.Value as SDAttribute).Action & SDAction.Insert) == SDAction.Insert).Select(p => p.Key.Name).ToList(); } else { ps = dic.Keys.Select(p => p.Name).ToList(); } string Sql = $@"insert into {typeof(T).Name} ({string.Join(",", ps)}) values ({string.Join(",", ps.Select(s => $"@{s}"))})"; return(new SqlCommand(Sql, tableParam)); }
private Dictionary <string, object> DefinitionParams() { var dicParams = new Dictionary <string, object>(); var matchs = paramReg.Matches(this.CommandText); Dictionary <string, Match> paramList = new Dictionary <string, Match>(); foreach (Match item in matchs) { string key = item.Value.ToUpper(); paramList.Add(key, item); } if (this.Params != null && !string.IsNullOrEmpty(this.CommandText)) { int index = -1; foreach (var pm in this.Params) { index++; if (pm == null) { continue; } if (pm is IDictionary) { var pps = (IDictionary)pm; foreach (var key in pps.Keys) { string n1 = "@" + key; var value = pps[key] ?? DBNull.Value; if (paramList.Keys.Contains(n1.ToUpper())) { dicParams[n1] = value; } string n2 = "@" + index.ToString() + SqlHelper.specialChar + key; if (paramList.Keys.Contains(n2.ToUpper())) { dicParams[n2] = value; } } } else { Type type = pm.GetType(); var pps = HelperCommon.GetProperty(type); foreach (var pp in pps) { string n1 = "@" + pp.Name; var value = pp.GetValue(pm) ?? DBNull.Value; if (paramList.Keys.Contains(n1.ToUpper())) { if (value.GetType().IsArray) { var vv = (IEnumerable)value; int m = -1; List <string> names = new List <string>(); foreach (var v in vv) { m++; string name = n1 + SqlHelper.specialChar + m; dicParams[name] = v; names.Add(name); } var match = paramList[n1.ToUpper()]; this.CommandText = this.CommandText.Replace(match.Index, match.Length, string.Join(",", names)); } else { dicParams[n1] = value; } } string n2 = "@" + index.ToString() + SqlHelper.specialChar + pp.Name; if (paramList.Keys.Contains(n2.ToUpper())) { if (value.GetType().IsArray) { var vv = (IEnumerable)value; int m = -1; List <string> names = new List <string>(); foreach (var v in vv) { m++; string name = n2 + SqlHelper.specialChar + m; dicParams[name] = v; names.Add(name); } var match = paramList[n2.ToUpper()]; this.CommandText = this.CommandText.Replace(match.Index, match.Length, string.Join(",", names)); } else { dicParams[n2] = value; } } } } } } return(dicParams); }