/// <summary>
/// 获取连接对象
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public static DbConnection GetConnByType(Datebasemodel model)
{
var strconn = string.Empty;
switch (model.Dbtype)
{
case "Mysql":
strconn = $"server={model.Dbip};port={model.Dbport}; user id={model.Useraccount}; password={model.Userpassword};database={model.Dbname}; pooling=false;CharSet=utf8;Allow Zero Datetime=True";
try
{
var a = new MySqlConnection(strconn);
a.Open();
a.Close();
return(a);
}
catch
{
return(null);
}
default:
strconn = $"server={model.Dbip};port={model.Dbport}; user id={model.Useraccount}; password={model.Userpassword};database={model.Dbname}; pooling=false;CharSet=utf8;Allow Zero Datetime=True";
try
{
var a = new MySqlConnection(strconn);
a.Open();
a.Close();
return(a);
}
catch
{
return(null);
}
}
}
private static bool IsTableExist(Datebasemodel model, DbConnection db, TableInfo info)
{
bool res = false;
try
{
var dbcom = db.CreateCommand();
var sql = $"SELECT count(1) FROM information_schema.TABLES WHERE table_name ='{info.TableName}' and table_schema='{model.Dbname}';";
dbcom.CommandText = sql;
var iscunzai = dbcom.ExecuteScalar().Toint();
res = iscunzai > 0;
}
catch (Exception ex)
{
}
return(res);
}
/// <summary>
///
/// </summary>
/// <param name="alldata">所有的数据</param>
/// <param name="isdrop">如果表存在是否删除在重建</param>
/// <param name="model">数据库连接对象</param>
/// <returns></returns>
public static Result CreateTble(List <string> alldata, bool isdrop, Datebasemodel model)
{
var res = new Result()
{
IsSucceed = false
};
var tableinfo = new TableInfo()
{
TableColumns = new List <TableColumn>()
};
var index = 0;
foreach (var item in alldata)
{
//过滤第二行
if (index == 1)
{
index++; continue;
}
var colinfo = item.Split(new char[] { '\t' });
if (index == 0)
{
tableinfo.TableName = colinfo[0];
tableinfo.TableRemark = colinfo[1];
}
else
{
var col = new TableColumn();
col.ColName = colinfo[0] ?? "";
col.ColType = colinfo[1] ?? "";
col.ColLength = colinfo[2].Toint();
col.ColDefault = colinfo[3];
col.ColPrimary = colinfo[4].Tobool();
col.ColEmpty = colinfo[5].Tobool();
col.ColSymbol = colinfo[6];
col.ColAuto = colinfo[7].Tobool();
col.ColRemart = colinfo[8];
tableinfo.TableColumns.Add(col);
}
index++;
}
try
{
using (var db = GetConnByType(model))
{
db.Open();
var iscunzai = IsTableExist(model, db, tableinfo);
if (!isdrop && iscunzai)
{
res.IsSucceed = false;
res.Message = $"已存在{tableinfo.TableName}表";
return(res);
}
res = Ctable(tableinfo, db);
//再次查询获取建表结果
res.IsSucceed = IsTableExist(model, db, tableinfo);
res.Message = res.IsSucceed ? $"创建{tableinfo.TableName}成功，共{tableinfo.TableColumns.Count}个字段" : $"创建{tableinfo.TableName}失败";
}
}
catch (Exception ex)
{
}
return(res);
}
