private void btnCreate_Click(object sender, EventArgs e)
{
mdc = new MDataColumn();
mdc.Add("ID", SqlDbType.Int, true, false, 11, true, null);
mdc.Add("Name");
mdc.ToTable().Bind(dgvData);
}
private static bool FillSchemaFromCache(ref MDataRow row, ref DbBase dbBase, string tableName, string sourceTableName)
{
bool returnResult = false;
string key = GetSchemaKey(tableName, dbBase.DataBase, dbBase.dalType);
if (CacheManage.LocalInstance.Contains(key))//缓存里获取
{
try
{
row = ((MDataColumn)CacheManage.LocalInstance.Get(key)).ToRow(sourceTableName);
returnResult = row.Count > 0;
}
catch (Exception err)
{
Log.WriteLogToTxt(err);
}
}
else if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
string fullPath = AppDomain.CurrentDomain.BaseDirectory + AppConfig.DB.SchemaMapPath + key + ".ts";
if (System.IO.File.Exists(fullPath))
{
MDataColumn mdcs = MDataColumn.CreateFrom(fullPath);
if (mdcs.Count > 0)
{
row = mdcs.ToRow(sourceTableName);
returnResult = row.Count > 0;
CacheManage.LocalInstance.Add(key, mdcs.Clone(), null, 1440);
}
}
}
return(returnResult);
}
/// <summary>
/// 获取指定的表架构生成的SQL(Create Table)的说明语句
/// </summary>
internal static string CreateTableDescriptionSql(string tableName, MDataColumn columns, DalType dalType)
{
string result = string.Empty;
switch (dalType)
{
case DalType.MsSql:
case DalType.Oracle:
StringBuilder sb = new StringBuilder();
foreach (MCellStruct mcs in columns)
{
if (!string.IsNullOrEmpty(mcs.Description))
{
if (dalType == DalType.MsSql)
{
sb.AppendFormat("exec sp_addextendedproperty N'MS_Description', N'{0}', N'user', N'dbo', N'table', N'{1}', N'column', N'{2}';\r\n", mcs.Description, tableName, mcs.ColumnName);
}
else if (dalType == DalType.Oracle)
{
sb.AppendFormat("comment on column {0}.{1} is '{2}';\r\n", tableName.ToUpper(), mcs.ColumnName.ToUpper(), mcs.Description);
}
}
}
if (dalType == DalType.MsSql) //增加表的描述
{
sb.AppendFormat("exec sp_addextendedproperty N'MS_Description', N'{0}', N'user', N'dbo', N'table', N'{1}';\r\n", columns.Description, tableName);
}
result = sb.ToString().TrimEnd(';');
break;
}
return(result);
}
private static void SetStruct(MDataColumn mdc, PropertyInfo pi, FieldInfo fi, int i, int count)
{
Type type = pi != null ? pi.PropertyType : fi.FieldType;
string name = pi != null ? pi.Name : fi.Name;
SqlDbType sqlType = SQL.DataType.GetSqlType(type);
mdc.Add(name, sqlType);
MCellStruct column = mdc[i];
LengthAttribute la = GetAttr <LengthAttribute>(pi, fi);//获取长度设置
if (la != null)
{
column.MaxSize = la.MaxSize;
column.Scale = la.Scale;
}
if (column.MaxSize <= 0)
{
column.MaxSize = DataType.GetMaxSize(sqlType);
}
KeyAttribute ka = GetAttr <KeyAttribute>(pi, fi);//获取关键字判断
if (ka != null)
{
column.IsPrimaryKey = ka.IsPrimaryKey;
column.IsAutoIncrement = ka.IsAutoIncrement;
column.IsCanNull = ka.IsCanNull;
}
else if (i == 0)
{
column.IsPrimaryKey = true;
column.IsCanNull = false;
if (column.ColumnName.ToLower().Contains("id") && (column.SqlType == System.Data.SqlDbType.Int || column.SqlType == SqlDbType.BigInt))
{
column.IsAutoIncrement = true;
}
}
DefaultValueAttribute dva = GetAttr <DefaultValueAttribute>(pi, fi);
if (dva != null && dva.DefaultValue != null)
{
if (column.SqlType == SqlDbType.Bit)
{
column.DefaultValue = (dva.DefaultValue.ToString() == "True" || dva.DefaultValue.ToString() == "1") ? 1 : 0;
}
else
{
column.DefaultValue = dva.DefaultValue;
}
}
else if (i > count - 3 && sqlType == SqlDbType.DateTime && name.EndsWith("Time"))
{
column.DefaultValue = SqlValue.GetDate;
}
DescriptionAttribute da = GetAttr <DescriptionAttribute>(pi, fi);//看是否有字段描述属性。
if (da != null)
{
column.Description = da.Description;
}
}
/// <summary>
/// 修改表的列结构
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="columns">列结构</param>
/// <param name="conn">数据库链接</param>
/// <returns></returns>
public static bool AlterTable(string tableName, MDataColumn columns, string conn)
{
List<string> sqls = SqlCreateForSchema.AlterTableSql(tableName, columns, conn);
if (sqls.Count > 0)
{
using (MProc proc = new MProc(null, conn))
{
proc.SetAopOff();
if (proc.DalType == DalType.MsSql)
{
proc.BeginTransation();//仅对mssql有效。
}
foreach (string sql in sqls)
{
proc.ResetProc(sql);
if (proc.ExeNonQuery() == -2)
{
proc.RollBack();
Log.WriteLogToTxt(proc.DebugInfo);
return false;
}
}
proc.EndTransation();
}
//清缓存
string key = GetSchemaCacheKey(tableName);
Cache.CacheManage.LocalInstance.Remove(key);
return true;
}
return false;
}
internal static string FormatOracleDateTime(string where, MDataColumn mdc)
{
if (where.IndexOf(':') > -1 && where.IndexOfAny(new char[] { '>', '<', '-', '/' }) > -1)//判断是否存在日期的判断
{
foreach (MCellStruct item in mdc)
{
if (DataType.GetGroup(item.SqlType) == 2 && where.IndexOf(item.ColumnName, StringComparison.OrdinalIgnoreCase) > -1)
{
string pattern = @"(\s?" + item.ColumnName + @"\s*[><]{1}[=]?\s*)('.{19,23}')";
Regex reg = new Regex(pattern, RegexOptions.IgnoreCase);
if (reg.IsMatch(where))
{
where = reg.Replace(where, delegate(Match match)
{
if (item.SqlType == SqlDbType.Timestamp)
{
return(match.Groups[1].Value + "to_timestamp(" + match.Groups[2].Value + ",'yyyy-MM-dd HH24:MI:ss.ff')");
}
else
{
return(match.Groups[1].Value + "to_date(" + match.Groups[2].Value + ",'yyyy-mm-dd hh24:mi:ss')");
}
});
}
}
}
}
return(where);
}
/// <summary>
/// 修改表的列结构
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="columns">列结构</param>
/// <param name="conn">数据库链接</param>
/// <returns></returns>
public static bool AlterTable(string tableName, MDataColumn columns, string conn)
{
List<string> sqls = SqlCreateForSchema.AlterTableSql(tableName, columns, conn);
if (sqls.Count > 0)
{
DalType dalType = DalType.None;
string database = string.Empty;
using (MProc proc = new MProc(null, conn))
{
dalType = proc.DalType;
database = proc.dalHelper.DataBase;
proc.SetAopOff();
if (proc.DalType == DalType.MsSql)
{
proc.BeginTransation();//仅对mssql有效。
}
foreach (string sql in sqls)
{
proc.ResetProc(sql);
if (proc.ExeNonQuery() == -2)
{
proc.RollBack();
Log.WriteLogToTxt(proc.DebugInfo);
return false;
}
}
proc.EndTransation();
}
RemoveCache(tableName, database, dalType);
return true;
}
return false;
}
/// <summary>
/// 获取表列架构（链接错误时，抛异常）
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="conn">数据库链接</param>
/// <param name="errInfo">出错时的错误信息</param>
/// <returns></returns>
public static MDataColumn GetColumns(string tableName, string conn, out string errInfo)
{
errInfo = string.Empty;
DbBase helper = DalCreate.CreateDal(conn);
helper.ChangeDatabaseWithCheck(tableName);//检测dbname.dbo.tablename的情况
if (!helper.TestConn())
{
errInfo = helper.debugInfo.ToString();
if (string.IsNullOrEmpty(errInfo))
{
errInfo = "Open database fail : " + tableName;
}
helper.Dispose();
Error.Throw(errInfo);
return(null);
}
if (!tableName.Contains(" "))
{
tableName = GetMapTableName(conn, tableName);
}
MDataColumn mdc = TableSchema.GetColumns(tableName, ref helper);
helper.Dispose();
return(mdc);
}
/// <summary>
/// 修改表的列结构
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="columns">列结构</param>
/// <param name="conn">数据库链接</param>
/// <returns></returns>
public static bool AlterTable(string tableName, MDataColumn columns, string conn)
{
List <string> sqls = SqlCreateForSchema.AlterTableSql(tableName, columns, conn);
if (sqls.Count > 0)
{
DalType dalType = DalType.None;
string database = string.Empty;
using (MProc proc = new MProc(null, conn))
{
dalType = proc.DalType;
database = proc.dalHelper.DataBase;
proc.SetAopState(Aop.AopOp.CloseAll);
if (proc.DalType == DalType.MsSql)
{
proc.BeginTransation();//仅对mssql有效。
}
foreach (string sql in sqls)
{
proc.ResetProc(sql);
if (proc.ExeNonQuery() == -2)
{
proc.RollBack();
Log.WriteLogToTxt(proc.DebugInfo);
return(false);
}
}
proc.EndTransation();
}
RemoveCache(tableName, database, dalType);
return(true);
}
return(false);
}
private void ViewColumns()
{
using (MAction action = new MAction("Users"))//实例后就可以拿到表的结构了。
{
mdc = action.Data.Columns;
}
mdc.ToTable().Bind(dgvData);
}
private static void FillTable(string objName, string objCode, MDataTable dt)
{
Dictionary <string, string> fieldTitleDic = GridConfig.FieldTitle;
MDataColumn mdc = DBTool.GetColumns(CrossDb.GetEnum(objCode));
MCellStruct cell = null;
int jointPrimaryCount = mdc.JointPrimary.Count;
for (int i = 0; i < mdc.Count; i++)
{
cell = mdc[i];
MDataRow row = dt.NewRow();
row.Set(Config_Grid.ObjName, objName);
row.Set(Config_Grid.Field, cell.ColumnName);
row.Set(Config_Grid.Title, fieldTitleDic.ContainsKey(cell.ColumnName) ? fieldTitleDic[cell.ColumnName] : cell.ColumnName);
row.Set(Config_Grid.Hidden, i == 0 && jointPrimaryCount < 2);
row.Set(Config_Grid.OrderNum, (i + 1) * 10);
row.Set(Config_Grid.Width, 100);
row.Set(Config_Grid.Sortable, i > 0);
row.Set(Config_Grid.Import, i > 0);
row.Set(Config_Grid.Export, i > 0);
row.Set(Config_Grid.Colspan, 1);
row.Set(Config_Grid.Rowspan, 1);
row.Set(Config_Grid.Edit, i > 0 || jointPrimaryCount > 1);
row.Set(Config_Grid.Frozen, i < 4);
row.Set(Config_Grid.Align, "center");
string value = DataType.GetType(cell.SqlType).Name.ToLower() + "," + cell.MaxSize + "," + cell.Scale + (cell.IsCanNull ? ",0" : ",1") + (cell.IsPrimaryKey ? ",1" : ",0");
row.Set(Config_Grid.DataType, value);
if (i == 0)
{
if (jointPrimaryCount < 2)
{
row.Set(Config_Grid.Formatter, "#");
}
}
else
{
switch (DataType.GetGroup(cell.SqlType))
{
case 2:
row.Set(Config_Grid.Formatter, "dateFormatter");
break;
case 3:
row.Set(Config_Grid.Formatter, "boolFormatter");
break;
default:
if (cell.MaxSize > 50)
{
row.Set(Config_Grid.Formatter, "stringFormatter");
}
break;
}
}
dt.Rows.Add(row);
}
}
public static string GetCreateTableDescriptionSql(string tableName, string conn)
{
MDataColumn mdc = GetColumns(tableName, conn);
if (mdc == null)
{
return(ErrorMsg);
}
return(GetCreateTableDescriptionSql(tableName, mdc, mdc.DataBaseType));
}
protected override void Show(IDialogVisualizerService windowService, IVisualizerObjectProvider objectProvider)
{
MDataColumn mdc = objectProvider.GetObject() as MDataColumn;
if (mdc != null)
{
MDataTable dt = mdc.ToTable();
string title = string.Format("TableName : {0} Columns£º {1}", dt.TableName, mdc.Count);
FormCreate.BindTable(windowService, dt, title);
}
}
private static void FillTable(string objName, MDataTable dt)
{
string formatObjName = objName.Contains(" ") ? objName.Substring(objName.LastIndexOf(' ')).Trim() : objName;
Dictionary <string, string> fieldTitleDic = GridConfig.FieldTitle;
MDataColumn mdc = DBTool.GetColumns(CrossDb.GetEnum(objName));
MCellStruct cell = null;
for (int i = 0; i < mdc.Count; i++)
{
cell = mdc[i];
MDataRow row = dt.NewRow();
row["ObjName"].Value = formatObjName;
row["Field"].Value = cell.ColumnName;
row["Title"].Value = fieldTitleDic.ContainsKey(cell.ColumnName) ? fieldTitleDic[cell.ColumnName] : cell.ColumnName;
row["Hidden"].Value = i == 0;//隐藏主键
row["Formatter"].Value = i == 0 ? "#" : "";
row["OrderNum"].Value = (i + 1) * 10;
row["Width"].Value = 100;//第一个主键列<10，则由图标个数*36 自动控制。
row["Sortable"].Value = i > 0;
row["Import"].Value = i > 0 && !cell.IsCanNull;
row["Export"].Value = true;
row["Edit"].Value = i > 0;
row["Frozen"].Value = i < 4;
row["Align"].Value = "center";
row["DataType"].Value = DataType.GetType(cell.SqlType).Name.ToLower() + "," + cell.MaxSize + "," + cell.Scale + "," + (cell.IsCanNull ? 0 : 1);
if (i > 0)
{
switch (DataType.GetGroup(cell.SqlType))
{
//case 1:
// row["DataType"].Value = "int";
// break;
case 2:
//row["DataType"].Value = "datetime";
row["Formatter"].Value = "dateFormatter";
break;
case 3:
//row["DataType"].Value = "bool";
row["Formatter"].Value = "boolFormatter";
break;
default:
//row["DataType"].Value = "string";
if (cell.MaxSize > 50)
{
row["Formatter"].Value = "stringFormatter";
}
break;
}
}
dt.Rows.Add(row);
}
}
void CreateRow()
{
MDataColumn mdc = new MDataColumn();
mdc.Add("ID", SqlDbType.Int, true);
mdc.Add("Name");
mdc.Add("CreateTime", SqlDbType.DateTime);
row = new MDataRow(mdc);
row.Set(0, 1).Set(1, "hello").Set(2, DateTime.Now);
row.SetToAll(this);
}
private void button2_Click(object sender, EventArgs e)
{
var tableName = this.textBox1.Text;
MDataColumn col = new MDataColumn();
col.Add("Name", SqlDbType.NVarChar);
col.Add("Age", SqlDbType.Int);
var flag = DBTool.CreateTable(tableName, col);
MessageBox.Show(flag + "");
}
/// <summary>
/// 获取表列架构
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="conn">数据库链接</param>
/// <returns></returns>
public static MDataColumn GetColumns(string tableName, string conn)
{
string err;
MDataColumn mdc = GetColumns(tableName, conn, out err);
if (mdc == null)
{
_ErrorMsg.Append(err);
}
return(mdc);
}
private static void FillTable(string objName, MDataTable dt)
{
string formatObjName = objName.Contains(" ") ? objName.Substring(objName.LastIndexOf(' ')).Trim() : objName;
Dictionary <string, string> fieldTitleDic = GridConfig.FieldTitle;
MDataColumn mdc = DBTool.GetColumns(CrossDb.GetEnum(objName));
MCellStruct cell = null;
for (int i = 0; i < mdc.Count; i++)
{
cell = mdc[i];
MDataRow row = dt.NewRow();
row.Set(Config_Grid.ObjName, formatObjName);
row.Set(Config_Grid.Field, cell.ColumnName);
row.Set(Config_Grid.Title, fieldTitleDic.ContainsKey(cell.ColumnName) ? fieldTitleDic[cell.ColumnName] : cell.ColumnName);
row.Set(Config_Grid.Hidden, i == 0);
row.Set(Config_Grid.OrderNum, (i + 1) * 10);
row.Set(Config_Grid.Width, 100);
row.Set(Config_Grid.Sortable, i > 0);
row.Set(Config_Grid.Import, i > 0);
row.Set(Config_Grid.Export, i > 0);
row.Set(Config_Grid.Colspan, 1);
row.Set(Config_Grid.Rowspan, 1);
row.Set(Config_Grid.Edit, i > 0);
row.Set(Config_Grid.Frozen, i < 4);
row.Set(Config_Grid.Align, "center");
row.Set(Config_Grid.DataType, DataType.GetType(cell.SqlType).Name.ToLower() + "," + cell.MaxSize + "," + cell.Scale + "," + (cell.IsCanNull ? 0 : 1));
if (i == 0)
{
row.Set(Config_Grid.Formatter, "#");
}
else
{
switch (DataType.GetGroup(cell.SqlType))
{
case 2:
row.Set(Config_Grid.Formatter, "dateFormatter");
break;
case 3:
row.Set(Config_Grid.Formatter, "boolFormatter");
break;
default:
if (cell.MaxSize > 50)
{
row.Set(Config_Grid.Formatter, "stringFormatter");
}
break;
}
}
dt.Rows.Add(row);
}
}
private string GetJson(MDataColumn mdc)
{
JsonHelper js = new JsonHelper();
foreach (var cell in mdc)
{
js.Add("Key", cell.ColumnName);
js.Add("Value", cell.ColumnName);
js.AddBr();
}
return(js.ToString(true));
}
/// <summary>
/// 读取数据库所有表
/// </summary>
/// <param name="key">关键字</param>
/// <param name="filter">过滤条件值</param>
/// <returns></returns>
public static MDataTable Get(string key, string filter)
{
MDataTable dt = null;
SortedDictionary <string, string> newDic = new SortedDictionary <string, string>();
switch (key)
{
case "C_SYS_Table":
if (!string.IsNullOrEmpty(filter))
{
if (CrossDb.DbTables.ContainsKey(filter))
{
foreach (var item in CrossDb.DbTables[filter])
{
newDic.Add(item.Key, item.Key);
}
}
}
else
{
foreach (var tableDic in CrossDb.DbTables)
{
foreach (var item in tableDic.Value)
{
newDic.Add(item.Key, item.Key);
}
}
}
break;
case "C_SYS_Column":
if (!string.IsNullOrEmpty(filter))
{
MDataColumn mdc = DBTool.GetColumns(CrossDb.GetEnum(filter));
foreach (var item in mdc)
{
newDic.Add(item.ColumnName, item.ColumnName);
}
}
break;
}
dt = MDataTable.CreateFrom(newDic);
if (dt != null)
{
dt.Columns[0].ColumnName = "value";
dt.Columns[1].ColumnName = "text";
if (dt.Columns.Count > 2)
{
dt.Columns[2].ColumnName = "parent";
}
}
return(dt);
}
/// <summary>
/// 得到enum
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="tableName_displayName">表名（显示名[数据库中备注的名称]）</param>
/// <param name="config"></param>
/// <returns></returns>
private static string GetFiledEnum(string tableName, string tableName_displayName, ProjectConfig config)
{
StringBuilder builder = new StringBuilder();
// 处理回车或换行
tableName_displayName = tableName_displayName.Replace("\r\n", "").Replace("\r", "").Replace("\n", "");;
tableName_displayName = tableName_displayName == "" ? tableName : tableName_displayName;
builder.AppendFormat(" #region [ 表名：{0} 备注名：{1} ]\r\n", tableName.PadRight(45), tableName_displayName.PadRight(30));
builder.AppendFormat(" /// <summary>\r\n", string.Empty);
builder.AppendFormat(" /// enum 表名：{0}\r\n", tableName_displayName);
builder.AppendFormat(" /// </summary>\r\n", string.Empty);
builder.AppendFormat(" public enum {0}\r\n", FormatKey(tableName));
builder.AppendFormat(" {{");
try
{
MDataColumn columns = DBTool.GetColumns(tableName, config.Conn);
if (columns.Count > 0)
{
for (int i = 0; i < columns.Count; i++)//进行列的输出
{
string str = FormatKey(columns[i].ColumnName);
// 处理回车或换行
string strDisplayName = columns[i].Description.Replace("\r\n", "").Replace("\r", "").Replace("\n", "");;
strDisplayName = strDisplayName == "" ? str : strDisplayName;
builder.AppendFormat("\r\n");
builder.AppendFormat(" /// <summary>\r\n", string.Empty);
builder.AppendFormat(" /// enum 字段名：{0}\r\n", strDisplayName);
builder.AppendFormat(" /// </summary>\r\n", string.Empty);
builder.AppendFormat(" [Display(Name = \"{0}\")]\r\n", strDisplayName);
builder.AppendFormat(" {0} ,\r\n", str, strDisplayName);
}
builder.Append(" }\r\n");
}
else
{
builder.Append(" }\r\n");
}
}
catch (Exception exception)
{
CYQ.Data.Log.WriteLogToTxt(exception);
}
builder.AppendFormat(" #endregion\r\n");
return(builder.ToString());
}
private void btnCreateData_Click(object sender, EventArgs e)
{
MDataTable dt = new MDataTable("MyTable");
dt.Columns.Add("ID", SqlDbType.Int, true, false, 11, true, null);
dt.Columns.Add("Name");
dt.Columns.Add("Password");
mdc = dt.Columns;
for (int i = 0; i < 10; i++)
{
dt.NewRow(true).Set(0, i + 1).Set(1, "Name" + i).Set(2, i);
}
dt.Bind(dgvData);
}
/// <summary>
/// 获取指定的表架构生成的SQL(Alter Table)的说明语句
/// </summary>
public static string GetAlterTableSql(string tableName, MDataColumn columns, string conn)
{
List <string> sqlItems = SqlCreateForSchema.AlterTableSql(tableName, columns, conn);
if (sqlItems.Count > 0)
{
StringBuilder sb = new StringBuilder();
foreach (string sql in sqlItems)
{
sb.AppendLine(sql);
}
sqlItems = null;
return(sb.ToString());
}
return(string.Empty);
}
private static MDataColumn GetViewColumns(string sqlText, ref DbBase helper)
{
helper.OpenCon(null);
helper.Com.CommandText = sqlText;
DbDataReader sdr = helper.Com.ExecuteReader(CommandBehavior.KeyInfo);
DataTable keyDt = null;
if (sdr != null)
{
keyDt = sdr.GetSchemaTable();
FixTableSchemaType(sdr, keyDt);
sdr.Close();
}
MDataColumn mdc = GetColumns(keyDt);
mdc.dalType = helper.dalType;
return(mdc);
}
private static MDataColumn GetViewColumns(string sqlText, ref DalBase helper)
{
MDataColumn mdc = null;
helper.OpenCon(null, AllowConnLevel.MaterBackupSlave);
helper.Com.CommandText = sqlText;
DbDataReader sdr = helper.Com.ExecuteReader(CommandBehavior.KeyInfo);
DataTable keyDt = null;
if (sdr != null)
{
keyDt = sdr.GetSchemaTable();
mdc = GetColumnByTable(keyDt, sdr, true);
mdc.DataBaseType = helper.DataBaseType;
}
return(mdc);
}
private static bool FillSchemaFromDb(ref MDataRow row, ref DbBase dbBase, string tableName, string sourceTableName)
{
try
{
MDataColumn mdcs = null;
//if (tableName.IndexOf('(') > -1 && tableName.IndexOf(')') > -1)//自定义视图table
//{
// dbBase.tempSql = "view";//使用access方式加载列
//}
mdcs = GetColumns(tableName, ref dbBase);
if (mdcs.Count == 0)
{
return(false);
}
row = mdcs.ToRow(sourceTableName);
row.TableName = sourceTableName;
string key = GetSchemaKey(tableName, dbBase.DataBase, dbBase.dalType);
CacheManage.LocalInstance.Add(key, mdcs.Clone(), null, 1440);
switch (dbBase.dalType)//文本数据库不保存。
{
case DalType.Access:
case DalType.SQLite:
case DalType.MsSql:
case DalType.MySql:
case DalType.Oracle:
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
string folderPath = AppDomain.CurrentDomain.BaseDirectory + AppConfig.DB.SchemaMapPath;
if (System.IO.Directory.Exists(folderPath))
{
mdcs.WriteSchema(folderPath + key + ".ts");
}
}
break;
}
return(true);
}
catch (Exception err)
{
Log.WriteLogToTxt(err);
return(false);
}
}
/// <summary>
/// Mysql Bit 类型不允许条件带引号 （字段='0' 不可以）
/// </summary>
/// <param name="where"></param>
/// <param name="mdc"></param>
/// <returns></returns>
internal static string FormatMySqlBit(string where, MDataColumn mdc)
{
if (where.Contains("'0'"))
{
foreach (MCellStruct item in mdc)
{
int groupID = DataType.GetGroup(item.SqlType);
if (groupID == 1 || groupID == 3)//视图模式里取到的bit是bigint,所以数字一并处理
{
if (where.IndexOf(item.ColumnName, StringComparison.OrdinalIgnoreCase) > -1)
{
string pattern = @"\s?" + item.ColumnName + @"\s*=\s*'0'";
where = Regex.Replace(where, pattern, " " + item.ColumnName + "=0", RegexOptions.IgnoreCase);
}
}
}
}
return(where);
}
/// <summary>
/// 设置Excel列头数据（支持多级表头）。
/// <param name="maxLevel">表头的层级数</param>
/// </summary>
private static ISheet SetHeader(IWorkbook export, MDataTable header, MDataColumn columns, out int maxLevel)
{
ISheet sheet = export.CreateSheet("Sheet1");//创建内存Excel
ICellStyle style = GetStyle(export, HSSFColor.LightOrange.Index);
if (header != null && header.FindRow(Config_Grid.Field + " like 'mg_%'") != null)
{
Dictionary <int, List <MDataRow> > headerGroup = GetColumnGroup(header);// header 包含mg_
MDataTable[] items = header.Split(Config_Grid.Field + " like 'mg_%'");
CreateMergeHeader(sheet, style, headerGroup, items[1]);
maxLevel = headerGroup.Count;
}
else
{
IRow row = sheet.CreateRow(0);//index代表第N行
if (columns != null)
{
for (int i = 0; i < columns.Count; i++)
{
string title = string.IsNullOrEmpty(columns[i].Description) ? columns[i].ColumnName : columns[i].Description;
ICell cell = row.CreateCell(i);
cell.SetCellValue(title);//设置列头
sheet.SetColumnWidth(i, 3000);
cell.CellStyle = style;
}
}
else
{
for (int i = 0; i < header.Rows.Count; i++)
{
string title = header.Rows[i].Get <string>(Config_Grid.Title);
ICell cell = row.CreateCell(i);
cell.SetCellValue(title);//设置列头
sheet.SetColumnWidth(i, 3000);
cell.CellStyle = style;
}
}
maxLevel = 1;
}
return(sheet);
}
public static Dictionary <string, string> GetTitleField(string tableName)
{
Dictionary <string, string> dic = new Dictionary <string, string>();
MDataColumn mdc = DBTool.GetColumns(CrossDb.GetEnum(tableName));
if (mdc.Count > 0)
{
foreach (MCellStruct item in mdc)
{
string des = Convert.ToString(item.Description);
if (!string.IsNullOrEmpty(des))
{
des = des.Split(new char[] { ' ', ',', '(' })[0];
if (!dic.ContainsKey(des))
{
dic.Add(des, item.ColumnName);
}
}
}
}
return(dic);
}
/// <summary>
/// 获取指定的表架构生成的SQL(Create Table)语句
/// </summary>
internal static string CreateTableSql(string tableName, MDataColumn columns, DalType dalType, string version)
{
switch (dalType)
{
case DalType.Txt:
case DalType.Xml:
return(columns.ToJson(true));
default:
string createSql = string.Empty;
createSql = "CREATE TABLE " + SqlFormat.Keyword(tableName, dalType) + " \n(";
//读取主键的个数，如果是联合主键，则不设置主键。
List <MCellStruct> primaryKeyList = new List <MCellStruct>();
foreach (MCellStruct column in columns)
{
if (column.IsPrimaryKey)
{
primaryKeyList.Add(column);
}
}
foreach (MCellStruct column in columns)
{
createSql += "\n " + GetKey(column, dalType, ref primaryKeyList, version);
}
if (primaryKeyList.Count > 0)
{
createSql += GetUnionPrimaryKey(dalType, primaryKeyList);
}
createSql = createSql.TrimEnd(',') + " \n)";
// createSql += GetSuffix(dalType);
if (dalType == DalType.MySql && createSql.IndexOf("CURRENT_TIMESTAMP") != createSql.LastIndexOf("CURRENT_TIMESTAMP"))
{
createSql = createSql.Replace("Default CURRENT_TIMESTAMP", string.Empty); //mysql不允许存在两个以上的CURRENT_TIMESTAMP。
}
primaryKeyList.Clear();
return(createSql);
}
}
public string GetExcelMapping()
{
MDataRow row = ExcelConfig.GetExcelRow(Query <string>("ID"));
string objName = row.Get <string>(Config_Excel.ExcelName);
string[] TableNames = row.Get <string>(Config_Excel.TableNames).Split(',');
Dictionary <string, string> dic = new Dictionary <string, string>();
JsonHelper js = new JsonHelper();
foreach (string name in TableNames)
{
MDataColumn mdc = DBTool.GetColumns(name);
js.Add(name, GetJson(mdc), true);
dic.Add(name, name);
}
JsonHelper jh = new JsonHelper();
jh.Add("objName", objName);
jh.Add("arrColumns", js.ToString(false));
jh.Add("arrTables", MDataTable.CreateFrom(dic).ToJson(false, false));
return(jh.ToString());
}
/// <summary>
/// 生成表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnCreate_Click(object sender, EventArgs e)
{
var dc = new MDataColumn();
var tableName = textTableName.Text;
var text = textPrimary.Text;
var cols = textCols.Text;
var conn = textConn.Text;
var list = JsonHelper.ToList <ColumnModel>(cols);
foreach (ColumnModel t in list)
{
var cell = new MCellStruct(t.ColName, t.SqlType);
if (text.ToLower().Equals(t.ColName.ToLower()))
{
cell.IsPrimaryKey = true;
}
cell.Description = t.Description;
dc.Add(cell);
}
var msg = DBTool.CreateTable(tableName, dc, conn)?"生成成功":"生成失败";
MessageBox.Show(msg);
}
/// <summary>
/// 条件比较
/// </summary>
private static bool GetTFilterOk(string where, int quoteIndex, string sign, Op op, MDataColumn mdc, out TFilter tFilter)
{
bool result = false;
tFilter = null;
int index = where.ToLower().IndexOf(sign, 0, quoteIndex > 0 ? quoteIndex : where.Length);
if (index > 0)
{
string columnAName = where.Substring(0, index).Trim();
int columnAIndex = mdc.GetIndex(columnAName);
string valueB = where.Substring(index + sign.Length).Trim(' ', '\'').Replace("''", "'");
if (op == Op.In || op == Op.NotIn)
{
valueB = ',' + valueB.TrimStart('(', ')').Replace("'", "") + ",";//去除单引号。
}
int columnBIndex = -1;
if (quoteIndex == 0 && mdc.Contains(valueB)) //判断右侧的是否列名。
{
columnBIndex = mdc.GetIndex(valueB);
}
tFilter = new TFilter(Ao.None, columnAName, columnAIndex, op, valueB, columnBIndex);
if (columnBIndex == -1 && !string.IsNullOrEmpty(Convert.ToString(valueB)))//右侧是值类型，转换值的类型。
{
if (columnAIndex > -1 && DataType.GetGroup(mdc[columnAIndex].SqlType) == 3)//bool型
{
switch (Convert.ToString(tFilter._valueB).ToLower())
{
case "true":
case "1":
case "on":
tFilter._valueB = true;
break;
case "false":
case "0":
case "":
tFilter._valueB = false;
break;
default:
tFilter._valueB = null;
break;
}
}
else
{
try
{
tFilter._valueB = StaticTool.ChangeType(tFilter._valueB, columnAIndex > -1 ? typeof(string) : mdc[columnAIndex].ValueType);
}
catch
{
}
}
}
result = true;
}
return result;
}
/// <summary>
/// 获取指定的表架构生成的SQL(Create Table)的说明语句
/// </summary>
public static string GetCreateTableDescriptionSql(string tableName, MDataColumn columns, DalType dalType)
{
return SqlCreateForSchema.CreateTableDescriptionSql(tableName, columns, dalType);
}
/// <summary>
/// 单个条件
/// </summary>
private static TFilter GetSingleTFilter(string where, MDataColumn mdc)
{
//id like 'a>b=c'
//id>'a like b'
where = where.TrimStart('(').TrimEnd(')').Trim();
int quoteIndex = where.IndexOf('\'');
quoteIndex = quoteIndex == -1 ? 0 : quoteIndex;
TFilter tFilter = null;
foreach (KeyValuePair<string, Op> opItem in Ops)
{
if (GetTFilterOk(where, quoteIndex, opItem.Key, opItem.Value, mdc, out tFilter))
{
break;
}
}
return tFilter;
}
/// <summary>
/// 多个条件
/// </summary>
private static TFilter[] GetTFilter(object whereObj, MDataColumn mdc)
{
List<TFilter> tFilterList = new List<TFilter>();
string whereStr = SqlFormat.GetIFieldSql(whereObj);
whereStr = SqlCreate.FormatWhere(whereStr, mdc, DalType.None, null);
string lowerWhere = whereStr.ToLower();
string andSign = " and ";
string orSign = " or ";
int andIndex = IndexOf(lowerWhere, andSign, 0);// lowerWhere.IndexOf(andSign);
int orIndex = IndexOf(lowerWhere, orSign, 0);
TFilter filter = null;
if (andIndex == -1 && orIndex == -1)//仅有一个条件
{
filter = GetSingleTFilter(whereStr, mdc);
if (filter != null)
{
tFilterList.Add(filter);
}
}
else if (orIndex == -1) // 只有and条件
{
int andStartIndex = 0;
while (andIndex > -1)
{
filter = GetSingleTFilter(whereStr.Substring(andStartIndex, andIndex - andStartIndex), mdc);
if (filter != null)
{
if (andStartIndex > 0)
{
filter._Ao = Ao.And;
}
tFilterList.Add(filter);
}
andStartIndex = andIndex + andSign.Length;
andIndex = IndexOf(lowerWhere, andSign, andStartIndex + 1);
}
filter = GetSingleTFilter(whereStr.Substring(andStartIndex), mdc);
if (filter != null)
{
filter._Ao = Ao.And;
tFilterList.Add(filter);
}
}
else if (andIndex == -1) //只有or 条件
{
int orStartIndex = 0;
while (orIndex > -1)
{
filter = GetSingleTFilter(whereStr.Substring(orStartIndex, orIndex - orStartIndex), mdc);
if (filter != null)
{
if (orStartIndex > 0)
{
filter._Ao = Ao.Or;
}
tFilterList.Add(filter);
}
orStartIndex = orIndex + orSign.Length;
orIndex = IndexOf(lowerWhere, orSign, orStartIndex + 1);
}
filter = GetSingleTFilter(whereStr.Substring(orStartIndex), mdc);
if (filter != null)
{
filter._Ao = Ao.Or;
tFilterList.Add(filter);
}
}
else //有and 又有 or
{
bool isAnd = andIndex < orIndex;
bool lastAnd = isAnd;
int andOrIndex = isAnd ? andIndex : orIndex;//最小的，前面的先处理
int andOrStartIndex = 0;
while (andOrIndex > -1)
{
filter = GetSingleTFilter(whereStr.Substring(andOrStartIndex, andOrIndex - andOrStartIndex), mdc);
if (filter != null)
{
if (andOrStartIndex > 0)
{
filter._Ao = lastAnd ? Ao.And : Ao.Or;
}
tFilterList.Add(filter);
}
andOrStartIndex = andOrIndex + (isAnd ? andSign.Length : orSign.Length);
if (isAnd)
{
andIndex = IndexOf(lowerWhere, andSign, andOrStartIndex + 1);
}
else
{
orIndex = IndexOf(lowerWhere, orSign, andOrStartIndex + 1);
}
lastAnd = isAnd;
if (andIndex == -1)
{
isAnd = false;
andOrIndex = orIndex;
}
else if (orIndex == -1)
{
isAnd = true;
andOrIndex = andIndex;
}
else
{
isAnd = andIndex < orIndex;
andOrIndex = isAnd ? andIndex : orIndex;//最小的，前面的先处理
}
}
filter = GetSingleTFilter(whereStr.Substring(andOrStartIndex), mdc);
if (filter != null)
{
filter._Ao = lastAnd ? Ao.And : Ao.Or;
tFilterList.Add(filter);
}
}
// string firstFilter=whereStr.su
return tFilterList.ToArray();
}
public static MDataColumn GetColumns(string tableName, ref DbBase dbHelper)
{
tableName = Convert.ToString(SqlCreate.SqlToViewSql(tableName));
DalType dalType = dbHelper.dalType;
tableName = SqlFormat.Keyword(tableName, dbHelper.dalType);
string key = GetSchemaKey(tableName, dbHelper.DataBase, dbHelper.dalType);
if (CacheManage.LocalInstance.Contains(key))//�������ȡ
{
return CacheManage.LocalInstance.Get<MDataColumn>(key).Clone();
}
switch (dalType)
{
case DalType.SQLite:
case DalType.MySql:
tableName = SqlFormat.NotKeyword(tableName);
break;
case DalType.Txt:
case DalType.Xml:
tableName = Path.GetFileNameWithoutExtension(tableName);//��ͼ�������.���ģ��������
string fileName = dbHelper.Con.DataSource + tableName + (dalType == DalType.Txt ? ".txt" : ".xml");
return MDataColumn.CreateFrom(fileName);
}
MDataColumn mdcs = new MDataColumn();
mdcs.dalType = dbHelper.dalType;
//���table��helper����ͬһ����
DbBase helper = dbHelper.ResetDbBase(tableName);
helper.IsAllowRecordSql = false;//�ڲ�ϵͳ������¼SQL��ṹ��䡣
try
{
bool isView = tableName.Contains(" ");//�Ƿ���ͼ��
if (!isView)
{
isView = Exists("V", tableName, ref helper);
}
MCellStruct mStruct = null;
SqlDbType sqlType = SqlDbType.NVarChar;
if (isView)
{
string sqlText = SqlFormat.BuildSqlWithWhereOneEqualsTow(tableName);// string.Format("select * from {0} where 1=2", tableName);
mdcs = GetViewColumns(sqlText, ref helper);
}
else
{
mdcs.AddRelateionTableName(SqlFormat.NotKeyword(tableName));
switch (dalType)
{
case DalType.MsSql:
case DalType.Oracle:
case DalType.MySql:
case DalType.Sybase:
#region Sql
string sql = string.Empty;
if (dalType == DalType.MsSql)
{
string dbName = null;
if (!helper.Version.StartsWith("08"))
{
//�Ȼ�ȡͬ��ʣ�����Ƿ���
string realTableName = Convert.ToString(helper.ExeScalar(string.Format(SynonymsName, SqlFormat.NotKeyword(tableName)), false));
if (!string.IsNullOrEmpty(realTableName))
{
string[] items = realTableName.Split('.');
tableName = realTableName;
if (items.Length > 0)//�����
{
dbName = realTableName.Split('.')[0];
}
}
}
sql = GetMSSQLColumns(helper.Version.StartsWith("08"), dbName ?? helper.DataBase);
}
else if (dalType == DalType.MySql)
{
sql = GetMySqlColumns(helper.DataBase);
}
else if (dalType == DalType.Oracle)
{
sql = GetOracleColumns();
}
else if (dalType == DalType.Sybase)
{
tableName = SqlFormat.NotKeyword(tableName);
sql = GetSybaseColumns();
}
helper.AddParameters("TableName", tableName, DbType.String, 150, ParameterDirection.Input);
DbDataReader sdr = helper.ExeDataReader(sql, false);
if (sdr != null)
{
long maxLength;
bool isAutoIncrement = false;
short scale = 0;
string sqlTypeName = string.Empty;
while (sdr.Read())
{
short.TryParse(Convert.ToString(sdr["Scale"]), out scale);
if (!long.TryParse(Convert.ToString(sdr["MaxSize"]), out maxLength))//mysql�ij��ȿ��ܴ���int.MaxValue
{
maxLength = -1;
}
else if (maxLength > int.MaxValue)
{
maxLength = int.MaxValue;
}
sqlTypeName = Convert.ToString(sdr["SqlType"]);
sqlType = DataType.GetSqlType(sqlTypeName);
isAutoIncrement = Convert.ToBoolean(sdr["IsAutoIncrement"]);
mStruct = new MCellStruct(mdcs.dalType);
mStruct.ColumnName = Convert.ToString(sdr["ColumnName"]).Trim();
mStruct.OldName = mStruct.ColumnName;
mStruct.SqlType = sqlType;
mStruct.IsAutoIncrement = isAutoIncrement;
mStruct.IsCanNull = Convert.ToBoolean(sdr["IsNullable"]);
mStruct.MaxSize = (int)maxLength;
mStruct.Scale = scale;
mStruct.Description = Convert.ToString(sdr["Description"]);
mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, sdr["DefaultValue"], 0, sqlType);
mStruct.IsPrimaryKey = Convert.ToString(sdr["IsPrimaryKey"]) == "1";
switch (dalType)
{
case DalType.MsSql:
case DalType.MySql:
case DalType.Oracle:
mStruct.IsUniqueKey = Convert.ToString(sdr["IsUniqueKey"]) == "1";
mStruct.IsForeignKey = Convert.ToString(sdr["IsForeignKey"]) == "1";
mStruct.FKTableName = Convert.ToString(sdr["FKTableName"]);
break;
}
mStruct.SqlTypeName = sqlTypeName;
mStruct.TableName = SqlFormat.NotKeyword(tableName);
mdcs.Add(mStruct);
}
sdr.Close();
if (dalType == DalType.Oracle && mdcs.Count > 0)//Ĭ��û���������ֻ�ܸ�������жϡ�
{
MCellStruct firstColumn = mdcs[0];
if (firstColumn.IsPrimaryKey && firstColumn.ColumnName.ToLower().Contains("id") && firstColumn.Scale == 0 && DataType.GetGroup(firstColumn.SqlType) == 1 && mdcs.JointPrimary.Count == 1)
{
firstColumn.IsAutoIncrement = true;
}
}
}
#endregion
break;
case DalType.SQLite:
#region SQlite
if (helper.Con.State != ConnectionState.Open)
{
helper.Con.Open();
}
DataTable sqliteDt = helper.Con.GetSchema("Columns", new string[] { null, null, tableName });
if (!helper.isOpenTrans)
{
helper.Con.Close();
}
int size;
short sizeScale;
string dataTypeName = string.Empty;
foreach (DataRow row in sqliteDt.Rows)
{
object len = row["NUMERIC_PRECISION"];
if (len == null)
{
len = row["CHARACTER_MAXIMUM_LENGTH"];
}
short.TryParse(Convert.ToString(row["NUMERIC_SCALE"]), out sizeScale);
if (!int.TryParse(Convert.ToString(len), out size))//mysql�ij��ȿ��ܴ���int.MaxValue
{
size = -1;
}
dataTypeName = Convert.ToString(row["DATA_TYPE"]);
if (dataTypeName == "text" && size > 0)
{
sqlType = DataType.GetSqlType("varchar");
}
else
{
sqlType = DataType.GetSqlType(dataTypeName);
}
//COLUMN_NAME,DATA_TYPE,PRIMARY_KEY,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH AUTOINCREMENT
mStruct = new MCellStruct(row["COLUMN_NAME"].ToString(), sqlType, Convert.ToBoolean(row["AUTOINCREMENT"]), Convert.ToBoolean(row["IS_NULLABLE"]), size);
mStruct.Scale = sizeScale;
mStruct.Description = Convert.ToString(row["DESCRIPTION"]);
mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, row["COLUMN_DEFAULT"], 0, sqlType);//"COLUMN_DEFAULT"
mStruct.IsPrimaryKey = Convert.ToBoolean(row["PRIMARY_KEY"]);
mStruct.SqlTypeName = dataTypeName;
mStruct.TableName = SqlFormat.NotKeyword(tableName);
mdcs.Add(mStruct);
}
#endregion
break;
case DalType.Access:
#region Access
DataTable keyDt, valueDt;
string sqlText = SqlFormat.BuildSqlWithWhereOneEqualsTow(tableName);// string.Format("select * from {0} where 1=2", tableName);
OleDbConnection con = new OleDbConnection(helper.Con.ConnectionString);
OleDbCommand com = new OleDbCommand(sqlText, con);
con.Open();
keyDt = com.ExecuteReader(CommandBehavior.KeyInfo).GetSchemaTable();
valueDt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, SqlFormat.NotKeyword(tableName) });
con.Close();
con.Dispose();
if (keyDt != null && valueDt != null)
{
string columnName = string.Empty, sqlTypeName = string.Empty;
bool isKey = false, isCanNull = true, isAutoIncrement = false;
int maxSize = -1;
short maxSizeScale = 0;
SqlDbType sqlDbType;
foreach (DataRow row in keyDt.Rows)
{
columnName = row["ColumnName"].ToString();
isKey = Convert.ToBoolean(row["IsKey"]);//IsKey
isCanNull = Convert.ToBoolean(row["AllowDBNull"]);//AllowDBNull
isAutoIncrement = Convert.ToBoolean(row["IsAutoIncrement"]);
sqlTypeName = Convert.ToString(row["DataType"]);
sqlDbType = DataType.GetSqlType(sqlTypeName);
short.TryParse(Convert.ToString(row["NumericScale"]), out maxSizeScale);
if (Convert.ToInt32(row["NumericPrecision"]) > 0)//NumericPrecision
{
maxSize = Convert.ToInt32(row["NumericPrecision"]);
}
else
{
long len = Convert.ToInt64(row["ColumnSize"]);
if (len > int.MaxValue)
{
maxSize = int.MaxValue;
}
else
{
maxSize = (int)len;
}
}
mStruct = new MCellStruct(columnName, sqlDbType, isAutoIncrement, isCanNull, maxSize);
mStruct.Scale = maxSizeScale;
mStruct.IsPrimaryKey = isKey;
mStruct.SqlTypeName = sqlTypeName;
mStruct.TableName = SqlFormat.NotKeyword(tableName);
foreach (DataRow item in valueDt.Rows)
{
if (columnName == item[3].ToString())//COLUMN_NAME
{
if (item[8].ToString() != "")
{
mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, item[8], 0, sqlDbType);//"COLUMN_DEFAULT"
}
break;
}
}
mdcs.Add(mStruct);
}
}
#endregion
break;
}
}
helper.ClearParameters();
}
catch (Exception err)
{
helper.debugInfo.Append(err.Message);
}
finally
{
helper.IsAllowRecordSql = true;//�ָ���¼SQL��ṹ��䡣
if (helper != dbHelper)
{
helper.Dispose();
}
}
if (mdcs.Count > 0)
{
//�Ƴ�����־���У�
string[] fields = AppConfig.DB.HiddenFields.Split(',');
foreach (string item in fields)
{
string field = item.Trim();
if (!string.IsNullOrEmpty(field) & mdcs.Contains(field))
{
mdcs.Remove(field);
}
}
}
if (!CacheManage.LocalInstance.Contains(key))
{
CacheManage.LocalInstance.Add(key, mdcs.Clone());
}
return mdcs;
}
/// <summary>
/// 获取指定的表架构生成的SQL(Create Table)的说明语句
/// </summary>
public static string GetCreateTableSql(string tableName, MDataColumn columns, DalType dalType, string version)
{
return SqlCreateForSchema.CreateTableSql(tableName, columns, dalType, version);
}
private string GetJson(MDataColumn mdc)
{
JsonHelper js = new JsonHelper();
foreach (var cell in mdc)
{
js.Add("Key", cell.ColumnName);
js.Add("Value", cell.ColumnName);
js.AddBr();
}
return js.ToString(true);
}
private static void GetViewColumns(string sqlText, MDataColumn mdcs, ref DbBase helper)
{
helper.OpenCon(null);
helper.Com.CommandText = sqlText;
DbDataReader sdr = helper.Com.ExecuteReader(CommandBehavior.KeyInfo);
DataTable keyDt = null;
if (sdr != null)
{
keyDt = sdr.GetSchemaTable();
sdr.Close();
}
//helper.CloseCon();
if (keyDt != null && keyDt.Rows.Count > 0)
{
mdcs.isViewOwner = true;
//DataColumnCollection dcList = helper.ExeDataTable(sqlText, false).Columns;
string columnName = string.Empty, sqlTypeName = string.Empty, tableName = string.Empty;
bool isKey = false, isCanNull = true, isAutoIncrement = false;
int maxSize = -1;
short maxSizeScale = 0;
SqlDbType sqlDbType;
string dataTypeName = "DataTypeName";
if (!keyDt.Columns.Contains(dataTypeName))
{
dataTypeName = "DataType";
}
bool isHasAutoIncrement = keyDt.Columns.Contains("IsAutoIncrement");
bool isHasHidden = keyDt.Columns.Contains("IsHidden");
foreach (DataRow row in keyDt.Rows)
{
if (isHasHidden && Convert.ToString(row["IsHidden"]) == "True")// !dcList.Contains(columnName))
{
continue;//�����Ǹ����������ֶΡ�
}
columnName = row["ColumnName"].ToString();
isKey = Convert.ToBoolean(row["IsKey"]);//IsKey
isCanNull = Convert.ToBoolean(row["AllowDBNull"]);//AllowDBNull
if (isHasAutoIncrement)
{
isAutoIncrement = Convert.ToBoolean(row["IsAutoIncrement"]);
}
sqlTypeName = Convert.ToString(row[dataTypeName]);
sqlDbType = DataType.GetSqlType(sqlTypeName);
tableName = Convert.ToString(row["BaseTableName"]);
if (short.TryParse(Convert.ToString(row["NumericScale"]), out maxSizeScale) && maxSizeScale == 255)
{
maxSizeScale = 0;
}
if (!int.TryParse(Convert.ToString(row["NumericPrecision"]), out maxSize) || maxSize == 255)//NumericPrecision
{
long len;
if (long.TryParse(Convert.ToString(row["ColumnSize"]), out len))
{
if (len > int.MaxValue)
{
maxSize = int.MaxValue;
}
else
{
maxSize = (int)len;
}
}
}
MCellStruct mStruct = new MCellStruct(columnName, sqlDbType, isAutoIncrement, isCanNull, maxSize);
mStruct.Scale = maxSizeScale;
mStruct.IsPrimaryKey = isKey;
mStruct.SqlTypeName = sqlTypeName;
mStruct.TableName = tableName;
mStruct.OldName = mStruct.ColumnName;
mdcs.Add(mStruct);
}
keyDt = null;
}
}
/// <summary>
/// ��ȡָ���ı�ܹ����ɵ�SQL(Create Table)���
/// </summary>
internal static string CreateTableSql(string tableName, MDataColumn columns, DalType dalType, string version)
{
switch (dalType)
{
case DalType.Txt:
case DalType.Xml:
return columns.ToJson(true);
default:
string createSql = string.Empty;
createSql = "CREATE TABLE " + SqlFormat.Keyword(tableName, dalType) + " \n(";
//��ȡ�����ĸ��������������������������������
List<MCellStruct> primaryKeyList = new List<MCellStruct>();
foreach (MCellStruct column in columns)
{
if (column.IsPrimaryKey)
{
primaryKeyList.Add(column);
}
}
foreach (MCellStruct column in columns)
{
createSql += "\n " + GetKey(column, dalType, ref primaryKeyList, version);
}
if (primaryKeyList.Count > 0)
{
createSql += GetUnionPrimaryKey(dalType, primaryKeyList);
}
createSql = createSql.TrimEnd(',') + " \n)";
// createSql += GetSuffix(dalType);
if (dalType == DalType.MySql && createSql.IndexOf("CURRENT_TIMESTAMP") != createSql.LastIndexOf("CURRENT_TIMESTAMP"))
{
createSql = createSql.Replace("Default CURRENT_TIMESTAMP", string.Empty);//mysql����������������ϵ�CURRENT_TIMESTAMP��
}
primaryKeyList.Clear();
return createSql;
}
}
/// <summary>
/// ��ȡָ���ı�ܹ����ɵ�SQL(Alter Table)���
/// </summary>
public static List<string> AlterTableSql(string tableName, MDataColumn columns, string conn)
{
List<string> sql = new List<string>();
DbBase helper = DalCreate.CreateDal(conn);
helper.ChangeDatabaseWithCheck(tableName);//���dbname.dbo.tablename�����
if (!helper.TestConn())
{
helper.Dispose();
return sql;
}
DalType dalType = helper.dalType;
string version = helper.Version;
MDataColumn dbColumn = TableSchema.GetColumns(tableName, ref helper);//��ȡ���ݿ���нṹ
helper.Dispose();
//��ʼ�Ƚ���ͬ
List<MCellStruct> primaryKeyList = new List<MCellStruct>();
string tbName = SqlFormat.Keyword(tableName, dalType);
string alterTable = "alter table " + tbName;
foreach (MCellStruct ms in columns)//�����µĽṹ
{
string cName = SqlFormat.Keyword(ms.ColumnName, dalType);
if (ms.AlterOp != AlterOp.None)
{
bool isContains = dbColumn.Contains(ms.ColumnName);
if (ms.AlterOp == AlterOp.Drop)
{
#region MyRegion
if (isContains)
{
switch (dalType)
{
case DalType.MsSql:
case DalType.Access:
case DalType.MySql:
case DalType.Oracle:
if (dalType == DalType.MsSql)
{
sql.Add(@"declare @name varchar(50) select @name =b.name from sysobjects b join syscolumns a on b.id = a.cdefault
where a.id = object_id('" + tableName + "') and a.name ='" + ms.ColumnName + "'if(@name!='') begin EXEC('alter table Base_Button drop constraint '+ @name) end");
}
sql.Add(alterTable + " drop column " + cName);
break;
case DalType.Sybase:
sql.Add(alterTable + " drop " + cName);
break;
}
}
#endregion
}
else if (ms.AlterOp == AlterOp.Rename)
{
#region MyRegion
if (!string.IsNullOrEmpty(ms.OldName) && ms.OldName != ms.ColumnName && !isContains)
{
string oName = SqlFormat.Keyword(ms.OldName, dalType);
switch (dalType)
{
case DalType.MsSql:
sql.Add("exec sp_rename '" + tbName + "." + oName + "', '" + ms.ColumnName + "', 'column'");
break;
case DalType.Sybase:
sql.Add("exec sp_rename \"" + tableName + "." + ms.OldName + "\", " + ms.ColumnName);
break;
case DalType.MySql:
sql.Add(alterTable + " change " + oName + " " + GetKey(ms, dalType, ref primaryKeyList, version).TrimEnd(','));
break;
case DalType.Oracle:
sql.Add(alterTable + " rename column " + oName + " to " + cName);
break;
}
}
#endregion
}
else if (ms.AlterOp == AlterOp.AddOrModify)
{
string alterSql = SqlFormat.Keyword(ms.ColumnName, dalType) + " " + DataType.GetDataType(ms, dalType, version);
//�����ж�
if (isContains) // ���ڣ�����
{
//����Ƿ���ͬ
MCellStruct dbStruct = dbColumn[ms.ColumnName];
if (dbStruct.IsCanNull != ms.IsCanNull || dbStruct.SqlType != ms.SqlType || dbStruct.MaxSize != ms.MaxSize || dbStruct.Scale != ms.Scale)
{
string modify = "";
switch (dalType)
{
case DalType.Oracle:
case DalType.Sybase:
modify = " modify ";
break;
case DalType.MySql:
modify = " change " + cName + " ";
break;
case DalType.MsSql:
case DalType.Access:
modify = " alter column ";
break;
}
if (ms.IsCanNull != dbStruct.IsCanNull)
{
alterSql += (ms.IsCanNull ? " NULL" : " NOT NULL");
}
sql.Add(alterTable + modify + alterSql);
}
}
else //���ڣ������
{
sql.Add(alterTable + " add " + GetKey(ms, dalType, ref primaryKeyList, version).TrimEnd(','));
if (!string.IsNullOrEmpty(ms.Description))
{
string description = SqlCreateForSchema.GetTableDescriptionSql(tableName, ms, dalType, true);
if (!string.IsNullOrEmpty(description))
{
sql.Add(description);
}
}
}
}
}
}
return sql;
}
/// <summary>
/// ��ȡָ���ı�ܹ����ɵ�SQL(Create Table)��˵�����
/// </summary>
internal static string CreateTableDescriptionSql(string tableName, MDataColumn columns, DalType dalType)
{
string result = string.Empty;
switch (dalType)
{
case DalType.MsSql:
case DalType.Oracle:
StringBuilder sb = new StringBuilder();
foreach (MCellStruct mcs in columns)
{
if (!string.IsNullOrEmpty(mcs.Description))
{
if (dalType == DalType.MsSql)
{
sb.AppendFormat("exec sp_addextendedproperty N'MS_Description', N'{0}', N'user', N'dbo', N'table', N'{1}', N'column', N'{2}';\r\n", mcs.Description, tableName, mcs.ColumnName);
}
else if (dalType == DalType.Oracle)
{
sb.AppendFormat("comment on column {0}.{1} is '{2}';\r\n", tableName.ToUpper(), mcs.ColumnName.ToUpper(), mcs.Description);
}
}
}
result = sb.ToString().TrimEnd(';');
break;
}
return result;
}
/// <summary>
/// 进行列修正（只有移除 和 修正类型，若无主键列，则新增主键列）
/// </summary>
private void FixTable(MDataColumn column)
{
if (column.Count > 0)
{
bool tableIsChange = false;
for (int i = mdt.Columns.Count - 1; i >= 0; i--)
{
if (!column.Contains(mdt.Columns[i].ColumnName))//没有此列
{
if (!tableIsChange)
{
mdt = mdt.Clone();//列需要变化时，克隆一份，不变更原有数据。
tableIsChange = true;
}
mdt.Columns.RemoveAt(i);
}
else
{
MCellStruct ms = column[mdt.Columns[i].ColumnName];//新表的字段
Type valueType = mdt.Columns[i].ValueType;//存档的字段的值的原始类型。
bool isChangeType = mdt.Columns[i].SqlType != ms.SqlType;
mdt.Columns[i].Load(ms);
if (isChangeType)
{
//修正数据的数据类型。
foreach (MDataRow row in mdt.Rows)
{
row[i].FixValue();//重新自我赋值修正数据类型。
}
}
}
}
//主键检测，若没有，则补充主键
if (column.JointPrimary != null && column.JointPrimary.Count > 0)
{
if (!mdt.Columns.Contains(column[0].ColumnName) && (column[0].IsPrimaryKey || column[0].IsAutoIncrement))
{
MCellStruct ms = column[0].Clone();
mdt = mdt.Clone();//列需要变化时，克隆一份，不变更原有数据。
ms.MDataColumn = null;
mdt.Columns.Insert(0, ms);
}
}
}
}
internal static MDataColumn GetColumns(DataTable tableSchema)
{
MDataColumn mdcs = new MDataColumn();
if (tableSchema != null && tableSchema.Rows.Count > 0)
{
mdcs.isViewOwner = true;
string columnName = string.Empty, sqlTypeName = string.Empty, tableName = string.Empty;
bool isKey = false, isCanNull = true, isAutoIncrement = false;
int maxSize = -1;
short maxSizeScale = 0;
SqlDbType sqlDbType;
string dataTypeName = "DataTypeName";
if (!tableSchema.Columns.Contains(dataTypeName))
{
dataTypeName = "DataType";
}
bool isHasAutoIncrement = tableSchema.Columns.Contains("IsAutoIncrement");
bool isHasHidden = tableSchema.Columns.Contains("IsHidden");
string hiddenFields = "," + AppConfig.DB.HiddenFields.ToLower() + ",";
for (int i = 0; i < tableSchema.Rows.Count; i++)
{
DataRow row = tableSchema.Rows[i];
tableName = Convert.ToString(row["BaseTableName"]);
mdcs.AddRelateionTableName(tableName);
if (isHasHidden && Convert.ToString(row["IsHidden"]) == "True")// !dcList.Contains(columnName))
{
continue;//�����Ǹ����������ֶΡ�
}
columnName = row["ColumnName"].ToString();
if (string.IsNullOrEmpty(columnName))
{
columnName = "Empty_" + i;
}
#region �����Ƿ�������
bool isHiddenField = hiddenFields.IndexOf("," + columnName + ",", StringComparison.OrdinalIgnoreCase) > -1;
if (isHiddenField)
{
continue;
}
#endregion
bool.TryParse(Convert.ToString(row["IsKey"]), out isKey);
bool.TryParse(Convert.ToString(row["AllowDBNull"]), out isCanNull);
// isKey = Convert.ToBoolean();//IsKey
//isCanNull = Convert.ToBoolean(row["AllowDBNull"]);//AllowDBNull
if (isHasAutoIncrement)
{
isAutoIncrement = Convert.ToBoolean(row["IsAutoIncrement"]);
}
sqlTypeName = Convert.ToString(row[dataTypeName]);
sqlDbType = DataType.GetSqlType(sqlTypeName);
if (short.TryParse(Convert.ToString(row["NumericScale"]), out maxSizeScale) && maxSizeScale == 255)
{
maxSizeScale = 0;
}
if (!int.TryParse(Convert.ToString(row["NumericPrecision"]), out maxSize) || maxSize == 255)//NumericPrecision
{
long len;
if (long.TryParse(Convert.ToString(row["ColumnSize"]), out len))
{
if (len > int.MaxValue)
{
maxSize = int.MaxValue;
}
else
{
maxSize = (int)len;
}
}
}
MCellStruct mStruct = new MCellStruct(columnName, sqlDbType, isAutoIncrement, isCanNull, maxSize);
mStruct.Scale = maxSizeScale;
mStruct.IsPrimaryKey = isKey;
mStruct.SqlTypeName = sqlTypeName;
mStruct.TableName = tableName;
mStruct.OldName = mStruct.ColumnName;
mStruct.ReaderIndex = i;
mdcs.Add(mStruct);
}
tableSchema = null;
}
return mdcs;
}
/// <summary>
/// 获取指定的表架构生成的SQL(Alter Table)的说明语句
/// </summary>
public static string GetAlterTableSql(string tableName, MDataColumn columns, string conn)
{
List<string> sqlItems = SqlCreateForSchema.AlterTableSql(tableName, columns, conn);
if (sqlItems.Count > 0)
{
StringBuilder sb = new StringBuilder();
foreach (string sql in sqlItems)
{
sb.AppendLine(sql);
}
sqlItems = null;
return sb.ToString();
}
return string.Empty;
}
/// <summary>
/// Mysql Bit ���Ͳ��������������� ���ֶ�='0' �����ԣ�
/// </summary>
/// <param name="where"></param>
/// <param name="mdc"></param>
/// <returns></returns>
internal static string FormatMySqlBit(string where, MDataColumn mdc)
{
if (where.Contains("'0'"))
{
foreach (MCellStruct item in mdc)
{
int groupID = DataType.GetGroup(item.SqlType);
if (groupID == 1 || groupID == 3)//��ͼģʽ��ȡ����bit��bigint,��������һ������
{
if (where.IndexOf(item.ColumnName, StringComparison.OrdinalIgnoreCase) > -1)
{
string pattern = " " + item.ColumnName + @"\s*=\s*'0'";
where = Regex.Replace(where, pattern, " " + item.ColumnName + "=0", RegexOptions.IgnoreCase);
}
}
}
}
return where;
}
/// <summary>
/// 获取指定的表架构生成的SQL(Alter Table)的说明语句
/// </summary>
public static string GetAlterTableSql(string tableName, MDataColumn columns)
{
return GetAlterTableSql(tableName, columns, AppConfig.DB.DefaultConn);
}
/// <summary>
/// 为指定的表架构生成SQL(Create Table)语句
/// </summary>
public static bool CreateTable(string tableName, MDataColumn columns)
{
return CreateTable(tableName, columns, AppConfig.DB.DefaultConn);
}
/// <summary>
/// 为指定的表架构生成SQL(Create Table)语句
/// </summary>
public static bool CreateTable(string tableName, MDataColumn columns, string conn)
{
if (string.IsNullOrEmpty(tableName) || tableName.Contains("(") && tableName.Contains(")"))
{
return false;
}
bool result = false;
switch (GetDalType(conn))
{
case DalType.Txt:
case DalType.Xml:
// string a, b, c;
conn = AppConfig.GetConn(conn);// CYQ.Data.DAL.DalCreate.GetConnString(conn, out a, out b, out c);
if (conn.ToLower().Contains(";ts=0"))//不写入表架构。
{
return true;
}
else
{
tableName = Path.GetFileNameWithoutExtension(tableName);
string fileName = NoSqlConnection.GetFilePath(conn) + tableName + ".ts";
result = columns.WriteSchema(fileName);
}
break;
default:
using (MProc proc = new MProc(null, conn))
{
try
{
proc.dalHelper.IsAllowRecordSql = false;
proc.SetAopOff();
proc.ResetProc(GetCreateTableSql(tableName, columns, proc.DalType, proc.DalVersion));//.Replace("\n", string.Empty)
result = proc.ExeNonQuery() > -2;
//获取扩展说明
string descriptionSql = GetCreateTableDescriptionSql(tableName, columns, proc.DalType).Replace("\r\n", string.Empty).Trim(' ', ';');
if (!string.IsNullOrEmpty(descriptionSql))
{
if (proc.DalType == DalType.Oracle)
{
foreach (string sql in descriptionSql.Split(';'))
{
proc.ResetProc(sql);
if (proc.ExeNonQuery() == -2)
{
break;
}
}
}
else
{
proc.ResetProc(descriptionSql);
proc.ExeNonQuery();
}
}
}
catch (Exception err)
{
Log.WriteLogToTxt(err);
}
}
break;
}
return result;
}
public static MDataColumn GetColumns(Type typeInfo)
{
string key = "ColumnCache:" + typeInfo.FullName;
if (columnCache.ContainsKey(key))
{
return columnCache[key].Clone();
}
else
{
#region ��ȡ�нṹ
MDataColumn mdc = new MDataColumn();
switch (StaticTool.GetSystemType(ref typeInfo))
{
case SysType.Base:
case SysType.Enum:
mdc.Add(typeInfo.Name, DataType.GetSqlType(typeInfo), false);
return mdc;
case SysType.Generic:
case SysType.Collection:
Type[] argTypes;
Tool.StaticTool.GetArgumentLength(ref typeInfo, out argTypes);
foreach (Type type in argTypes)
{
mdc.Add(type.Name, DataType.GetSqlType(type), false);
}
argTypes = null;
return mdc;
}
PropertyInfo[] pis = StaticTool.GetPropertyInfo(typeInfo);
SqlDbType sqlType;
for (int i = 0; i < pis.Length; i++)
{
sqlType = SQL.DataType.GetSqlType(pis[i].PropertyType);
mdc.Add(pis[i].Name, sqlType);
MCellStruct column = mdc[i];
column.MaxSize = DataType.GetMaxSize(sqlType);
if (i == 0)
{
column.IsPrimaryKey = true;
column.IsCanNull = false;
if (column.ColumnName.ToLower().Contains("id") && (column.SqlType == System.Data.SqlDbType.Int || column.SqlType == SqlDbType.BigInt))
{
column.IsAutoIncrement = true;
}
}
else if (i > pis.Length - 3 && sqlType == SqlDbType.DateTime && pis[i].Name.EndsWith("Time"))
{
column.DefaultValue = SqlValue.GetDate;
}
}
pis = null;
#endregion
if (!columnCache.ContainsKey(key))
{
columnCache.Add(typeInfo.FullName, mdc.Clone());
}
return mdc;
}
}
