Beispiel #1
0
        public void Copy(CSV.Log log, DbProvider provider, string prefixion)
        {
            var sqler = new DbFactory(provider).Sqler(0, false);

            foreach (var type in _copy)
            {
                var tabName = type.Name;
                log.Info("复制表", tabName);
                var sName     = tabName;
                var Delimiter = provider.Delimiter;
                if (String.IsNullOrEmpty(Delimiter))
                {
                    log.Info("当前配置不支持复制表数据", tabName);
                    return;
                }
                else
                {
                    switch (Delimiter)
                    {
                    case ".":
                        tabName = String.Format("{0}{1}{2}.{0}{3}{2}", provider.QuotePrefix, provider.Prefixion, provider.QuoteSuffix, tabName);
                        sName   = String.Format("{0}{1}{2}.{0}{3}{2}", provider.QuotePrefix, provider.Prefixion, provider.QuoteSuffix, sName);
                        break;

                    default:
                        tabName = String.Format("{0}{1}{4}{3}{2}", provider.QuotePrefix, provider.Prefixion, provider.QuoteSuffix, tabName, Delimiter);
                        sName   = String.Format("{0}{1}{4}{3}{2}", provider.QuotePrefix, provider.Prefixion, provider.QuoteSuffix, sName, Delimiter);
                        break;
                    }
                }

                var sb = new StringBuilder();
                var ps = type.GetProperties();
                foreach (var property in ps)
                {
                    sb.AppendFormat("{0}{3}{1}", provider.QuotePrefix, provider.Prefixion, provider.QuoteSuffix, property.Name);
                    sb.Append(",");
                }
                sb.Remove(sb.Length - 1, 1);
                try
                {
                    sqler.ExecuteNonQuery(String.Format("INSERT INTO {0}({1})SELECT {1} FROM {2}", tabName, sb, sName));
                }
                catch (Exception ex)
                {
                    log.Error("复制出错", ex.Message);
                }
            }
        }
Beispiel #2
0
        public void CheckPrefix(CSV.Log log, DbProvider provider)
        {
            var builder = provider.Builder;
            var factory = new DbFactory(provider);

            if (builder == null)
            {
                log.Debug("此数据库节点配置器未有管理器");
                return;
            }
            var sqler = factory.Sqler(1, false);

            log.Info("检测前缀", provider.Prefixion);
            var Delimiter = provider.Delimiter;

            if (String.Equals(Delimiter, ".") && String.IsNullOrEmpty(provider.Prefixion) == false)
            {
                var prefixion = String.Format("{0}{1}{2}", provider.QuotePrefix, provider.Prefixion, provider.QuoteSuffix);
                var schemaSQL = builder.Schema(prefixion);
                if (String.IsNullOrEmpty(schemaSQL) == false)
                {
                    try
                    {
                        sqler.ExecuteNonQuery(schemaSQL);
                    }
                    catch (Exception ex)
                    {
                        log.Error(ex.Message);
                    }
                }
            }
        }
Beispiel #3
0
        public void Drop(CSV.Log log, DbProvider provider)
        {
            var factory = new DbFactory(provider);
            var sqler   = factory.Sqler(1, false);

            var em        = dictionary.GetEnumerator();
            var Delimiter = provider.Delimiter;

            while (em.MoveNext())
            {
                var tabName = em.Current.Key.GetType().Name;
                log.Info("删除表", tabName);
                tabName = GetName(provider, tabName);
                var sb = new StringBuilder();
                sb.Append("DROP TABLE ");
                sb.Append(tabName);
                try
                {
                    sqler.ExecuteNonQuery(sb.ToString());
                }
                catch (Exception ex)
                {
                    log.Error(ex.Message);
                }
            }
        }
