好消息,
程序员专用早餐机。和掌柜说 ideaam,可以节省20元。 点击链接 或復·制这段描述¥k3MbbVKccMU¥后到淘♂寳♀
或者 淘宝扫码 支持下同行哈
----------------------------------------------------------------------------------------------------------------------------
[TestClass]
public class UnitTest1
{
[TestMethod]
public void TestMethod1()
{
using (testEntities entity = new testEntities())
{
var tableName = "test2";
var json = "{\"name\":\"hello\", \"age\":1, \"createtime\":\"2012-04-23T18:25:43.511Z\", object:{\"column1\":\"test\", \"column2\":\"test\"}}";
var jsonEntity = JsonConvert.DeserializeObject<Newtonsoft.Json.Linq.JObject>(json);
var tableList = entity.Database.SqlQuery<MysqlTableSchema>("show tables").ToListAsync();
tableList.Wait();
var isExistsTable = tableList.Result.Where(r => r.tables_in_test.ToLower() == tableName.ToLower()).Count() > 0;
if (!isExistsTable)
{
entity.Database.ExecuteSqlCommand("CREATE TABLE `" + tableName + "` (`id` INT NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`));");
}
CreateColumns(tableName, jsonEntity);
}
}
private static void CreateColumns(string tableName, JObject jsonEntity, string columPrefix = "")
{
using (testEntities entity = new testEntities())
{
var columList = entity.Database.SqlQuery<MysqlColumSchema>("DESCRIBE " + tableName).ToListAsync();
columList.Wait();
foreach (var item in jsonEntity.Properties())
{
var columName = string.IsNullOrEmpty(columPrefix) ? item.Name.ToLower() : columPrefix + "_" + item.Name.ToLower();
var isExists = columList.Result.Where(e => e.field.ToLower().Equals(columName)).ToList().Count() > 0;
if (!isExists)
{
var mysqlType = "";
var index = "";
switch (item.Value.Type)
{
case Newtonsoft.Json.Linq.JTokenType.Object:
CreateColumns(tableName, item.Value.ToObject<JObject>(), columPrefix = item.Name.ToLower());
continue;
case Newtonsoft.Json.Linq.JTokenType.Boolean:
mysqlType = "nvarchar(1000) ";
index = "ALTER TABLE " + tableName + " add index INDEX_" +columName+ " (" +columName+ ");";
break;
case Newtonsoft.Json.Linq.JTokenType.Bytes:
break;
case Newtonsoft.Json.Linq.JTokenType.Comment:
mysqlType = "nvarchar(1000) ";
index = "ALTER TABLE " + tableName + " add index INDEX_" +columName+ " (" +columName+ ");";
break;
case Newtonsoft.Json.Linq.JTokenType.Date:
mysqlType = "datetime NULL DEFAULT CURRENT_TIMESTAMP";
index = "ALTER TABLE " + tableName + " add index INDEX_" +columName+ " (" +columName+ ");";
break;
case Newtonsoft.Json.Linq.JTokenType.Float:
mysqlType = "decimal(6,2) NULL DEFAULT '0.00'";
index = "ALTER TABLE " + tableName + " add index INDEX_" +columName+ " (" +columName+ ");";
break;
case Newtonsoft.Json.Linq.JTokenType.Guid:
mysqlType = "nvarchar(45) ";
index = "ALTER TABLE " + tableName + " add index INDEX_" +columName+ " (" +columName+ ");";
break;
case Newtonsoft.Json.Linq.JTokenType.Integer:
mysqlType = "int(11) NULL";
index = "ALTER TABLE " + tableName + " add index INDEX_" +columName+ " (" +columName+ ");";
break;
case Newtonsoft.Json.Linq.JTokenType.String:
mysqlType = "nvarchar(1000) ";
index = "ALTER TABLE " + tableName + " add index INDEX_" +columName+ " (" +columName+ ");";
break;
case Newtonsoft.Json.Linq.JTokenType.Uri:
mysqlType = "nvarchar(1000) ";
index = "ALTER TABLE " + tableName + " add index INDEX_" +columName+ " (" +columName+ ");";
break;
default:
mysqlType = "nvarchar(2000) ";
break;
}
entity.Database.ExecuteSqlCommand("alter table " + tableName + " add column " +columName+ " " + mysqlType + ";" + index);
}
}
}
}
public class MysqlTableSchema
{
public string tables_in_test { get; set; }
}
public class MysqlColumSchema
{
public string field { get; set; }
public string type { get; set; }
public string key { get; set; }
public string Null { get; set; }
public string Extra { get; set; }
}
}