static string Table_Name = "";
/// <summary>
/// model实体中的字段名相对数据库表添加的字段
/// 如:
/// model中一字段名为cm_tableName,
/// 其中tableName为数据库表名
/// 则此变量的值为cm_
/// </summary>
static string Common_Prefix = "";
/// <summary>
/// 泛型获取SQL INSERT字符串
/// </summary>
/// <typeparam name="T">对象类型</typeparam>
/// <param name="md">对象类型参数</param>
/// <returns>返回SQL INSERT语句；调用方法： </returns>
public static String toSqlInsertBuilder<T>(this T md)
{
//要插入的字段
string SQL_STR_INSERT_FIELDS = "";
//要插入的值
string SQL_STR_INSERT_VALUES = "";
string SQL_STR = "";
//获取当前实例的类型
Type type = md.GetType();
//获取实体名（即表名）
String tableName = type.Name.ToString();
//如果表名还没给值，则把表名赋给它
//Table_Name = str;
//插入语句的头部
String SQL_STR_INSERT_HEADER = "insert into " + tableName + "(";
//根据类型创建一个空的实体model
object obj = Activator.CreateInstance(type);
//获取所有为Public的字段和实例成员（如果有的话）
PropertyInfo[] props = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
//遍历每一个字段
foreach (PropertyInfo p in props)
{
if (p.GetValue(md, null) != null)
{
if (p.Name.ToLower() != "id")
{
//返回传入的实体中此（P）字段的值，如果此值不为空，则进入if方法体内
if (p.GetValue(md, null).ToString().Trim().Length > )
{
SQL_STR_INSERT_FIELDS = SQL_STR_INSERT_FIELDS
//将字段名比数据库字段名多的部分替换掉
//.Replace(Common_Prefix.ToLower().ToString(), "")
+ p.Name.ToLower().ToString() + ",";
SQL_STR_INSERT_VALUES = SQL_STR_INSERT_VALUES + "', '"
+ p.GetValue(md, null).ToString().Trim();
}
}
}
}
//
if (SQL_STR_INSERT_FIELDS.ToString().Trim().Length > )
{
SQL_STR = SQL_STR_INSERT_HEADER
+ SQL_STR_INSERT_FIELDS.Substring(, SQL_STR_INSERT_FIELDS.Length - )
+ ")values(" + SQL_STR_INSERT_VALUES.Substring(, SQL_STR_INSERT_VALUES.Length - )
+ "');";
}
return SQL_STR;
}
public static String SqlInsertBuilder(DataSet Ds)
{
DataTable dt = new DataTable();
if (Ds != null)
{
if (Ds.Tables.Count > )
{
dt = Ds.Tables[];
}
}
String SQL_STR_INSERT_HEADER = "insert into mytablename(";
string SQL_STR_INSERT_FIELDS = "";
string SQL_STR_INSERT_VALUES = "";
string SQL_STR = "";
if (dt.Rows.Count > )
{
SQL_STR = "";
foreach (DataRow dr in dt.Rows)
{
SQL_STR_INSERT_HEADER = "insert into mytablename(";
SQL_STR_INSERT_HEADER = SQL_STR_INSERT_HEADER.Replace("mytablename", Table_Name.ToString());
SQL_STR_INSERT_FIELDS = "";// SQL_STR_INSERT_FIELDS + dc.ColumnName.ToLower().ToString() + ",";
SQL_STR_INSERT_VALUES = "";//SQL_STR_INSERT_VALUES + "', '" + dr[dc.ColumnName].ToString().Trim();
foreach (DataColumn dc in dt.Columns)
{
if (dr[dc.ColumnName].ToString().Trim().Length > )
{
SQL_STR_INSERT_FIELDS = SQL_STR_INSERT_FIELDS
+ dc.ColumnName.ToLower().ToString() + ",";
SQL_STR_INSERT_VALUES = SQL_STR_INSERT_VALUES
+ "', '" + dr[dc.ColumnName].ToString().Trim();
}
}
if (SQL_STR_INSERT_FIELDS.ToString().Trim().Length > )
{
SQL_STR = SQL_STR + SQL_STR_INSERT_HEADER
+ SQL_STR_INSERT_FIELDS.Substring(, SQL_STR_INSERT_FIELDS.Length - )
+ ")values(" + SQL_STR_INSERT_VALUES.Substring(, SQL_STR_INSERT_VALUES.Length - )
+ "');";
}
}
}
return SQL_STR;
}
public static String SqlInsertBuilder(DataTable dt)
{
String SQL_STR_INSERT_HEADER = "insert into mytablename(";
string SQL_STR_INSERT_FIELDS = "";
string SQL_STR_INSERT_VALUES = "";
string SQL_STR = "";
if (dt.Rows.Count > )
{
SQL_STR = "";
foreach (DataRow dr in dt.Rows)
{
SQL_STR_INSERT_HEADER = "insert into mytablename(";
SQL_STR_INSERT_HEADER = SQL_STR_INSERT_HEADER.Replace("mytablename", Table_Name.ToString());
SQL_STR_INSERT_FIELDS = "";
SQL_STR_INSERT_VALUES = "";
foreach (DataColumn dc in dt.Columns)
{
if (dr[dc.ColumnName].ToString().Trim().Length > )
{
SQL_STR_INSERT_FIELDS = SQL_STR_INSERT_FIELDS
+ dc.ColumnName.ToLower().ToString() + ",";
SQL_STR_INSERT_VALUES = SQL_STR_INSERT_VALUES
+ "', '" + dr[dc.ColumnName].ToString().Trim();
}
}
if (SQL_STR_INSERT_FIELDS.ToString().Trim().Length > )
{
SQL_STR = SQL_STR + SQL_STR_INSERT_HEADER
+ SQL_STR_INSERT_FIELDS.Substring(, SQL_STR_INSERT_FIELDS.Length - )
+ ")values(" + SQL_STR_INSERT_VALUES.Substring(, SQL_STR_INSERT_VALUES.Length - )
+ "');";
}
}
}
return SQL_STR;
}