/// <summary>
/// 生成Insert语句
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="dr">数据</param>
/// <returns></returns>
public static string CreateInsertSql(string tableName, DataRow dr)
{
List <string> strFields = new List <string>();
List <string> strValues = new List <string>();
foreach (var pair in DbTool.GetColumns(dr))
{
strFields.Add(string.Format("[{0}]", pair));
strValues.Add(dr[pair] == DBNull.Value ? "null" : string.Format("'{0}'", dr[pair].ToString().Replace("'", "''")));
}
string insertSql = string.Format("Insert Into [{0}]({1}) values({2})",
tableName, string.Join(",", strFields.ToArray()), string.Join(",", strValues.ToArray()));
return(insertSql);
}
/// <summary>
/// 获取表的所有列，并且优先排主键
/// </summary>
/// <param name="dt">表</param>
/// <returns></returns>
//public static List<string> GetColumns(TableSchema dt)
//{
// var list = dt.PrimaryKeys.ToArray().Select(c => c.Name).ToList();
// list.AddRange(dt.NonPrimaryKeyColumns.ToArray().Select(c => c.Name));
// return list;
//}
/// <summary>
/// 获取表的所有列，并且优先排主键
/// </summary>
/// <param name="ds">数据库</param>
/// <param name="tableName">表名</param>
/// <param name="owner">表来源</param>
/// <returns></returns>
//public static List<string> GetColumns(DatabaseSchema ds, string tableName, string owner)
//{
// TableSchema dt = new TableSchema(ds, tableName, owner, DateTime.MinValue);
// return GetColumns(dt);
//}
/// <summary>
/// 获取表的所有列，并且优先排主键
/// </summary>
/// <param name="conStr">连接字符串</param>
/// <param name="tableName">表名</param>
/// <param name="owner">表来源</param>
/// <returns></returns>
//public static List<string> GetColumns(string conStr, string tableName, string owner)
//{
// return GetColumns(conStr, tableName, owner, PbDbType.Sql);
//}
/// <summary>
/// 获取表的所有列，并且优先排主键
/// </summary>
/// <param name="conStr">连接字符串</param>
/// <param name="tableName">表名</param>
/// <param name="owner">表来源</param>
/// <param name="type">数据类型</param>
/// <returns></returns>
//public static List<string> GetColumns(string conStr, string tableName, string owner, PbDbType type)
//{
// DatabaseSchema ds;
// if (type == PbDbType.Sql)
// ds = new DatabaseSchema(new SqlSchemaProvider(), conStr);
// else
// ds = new DatabaseSchema(new ADOXSchemaProvider(), conStr);
// return GetColumns(ds, tableName, owner);
//}
#endregion
#region 获取sql语句的参数值
/// <summary>
/// 获取sql语句的参数值
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
//public static List<DbParameter[]> GetParemeters(TableSchema dt)
//{
// var list = new List<DbParameter[]>();
// var cls = DbTool.GetColumns(dt);
// var dtcls = dt.Columns;
// foreach (DataRow dr in dt.GetTableData().Rows)
// {
// var dp = new List<DbParameter>();
// foreach (string cl in cls)
// {
// dp.Add(new SqlParameter(cl, dr[cl]));
// }
// list.Add(dp.ToArray());
// }
// return list;
//}
/// <summary>
/// 获取sql语句的参数值
/// </summary>
/// <param name="dt"></param>
/// <returns></returns>
public static List <DbParameter[]> GetParemeters(DataTable dt)
{
var list = new List <DbParameter[]>();
var cls = DbTool.GetColumns(dt);
foreach (DataRow dr in dt.Rows)
{
var dp = new List <DbParameter>();
foreach (string cl in cls)
{
dp.Add(new SqlParameter(cl, dr[cl]));
}
list.Add(dp.ToArray());
}
return(list);
}
/// <summary>
/// 生成带参数的Insert语句
/// </summary>
/// <param name="tableName"></param>
/// <param name="dt"></param>
/// <returns></returns>
public static string CreateInsertSqlWithParameter(string tableName, DataTable dt)
{
var cls = DbTool.GetColumns(dt);
return(CreateInsertSqlWithParameter(tableName, cls));
}