Beispiel #4
0
        public void Setup(IDictionary args, CSV.Log log, DbProvider provider)
        {
            var builder = provider.Builder;

            if (builder == null)
            {
                log.Debug("此数据库节点配置器未有管理器");
                log.End("操作结束");
                return;
            }
            var factory = new DbFactory(provider);
            var sqler   = factory.Sqler(1, false);

            var em        = dictionary.GetEnumerator();
            var Delimiter = provider.Delimiter;

            log.Info("数据前缀", provider.Prefixion);
            if (String.Equals(Delimiter, ".") && String.IsNullOrEmpty(provider.Prefixion) == false)
            {
                var prefixion = String.Format("{0}{1}{2}", provider.QuotePrefix, provider.Prefixion, provider.QuoteSuffix);
                var schemaSQL = builder.Schema(prefixion);
                if (String.IsNullOrEmpty(schemaSQL) == false)
                {
                    try
                    {
                        sqler.ExecuteNonQuery(schemaSQL);
                    }
                    catch (Exception ex)
                    {
                        log.Error(ex.Message);
                    }
                }
            }

            while (em.MoveNext())
            {
                CreateTable(sqler, provider, log, em.Current.Key, em.Current.Value);
            }
            this.Setup(args, factory);
        }
Beispiel #5
0
        public override void ProcessActivity(WebRequest request, WebResponse response)
        {
            var Settings = this.AsyncDialog("Settings", g =>
            {
                if (request.SendValues != null && request.SendValues.ContainsKey("media_id"))
                {
                    return(this.DialogValue(new WebMeta().Put("media_id", request.SendValues["media_id"])));
                }
                var from = new Web.UIFormDialog()
                {
                    Title = this.Title ?? "文本数据导入"
                };

                from.AddFile("选择csv文件", "media_id", String.Empty).Put("Accept", "text/csv");
                from.AddRadio("文件编码", "Encoding").Put("中文操作系统标准(GB2312)", "gb2312").Put("英文操作系统标准(UTF8)", "utf-8", true);
                var opts = from.AddTextValue("必须列名");
                foreach (var s in Columns)
                {
                    opts.Add(s, "必须字段");
                }
                from.Submit("确认上传", request, "Pager");
                return(from);
            });
            var media_id = Settings["media_id"];

            if (media_id.EndsWith(".csv") == false)
            {
                this.Prompt("请上传csv文本文件");
            }

            var Key      = this.AsyncDialog("Key", Utility.Guid(Guid.NewGuid()));
            var pathFile = this.AsyncDialog("File", g =>
            {
                string path2 = UMC.Data.Utility.MapPath(String.Format("App_Data\\Static\\TEMP\\{0}\\", Utility.GetRoot(request.Url)));

                var filename = String.Format("{0}{1}.tmp", path2, Key);
                Utility.Copy(new UMC.Net.HttpClient().GetStreamAsync(media_id).Result, filename);

                return(this.DialogValue(filename));
            });

            if (pathFile.EndsWith(".csv"))
            {
                var pad = new Uri(request.Url, String.Format("/TEMP/{0}", pathFile));
                this.Context.Send(new Web.WebMeta().Put("type", "OpenUrl").Put("value", pad.AbsoluteUri), true);
            }
            var columnSettings = this.AsyncDialog("Columns", g =>
            {
                var reader = new System.IO.StreamReader(pathFile, Encoding.GetEncoding(Settings["Encoding"] ?? "utf-8"));
                var cinex  = 0;
                var from   = new Web.UIFormDialog()
                {
                    Title = "核对字段"
                };
                var csvColumns = CSV.FromCsvLine(CSV.ReadLine(reader));
                reader.Close();
                foreach (var c in this.Columns)
                {
                    var opts = from.AddSelect(c, "Col_" + cinex).Put("请选择", "");
                    for (var i = 0; i < csvColumns.Length; i++)
                    {
                        opts.Add(String.Format("{1}(第{0}列)", i + 1, csvColumns[i]), i.ToString(), String.Equals(csvColumns[i], c, StringComparison.CurrentCultureIgnoreCase));
                    }
                    cinex++;
                }
                from.Submit("确认通过");
                return(from);
            });
            var indexs   = new List <int>();
            var maxIndex = -1;

            for (var i = 0; i < this.Columns.Length; i++)
            {
                var cindex = Convert.ToInt32(columnSettings["Col_" + i]);
                if (maxIndex < cindex)
                {
                    maxIndex = cindex;
                }
                indexs.Add(cindex);
            }
            if (indexs.Count == 0)
            {
                this.Prompt("无有效的字段");
            }

            var userName = Utility.GetUsername();

            var log = new CSV.Log(Utility.GetRoot(request.Url), Key, String.Format("开始{0}", this.Title ?? "文本数据导入"));

            Data.Reflection.Start(() =>
            {
                int rowIndex = 1;
                var reader   = new System.IO.StreamReader(pathFile, Encoding.GetEncoding(Settings["Encoding"] ?? "utf-8"));
                System.IO.FileStream file = System.IO.File.Open(String.Format("{0}.csv", pathFile), System.IO.FileMode.Create);


                try
                {
                    var writer = new System.IO.StreamWriter(file, Encoding.UTF8);
                    var total  = 0;
                    var now    = DateTime.Now;


                    var header = CSV.ReadLine(reader);
                    writer.WriteLine(header);

                    int okindex = 0;

                    CSV.EachRow(reader, data =>
                    {
                        rowIndex++;
                        if (data.Length <= maxIndex)
                        {
                            log.Error(String.Format("第{0}行 数据无效", rowIndex));
                            if (okindex + 10 < rowIndex)
                            {
                                throw new ArgumentException(String.Format("连续超过10条无效的数据"));
                            }
                            return;
                        }
                        var hash = new Hashtable();
                        for (var i = 0; i < indexs.Count; i++)
                        {
                            int index = indexs[i];
                            if (data.Length > index)
                            {
                                if (String.IsNullOrEmpty(data[index]))
                                {
                                    log.Error(String.Format("第{0}行 {1}列数据为空", rowIndex, this.Columns[i]));
                                    if (okindex + 10 < rowIndex)
                                    {
                                        throw new ArgumentException(String.Format("连续超过10条无效的数据"));
                                    }
                                    return;
                                }
                            }
                            else
                            {
                                log.Error(String.Format("第{0}行 无{1}列数据", rowIndex, this.Columns[i]));
                                if (okindex + 10 < rowIndex)
                                {
                                    throw new ArgumentException(String.Format("连续超过10条无效的数据"));
                                }
                                return;
                            }
                            hash[this.Columns[i]] = data[index];
                        }
                        if (Do(log, rowIndex, hash))
                        {
                            total++;
                            okindex = rowIndex;
                        }
                        else
                        {
                            foreach (var d in data)
                            {
                                UMC.Data.CSV.CSVFormat(writer, d);
                                writer.Write(",");
                            }
                            writer.WriteLine();
                            writer.Flush();
                        }
                        if (okindex + 10 < rowIndex)
                        {
                            new ArgumentException(String.Format("连续超过10条无效的数据"));
                        }
                    });
                    writer.Flush();
                    writer.Close();
                    log.End("导入数据完成");
                    log.Info(String.Format("导入成功{0}条", total));
                    log.Info(String.Format("用时{0}", DateTime.Now - now));
                }
                catch (Exception ex)
                {
                    log.End(String.Format("在{0}行导入失败", rowIndex));
                    log.Info(ex.Message);
                }
                finally
                {
                    file.Close();
                    reader.Close();
                    log.Close();
                }
            });    //.Start();

            this.Context.Send(new UISectionBuilder("System", "Log", new WebMeta("Key", Key))
                              .Builder(), true);
        }
