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);
}
}
}
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);
}
}
}
}
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);
}
}
}
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);
}
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);
}
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);
}
}
}
}
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()));
}
}
}
}
}
}
/// <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();
}