using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
namespace BlueWhale.Web.DataSynchro.Web.U8.ComonDBHelper
{
public class SqlBuilderHelper
{
/// <summary>
/// Insert SQL语句
/// </summary>
/// <param name="obj">要转换的对象,不可空</param>
/// <param name="tableName">要添加的表明,不可空</param>
/// <returns>
/// 空
/// sql语句
/// </returns>
public static string InsertSql<T>(T t, string tableName) where T : class
{
if (t == null || string.IsNullOrEmpty(tableName))
{
return string.Empty;
}
string columns = GetColmons(t);
if (string.IsNullOrEmpty(columns))
{
return string.Empty;
}
string values = GetValues(t);
if (string.IsNullOrEmpty(values))
{
return string.Empty;
}
StringBuilder sql = new StringBuilder();
sql.Append("Insert into " + tableName);
sql.Append("(" + columns + ")");
sql.Append(" values(" + values + ")");
return sql.ToString();
}
/// <summary>
/// BulkInsert SQL语句(批量添加)
/// </summary>
/// <typeparam name="T">类型</typeparam>
/// <param name="objs">要转换的对象集合,不可空</param>
/// <param name="tableName">>要添加的表明,不可空</param>
/// <returns>
/// 空
/// sql语句
/// </returns>
public static string BulkInsertSql<T>(List<T> objs, string tableName) where T : class
{
if (objs == null || objs.Count == 0 || string.IsNullOrEmpty(tableName))
{
return string.Empty;
}
string columns = GetColmons(objs[0]);
if (string.IsNullOrEmpty(columns))
{
return string.Empty;
}
string values = string.Join(",", objs.Select(p => string.Format("({0})", GetValues(p))).ToArray());
StringBuilder sql = new StringBuilder();
sql.Append("Insert into " + tableName);
sql.Append("(" + columns + ")");
sql.Append(" values " + values + "");
return sql.ToString();
}
/// <summary>
/// 获得类型的列名
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
private static string GetColmons<T>(T obj)
{
if (obj == null)
{
return string.Empty;
}
return string.Join(",", obj.GetType().GetProperties().Select(p => p.Name).ToList());
}
/// <summary>
/// 获得值
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
private static string GetValues<T>(T obj)
{
if (obj == null)
{
return string.Empty;
}
return string.Join(",", obj.GetType().GetProperties().Select(p => string.Format("'{0}'", p.GetValue(obj))).ToArray());
}
public static string InsertSql<T>(T objmodel, string tableName, string columns)
{
//获取model中的属性
System.Text.StringBuilder instr = new System.Text.StringBuilder();
System.Text.StringBuilder invalues = new System.Text.StringBuilder();
System.Text.StringBuilder sqlinstr = new System.Text.StringBuilder();
Type t = objmodel.GetType();//获得该类的Type
//再用Type.GetProperties获得PropertyInfo[],然后就可以用foreach 遍历了
//foreach (PropertyInfo pi in t.GetProperties())
//{
// object value1 = pi.GetValue(objmodel, null);//用pi.GetValue获得值
// string name = pi.Name;//获得属性的名字,后面就可以根据名字判断来进行些自己想要的操作
// //进行你想要的操作
//}
foreach (string column in columns.Split(','))
{
System.Reflection.PropertyInfo pi = t.GetProperty(column);
if (pi != null)
{
instr.Append(string.Format(" {0},", column));
object value1 = pi.GetValue(objmodel, null);
if (value1 == null || value1.ToString().ToLower()=="null")
invalues.Append(string.Format(" null,"));
else
invalues.Append(string.Format(" '{0}',", value1));
}
}
sqlinstr.Append(string.Format("insert into {0} ({1}) values(", tableName, instr.ToString().Substring(0, instr.Length - 1)));
sqlinstr.Append(string.Format(" {0}) ", invalues.ToString().Substring(0, invalues.ToString().Length - 1)));
return sqlinstr.ToString();
}
public static string UpdateSql<T>(T objmodel, string tableName, string columns, string keys)
{
//获取model中的属性
System.Text.StringBuilder instr = new System.Text.StringBuilder();
System.Text.StringBuilder wherestr = new System.Text.StringBuilder();
System.Text.StringBuilder sqlinstr = new System.Text.StringBuilder();
Type t = objmodel.GetType();//获得该类的Type
//再用Type.GetProperties获得PropertyInfo[],然后就可以用foreach 遍历了
//foreach (PropertyInfo pi in t.GetProperties())
//{
// object value1 = pi.GetValue(objmodel, null);//用pi.GetValue获得值
// string name = pi.Name;//获得属性的名字,后面就可以根据名字判断来进行些自己想要的操作
// //进行你想要的操作
//}
foreach (string column in columns.Split(','))
{
System.Reflection.PropertyInfo pi = t.GetProperty(column);
if (pi != null)
{
if (keys.IndexOf(column) > -1)
continue;
object value1 = pi.GetValue(objmodel, null);
if (value1 == null || value1.ToString().ToLower()=="null")
instr.Append(string.Format(" {0}=null,",column));
else
instr.Append(string.Format(" {0} = '{1}' ,", column, value1));
}
}
sqlinstr.Append(string.Format("Update {0} set {1} ", tableName, instr.ToString().Substring(0, instr.Length - 1)));
wherestr.Append(" 1=1 ");
foreach (string pk in keys.Split(','))
{
System.Reflection.PropertyInfo pi = t.GetProperty(pk);
if (pi != null)
{
object value1 = pi.GetValue(objmodel, null);
wherestr.Append(string.Format(" AND {0} = '{1}' ", pk, value1));
}
}
return string.Format(" {0} where {1} ", sqlinstr.ToString(), wherestr);
}
}
}