Beispiel #6
0
        void CreateTable(ISqler sqler, DbProvider provider, CSV.Log log, Object key, object value)
        {
            var tabName = key.GetType().Name;

            log.Info("创建表", tabName);

            var keys = CBO.GetProperty(key);
            IDictionary <string, object> textKeys = new Dictionary <string, object>();

            if (value != null)
            {
                if (value.GetType().IsArray == false)
                {
                    textKeys = CBO.GetProperty(value);
                }
            }
            var Delimiter = provider.Delimiter;
            //var provider = sqler.DbProvider;
            var builder = provider.Builder;

            if (String.IsNullOrEmpty(Delimiter))
            {
                tabName = String.Format("{0}{3}{2}", provider.QuotePrefix, provider.Prefixion, provider.QuoteSuffix, tabName);
            }
            else
            {
                if (String.IsNullOrEmpty(provider.Prefixion))
                {
                    tabName = String.Format("{0}{1}{2}", provider.QuotePrefix, tabName, provider.QuoteSuffix);
                }
                else
                {
                    switch (Delimiter)
                    {
                    case ".":
                        tabName = String.Format("{0}{1}{2}.{0}{3}{2}", provider.QuotePrefix, provider.Prefixion, provider.QuoteSuffix, tabName);
                        break;

                    default:
                        tabName = String.Format("{0}{1}{4}{3}{2}", provider.QuotePrefix, provider.Prefixion, provider.QuoteSuffix, tabName, Delimiter);
                        break;
                    }
                }
            }
            var sb = new StringBuilder();

            sb.Append("CREATE TABLE ");
            sb.Append(tabName);
            sb.Append("(");
            var ps = key.GetType().GetProperties();

            foreach (var property in ps)
            {
                var filed = String.Format("{0}{2}{1} ", provider.QuotePrefix, provider.QuoteSuffix, property.Name);

                sb.Append(builder.Column(filed));
                var type = property.PropertyType;
                if (type.IsGenericType)
                {
                    type = type.GetGenericArguments()[0];
                }
                switch (type.FullName)
                {
                case "System.SByte":
                case "System.Byte":
                case "System.Int16":
                case "System.UInt16":
                case "System.Int32":
                case "System.UInt32":
                    sb.Append(builder.Integer());
                    break;

                case "System.Double":
                case "System.Single":
                    sb.Append(builder.Float());
                    break;

                case "System.Int64":
                case "System.UInt64":
                case "System.Decimal":
                    sb.Append(builder.Number());
                    break;

                case "System.Boolean":
                    sb.Append(builder.Boolean());
                    break;

                case "System.DateTime":
                    sb.Append(builder.Date());
                    break;

                case "System.Guid":
                    sb.Append(builder.Guid());
                    break;

                default:
                    if (property.PropertyType.IsEnum)
                    {
                        sb.Append(builder.Integer());
                    }
                    else
                    {
                        if (textKeys.ContainsKey(property.Name))
                        {
                            sb.Append(builder.Text());
                        }
                        else
                        {
                            sb.Append(builder.String());
                        }
                    }
                    break;
                }
                if (keys.ContainsKey(property.Name))
                {
                    sb.Append(" NOT NULL");
                }
                sb.Append(",");
            }
            if (value != null)
            {
                if (value.GetType().IsArray)
                {
                    var fs = (string[])value;
                    foreach (var f in fs)
                    {
                        var filed = String.Format("{0}{2}{1} ", provider.QuotePrefix, provider.QuoteSuffix, f);
                        sb.Append(builder.Column(filed));
                        sb.Append(builder.String());
                        sb.Append(",");
                    }
                }
            }
            sb.Remove(sb.Length - 1, 1);

            sb.Append(")");
            try
            {
                sqler.ExecuteNonQuery(sb.ToString());
            }
            catch (Exception ex)
            {
                log.Error(ex.Message);
            }
            if (keys.Count > 0)
            {
                var ids = new List <String>();
                var m   = keys.GetEnumerator();
                while (m.MoveNext())
                {
                    var filed = String.Format("{0}{1}{2}", provider.QuotePrefix, m.Current.Key, provider.QuoteSuffix);
                    ids.Add(filed);
                }
                var sql = builder.PrimaryKey(tabName, ids.ToArray());
                if (String.IsNullOrEmpty(sql) == false)
                {
                    try
                    {
                        sqler.ExecuteNonQuery(sql);
                    }
                    catch (Exception ex)
                    {
                        log.Error("创建主键" + String.Join(",", ids.ToArray()), ex.Message);
                    }
                }
            }
        }
