Esempio n. 1
0
        /// <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);
        }
Esempio n. 2
0
        /// <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);
        }
Esempio n. 3
0
        /// <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));
        }