Beispiel #7
0
        void CheckTable(ISqler sqler, DbProvider provider, CSV.Log log, Object key, object value)
        {
            var tabName = key.GetType().Name;

            log.Info("检测表", tabName);

            var keys = CBO.GetProperty(key);
            IDictionary <string, object> textKeys = new Dictionary <string, object>();

            if (value != null)
            {
                if (value.GetType().IsArray == false)
                {
                    textKeys = CBO.GetProperty(value);
                }
            }
            var builder = provider.Builder;


            tabName = GetName(provider, tabName);
            if (builder.Check(tabName.Replace(provider.QuotePrefix, "").Replace(provider.QuoteSuffix, ""), sqler) == false)
            {
                CreateTable(sqler, provider, log, key, value);
            }
            else
            {
                var ps = key.GetType().GetProperties();
                foreach (var property in ps)
                {
                    var filed  = String.Format("{0}{2}{1} ", provider.QuotePrefix, provider.QuoteSuffix, property.Name);
                    var cfiled = builder.Column(filed);
                    if (builder.Check(tabName.Replace(provider.QuotePrefix, "").Replace(provider.QuoteSuffix, "").Trim(), cfiled.Replace(provider.QuotePrefix, "").Replace(provider.QuoteSuffix, "").Trim(), sqler) == false)
                    {
                        var sb   = new StringBuilder();
                        var type = property.PropertyType;
                        if (type.IsGenericType)
                        {
                            type = type.GetGenericArguments()[0];
                        }
                        switch (type.FullName)
                        {
                        case "System.SByte":
                        case "System.Byte":
                        case "System.Int16":
                        case "System.UInt16":
                        case "System.Int32":
                        case "System.UInt32":
                            sb.Append(builder.AddColumn(tabName, cfiled, builder.Integer()));
                            break;

                        case "System.Double":
                        case "System.Single":
                            sb.Append(builder.AddColumn(tabName, cfiled, builder.Float()));
                            break;

                        case "System.Int64":
                        case "System.UInt64":
                        case "System.Decimal":
                            sb.Append(builder.AddColumn(tabName, cfiled, builder.Number()));
                            break;

                        case "System.Boolean":
                            sb.Append(builder.AddColumn(tabName, cfiled, builder.Boolean()));
                            break;

                        case "System.DateTime":
                            sb.Append(builder.AddColumn(tabName, cfiled, builder.Date()));
                            break;

                        case "System.Guid":
                            sb.Append(builder.AddColumn(tabName, cfiled, builder.Guid()));
                            break;

                        default:
                            if (property.PropertyType.IsEnum)
                            {
                                sb.Append(builder.AddColumn(tabName, cfiled, builder.Integer()));
                            }
                            else
                            {
                                if (textKeys.ContainsKey(property.Name))
                                {
                                    sb.Append(builder.AddColumn(tabName, cfiled, builder.Text()));
                                }
                                else
                                {
                                    sb.Append(builder.AddColumn(tabName, cfiled, builder.String()));
                                }
                            }
                            break;
                        }
                        if (sb.Length > 0)
                        {
                            log.Info("追加表字段", tabName + '.' + cfiled);
                            ExecuteNonQuery(log, sqler, sb.ToString());
                        }
                    }
                }
                if (value != null)
                {
                    if (value.GetType().IsArray)
                    {
                        var fs = (string[])value;
                        foreach (var f in fs)
                        {
                            var filed = builder.Column(String.Format("{0}{2}{1} ", provider.QuotePrefix, provider.QuoteSuffix, f));
                            if (builder.Check(tabName.Replace(provider.QuotePrefix, "").Replace(provider.QuoteSuffix, "").Trim(), filed.Replace(provider.QuotePrefix, "").Replace(provider.QuoteSuffix, "").Trim(), sqler) == false)
                            {
                                log.Info("追加表字段", tabName + '.' + filed);
                                ExecuteNonQuery(log, sqler, builder.AddColumn(tabName, filed, builder.String()));
                            }
                        }
                    }
                }
            }
        }
Beispiel #8
0
        /// <summary>
        /// 还原
        /// </summary>
        public void Restore(CSV.Log log, DbProvider provider, string file)
        {
            SQLiteDbProvider sqlite = new SQLiteDbProvider();

            sqlite.Provider = Provider.Create(provider.Provider.Name, sqlite.GetType().FullName);
            sqlite.Provider.Attributes["db"] = file;
            var       em          = dictionary.GetEnumerator();
            var       Delimiter   = provider.Delimiter;
            DbFactory dsource     = new DbFactory(sqlite);
            DbFactory target      = new DbFactory(provider);
            var       targetSqler = target.Sqler(false);
            var       sourceSqler = dsource.Sqler();

            while (em.MoveNext())
            {
                var value = em.Current.Value;
                //CreateTable(target.Sqler(), sqlite, log, em.Current.Key, em.Current.Value);
                var tabName = em.Current.Key.GetType().Name;
                log.Info("还原表", tabName);
                var fields    = new List <String>();;
                var insertSql = new StringBuilder();
                insertSql.Append("INSERT INTO ");
                insertSql.Append(GetName(provider, tabName));
                insertSql.Append("(");
                foreach (var f in em.Current.Key.GetType().GetProperties())
                {
                    insertSql.AppendFormat("{0}{1}{2},", provider.QuotePrefix, f.Name, provider.QuoteSuffix);
                    fields.Add(f.Name);
                }
                if (value != null)
                {
                    if (value.GetType().IsArray)
                    {
                        var fs = (string[])value;
                        foreach (var f in fs)
                        {
                            insertSql.AppendFormat("{0}{1}{2},", provider.QuotePrefix, f, provider.QuoteSuffix);
                            fields.Add(f);
                        }
                    }
                }
                insertSql.Remove(insertSql.Length - 1, 1);
                insertSql.Append(")VALUES(");
                for (var i = 0; i < fields.Count; i++)
                {
                    insertSql.Append("{");
                    insertSql.Append(i);
                    insertSql.Append("},");
                }
                insertSql.Remove(insertSql.Length - 1, 1);
                insertSql.Append(")");
                targetSqler.ExecuteNonQuery("DELETE FROM " + GetName(provider, tabName));
                sourceSqler.Execute(String.Format("SELECT *FROM {0}", tabName), dr =>
                {
                    //System.Data.Common.DbTransaction transaction = null;
                    targetSqler.Execute(s =>
                    {
                        var valueStrSQL = insertSql.ToString();
                        while (dr.Read())
                        {
                            var values = new List <Object>();

                            for (int d = 0; d < fields.Count; d++)
                            {
                                values.Add(dr[fields[d]]);
                            }
                            s.Reset(valueStrSQL, values.ToArray());
                            return(true);
                        }
                        //if (transaction != null)
                        //{
                        //    transaction.Commit();
                        //}
                        return(false);
                    }, cmd =>
                    {
                        //if (transaction == null)
                        //{
                        //    transaction = cmd.Connection.BeginTransaction();
                        //}
                        cmd.ExecuteNonQuery();
                    });
                });
            }
            dsource.Close();
            target.Close();
        }