/// <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);
}
public static void BindTable(IDialogVisualizerService windowService, MDataTable dt, string title)
{
if (dt == null)
{
return;
}
if (string.IsNullOrEmpty(title))
{
title = string.Format("TableName : {0} Rows： {1} Columns： {2}", dt.TableName, dt.Rows.Count, dt.Columns.Count);
}
if (dt.Rows.Count > 200)
{
dt = dt.Select(200, null);
title = title + " Show: Top 200 Rows";
}
Form form = FormCreate.CreateForm(title);
DataGridView dg = FormCreate.CreateGrid(form);
try
{
//插入行号
MCellStruct ms = new MCellStruct("[No.]", System.Data.SqlDbType.Int);
dt.Columns.Insert(0, ms);
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i][0].Value = i + 1;
}
dt.Bind(dg);
AutoSizeColumn(dg);
}
catch (Exception err)
{
MessageBox.Show(err.Message);
}
windowService.ShowDialog(form);
}
private void BindTable(IDialogVisualizerService windowService, MDataTable dt)
{
if (dt == null)
{
return;
}
//if (dt.Rows.Count > 200)
//{
// dt = dt.Select(200, null);
//}
//插入行号
MCellStruct ms = new MCellStruct("索引", System.Data.SqlDbType.Int);
dt.Columns.Insert(0, ms);
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i][0].Value = i;
}
CollectionViewMain form = new CollectionViewMain(dt.ToDataTable());
windowService.ShowDialog(form);
}
private static void FillTable(string objName, string objCode, MDataTable dt)
{
Dictionary <string, string> fieldTitleDic = GridConfig.FieldTitle;
string errInfo;
string tableName = Convert.ToString(CrossDb.GetEnum(objCode));
MDataColumn mdc = DBTool.GetColumns(tableName, null, out errInfo);
if (mdc == null || mdc.Count == 0)
{
if (!string.IsNullOrEmpty(errInfo))
{
dt.DynamicData = errInfo;
}
return;
}
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) || i > 25);//超过25个字段，后面的都先隐藏。
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);
}
}
/// <summary>
/// 导入时，把中文列头翻译成英文列头。
/// </summary>
public static Dictionary <string, string> FormatterTitle(MDataTable dt, MDataRow info, string objName)
{
if (info == null)
{
return(GridConfig.SetHeaderField(dt, objName));
}
else
{
Dictionary <string, string> formatDic = new Dictionary <string, string>();
MDataTable config = GetExcelInfo(info.Get <string>(0));
if (config != null)
{
//附加自定义列。
foreach (var configRow in config.Rows)
{
string formatter = configRow.Get <string>(Config_ExcelInfo.Formatter);
if (!string.IsNullOrEmpty(formatter) && formatter[0] != '#')//增加默认值的列。
{
string excelName = configRow.Get <string>(Config_ExcelInfo.ExcelName);
if (!dt.Columns.Contains(excelName))
{
MCellStruct ms = new MCellStruct(excelName, System.Data.SqlDbType.NVarChar);
ms.TableName = configRow.Get <string>("TableName");
dt.Columns.Insert(dt.Columns.Count - 1, ms);
}
}
}
MDataRow row;
foreach (MCellStruct item in dt.Columns)
{
row = config.FindRow("ExcelName='" + item.ColumnName + "'");
if (row == null && item.ColumnName.IndexOf('_') > 0) // 兼容只找一级的映射列。
{
string columnName = item.ColumnName.Split('_')[0];
row = config.FindRow("ExcelName='" + columnName + "'");
}
if (row != null)
{
string field = row.Get <string>("Field");
if (string.IsNullOrEmpty(field))
{
continue;
}
if (string.Compare(item.ColumnName, field, StringComparison.OrdinalIgnoreCase) != 0)
{
item.Description = item.ColumnName;//把中文列名放到描述里。
item.TableName = row.Get <string>("TableName");
int index = dt.Columns.GetIndex(field);
if (index < 0)
{
item.ColumnName = field;//
}
else // 字段同名
{
item.ColumnName = item.TableName + "." + field;
//修改上一个，也增加表名。
dt.Columns[index].ColumnName = dt.Columns[index].TableName + "." + dt.Columns[index].ColumnName;
}
}
string formatter = row.Get <string>("Formatter");
if (!string.IsNullOrEmpty(formatter)) // 需要格式化的项
{
if (formatter.Length > 2 && formatter[0] == '#')
{
//item.SqlType = System.Data.SqlDbType.NVarChar;//重置数据类型(int数据将格式成文本）
formatDic.Add(item.ColumnName, formatter.Substring(1).Split(new string[] { "=>" }, StringSplitOptions.None)[0]);
}
else
{
item.DefaultValue = SQLCode.FormatPara(formatter);//如果不是#开头的，设置为默认值。
}
}
}
}
}
return(formatDic);
}
}
private static string GetKey(MCellStruct column, DalType dalType, ref List<MCellStruct> primaryKeyList, string version)
{
string key = SqlFormat.Keyword(column.ColumnName, dalType);//������
int groupID = DataType.GetGroup(column.SqlType);//���ݿ����͡�
bool isAutoOrPKey = column.IsPrimaryKey || column.IsAutoIncrement;//�Ƿ������������С�
if (dalType != DalType.Access || !isAutoOrPKey || !column.IsAutoIncrement)
{
SqlDbType sdt = column.SqlType;
if (sdt == SqlDbType.DateTime && dalType == DalType.MySql && Convert.ToString(column.DefaultValue) == SqlValue.GetDate)
{
sdt = SqlDbType.Timestamp;
}
key += " " + DataType.GetDataType(column, dalType, version);
}
if (isAutoOrPKey)
{
if (column.IsAutoIncrement)
{
if (primaryKeyList.Count == 0 || (!column.IsPrimaryKey && dalType == DalType.MySql))//MySql ����������������.
{
column.IsPrimaryKey = true;
primaryKeyList.Insert(0, column);
}
}
switch (dalType)
{
case DalType.Access:
if (column.IsAutoIncrement)
{
key += " autoincrement(1,1)";
}
else// ������
{
if (groupID == 4)//��������GUID
{
key += " default GenGUID()";
}
}
break;
case DalType.MsSql:
if (column.IsAutoIncrement)
{
key += " IDENTITY(1,1)";
}
else
{
if (groupID == 4)//��������GUID
{
key += " Default (newid())";
}
}
break;
case DalType.Oracle:
if (Convert.ToString(column.DefaultValue) == SqlValue.GUID)//��������GUID
{
key += " Default (SYS_GUID())";
}
break;
case DalType.Sybase:
if (column.IsAutoIncrement)
{
key += " IDENTITY";
}
else
{
if (groupID == 4)//��������GUID
{
key += " Default (newid())";
}
}
break;
case DalType.MySql:
if (column.IsAutoIncrement)
{
key += " AUTO_INCREMENT";
if (!column.IsPrimaryKey)
{
primaryKeyList.Add(column);
}
}
break;
case DalType.SQLite://sqlite��AUTOINCREMENT����д��primarykeyǰ,
if (column.IsAutoIncrement)
{
key += " PRIMARY KEY AUTOINCREMENT";
primaryKeyList.Clear();//����������ӣ�ֻ���������һ��������
}
break;
}
key += " NOT NULL";
}
else
{
string defaultValue = string.Empty;
if (Convert.ToString(column.DefaultValue).Length > 0 && groupID < 5)//Ĭ��ֵֻ���ǻ��������С�
{
if (dalType == DalType.MySql)
{
if ((groupID == 0 && (column.MaxSize < 1 || column.MaxSize > 8000)) || (groupID == 2 && key.Contains("datetime"))) //ֻ�ܶ�TIMESTAMP���͵ĸ�Ĭ��ֵ��
{
goto er;
}
}
defaultValue = SqlFormat.FormatDefaultValue(dalType, column.DefaultValue, 1, column.SqlType);
if (!string.IsNullOrEmpty(defaultValue))
{
if (dalType == DalType.MySql) { defaultValue = defaultValue.Trim('(', ')'); }
key += " Default " + defaultValue;
}
}
er:
if (dalType != DalType.Access)
{
if (dalType == DalType.Sybase && column.SqlType == SqlDbType.Bit)
{
if (string.IsNullOrEmpty(defaultValue))
{
key += " Default 0";
}
key += " NOT NULL";//Sybase bit ������ΪNull
}
else
{
key += column.IsCanNull ? " NULL" : " NOT NULL";
}
}
}
if (!string.IsNullOrEmpty(column.Description))
{
switch (dalType)
{
case DalType.MySql:
key += string.Format(" COMMENT '{0}'", column.Description.Replace("'", "''"));
break;
}
}
return key + ",";
}
/// <summary>
/// ��ȡ�����еı��������
/// </summary>
internal static string GetTableDescriptionSql(string tableName, MCellStruct mcs, DalType dalType, bool isAdd)
{
switch (dalType)
{
case DalType.MsSql:
string spName = isAdd ? "sp_addextendedproperty" : "sp_updateextendedproperty";
return string.Format("exec {3} N'MS_Description', N'{0}', N'user', N'dbo', N'table', N'{1}', N'column', N'{2}'", mcs.Description, tableName, mcs.ColumnName, spName);
case DalType.Oracle:
return string.Format("comment on column {0}.{1} is '{2}'", tableName.ToUpper(), mcs.ColumnName.ToUpper(), mcs.Description);
}
return string.Empty;
}
public override DataTable GetSchema(string collectionName, string[] restrictionValues)
{
DataTable dt = new DataTable(collectionName);
dt.Columns.Add("TABLE_NAME");
switch (collectionName.ToLower())
{
case "tables":
string[] tsList = Directory.GetFiles(DataSource, "*.ts", SearchOption.TopDirectoryOnly);
if (tsList != null && tsList.Length > 0)
{
string dalType = ".txt";
if (restrictionValues != null && restrictionValues.Length > 0)
{
if (restrictionValues[0] != null && Convert.ToString(restrictionValues[0]).ToLower() == "xml")
{
dalType = ".xml";
}
}
DataRow row = null;
string tName = string.Empty;
foreach (string tsName in tsList)
{
tName = Path.GetFileNameWithoutExtension(tsName); //获得表名。
string[] tableList = Directory.GetFiles(DataSource, tName + ".*", SearchOption.TopDirectoryOnly);
foreach (string tableName in tableList)
{
if (tableName.EndsWith(dalType))
{
continue;
}
row = dt.NewRow();
row[0] = tableName.EndsWith(".ts") ? Path.GetFileNameWithoutExtension(tableName) : Path.GetFileName(tableName);
dt.Rows.Add(row);
}
}
}
break;
case "columns":
dt.Columns.Add("COLUMN_NAME");
dt.Columns.Add("COLUMN_LCID", typeof(int));
dt.Columns.Add("DATA_TYPE", typeof(int));
dt.Columns.Add("TABLE_MaxSize", typeof(int));
dt.Columns.Add("COLUMN_ISREADONLY", typeof(bool));
dt.Columns.Add("TABLE_ISCANNULL", typeof(bool));
dt.Columns.Add("COLUMN_DEFAULT");
tsList = Directory.GetFiles(DataSource, "*.ts");
if (tsList != null && tsList.Length > 0)
{
DataRow row = null;
string tName = string.Empty;
foreach (string tsName in tsList)
{
MDataColumn mdc = MDataColumn.CreateFrom(tsName);
if (mdc.Count > 0)
{
tName = Path.GetFileNameWithoutExtension(tsName);
MCellStruct cs = null;
for (int i = 0; i < mdc.Count; i++)
{
cs = mdc[i];
row = dt.NewRow();
row[0] = tName;
row[1] = cs.ColumnName;
row[2] = i;
row[3] = (int)cs.SqlType;
row[4] = cs.MaxSize;
row[5] = cs.IsAutoIncrement;
row[6] = cs.IsCanNull;
row[7] = Convert.ToString(cs.DefaultValue);
dt.Rows.Add(row);
}
}
}
}
break;
}
return(dt);
}
public static MDataColumn GetColumns(string tableName, string conn)
{
string key = GetSchemaKey(tableName, conn);
#region 缓存检测
if (_ColumnCache.ContainsKey(key))
{
return(_ColumnCache[key].Clone());
}
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
string fullPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath + key + ".ts";
if (System.IO.File.Exists(fullPath))
{
MDataColumn columns = MDataColumn.CreateFrom(fullPath);
if (columns.Count > 0)
{
CacheManage.LocalInstance.Set(key, columns.Clone(), 1440);
return(columns);
}
}
}
#endregion
string fixName;
conn = CrossDB.GetConn(tableName, out fixName, conn ?? AppConfig.DB.DefaultConn);
tableName = fixName;
if (conn == null)
{
return(null);
}
MDataColumn mdcs = null;
using (DalBase dbHelper = DalCreate.CreateDal(conn))
{
DataBaseType dalType = dbHelper.DataBaseType;
if (dalType == DataBaseType.Txt || dalType == DataBaseType.Xml)
{
#region 文本数据库处理。
if (!tableName.Contains(" ")) // || tableName.IndexOfAny(Path.GetInvalidPathChars()) == -1
{
tableName = SqlFormat.NotKeyword(tableName); //处理database..tableName;
tableName = Path.GetFileNameWithoutExtension(tableName); //视图表，带“.“的，会出问题
string fileName = dbHelper.Con.DataSource + tableName + (dalType == DataBaseType.Txt ? ".txt" : ".xml");
mdcs = MDataColumn.CreateFrom(fileName);
mdcs.DataBaseType = dalType;
mdcs.Conn = conn;
}
#endregion
}
else
{
#region 其它数据库
mdcs = new MDataColumn();
mdcs.Conn = conn;
mdcs.TableName = tableName;
mdcs.DataBaseType = dalType;
tableName = SqlFormat.Keyword(tableName, dbHelper.DataBaseType);//加上关键字：引号
//如果table和helper不在同一个库
DalBase helper = dbHelper.ResetDalBase(tableName);
helper.IsRecordDebugInfo = false || AppDebug.IsContainSysSql;//内部系统，不记录SQL表结构语句。
try
{
bool isView = tableName.Contains(" ");//是否视图。
if (!isView)
{
isView = CrossDB.Exists(tableName, "V", conn);
}
if (!isView)
{
TableInfo info = CrossDB.GetTableInfoByName(mdcs.TableName, conn);
if (info != null)
{
mdcs.Description = info.Description;
}
}
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 DataBaseType.MsSql:
case DataBaseType.Oracle:
case DataBaseType.MySql:
case DataBaseType.Sybase:
case DataBaseType.PostgreSQL:
#region Sql
string sql = string.Empty;
if (dalType == DataBaseType.MsSql)
{
#region Mssql
string dbName = null;
if (!helper.Version.StartsWith("08"))
{
//先获取同义词，检测是否跨库
string realTableName = Convert.ToString(helper.ExeScalar(string.Format(MSSQL_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.DataBaseName);
#endregion
}
else if (dalType == DataBaseType.MySql)
{
sql = GetMySqlColumns(helper.DataBaseName);
}
else if (dalType == DataBaseType.Oracle)
{
tableName = tableName.ToUpper(); //Oracle转大写。
//先获取同义词，不检测是否跨库
string realTableName = Convert.ToString(helper.ExeScalar(string.Format(Oracle_SynonymsName, SqlFormat.NotKeyword(tableName)), false));
if (!string.IsNullOrEmpty(realTableName))
{
tableName = realTableName;
}
sql = GetOracleColumns();
}
else if (dalType == DataBaseType.Sybase)
{
tableName = SqlFormat.NotKeyword(tableName);
sql = GetSybaseColumns();
}
else if (dalType == DataBaseType.PostgreSQL)
{
sql = GetPostgreColumns();
}
helper.AddParameters("TableName", SqlFormat.NotKeyword(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的长度可能大于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.DataBaseType);
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 DataBaseType.MsSql:
case DataBaseType.MySql:
case DataBaseType.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 == DataBaseType.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 DataBaseType.SQLite:
#region SQlite
if (helper.Con.State != ConnectionState.Open)
{
helper.Con.Open();
}
DataTable sqliteDt = helper.Con.GetSchema("Columns", new string[] { null, null, SqlFormat.NotKeyword(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 == DBNull.Value)
{
len = row["CHARACTER_MAXIMUM_LENGTH"];
}
short.TryParse(Convert.ToString(row["NUMERIC_SCALE"]), out sizeScale);
if (!int.TryParse(Convert.ToString(len), out size)) //mysql的长度可能大于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 DataBaseType.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;
}
}
}
catch (Exception err)
{
Log.Write(err, LogType.DataBase);
//helper.DebugInfo.Append(err.Message);
}
#endregion
}
}
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);
}
}
}
#region 缓存设置
if (!_ColumnCache.ContainsKey(key) && mdcs.Count > 0)
{
_ColumnCache.Add(key, mdcs.Clone());
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
string folderPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath;
if (!System.IO.Directory.Exists(folderPath))
{
System.IO.Directory.CreateDirectory(folderPath);
}
mdcs.WriteSchema(folderPath + key + ".ts");
}
}
#endregion
return(mdcs);
}
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 = "DataTypeString";
if (!tableSchema.Columns.Contains(dataTypeName))
{
dataTypeName = "DataType";
if (!tableSchema.Columns.Contains(dataTypeName))
{
dataTypeName = "DataTypeName";
}
}
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);
}
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);
}
}
/// <summary>
/// 获取指定数据库的数据类型
/// </summary>
/// <param name="ms">单元格结构</param>
/// <param name="dalType">数据库类型</param>
/// <param name="version">数据库版本号</param>
/// <returns></returns>
public static string GetDataType(MCellStruct ms, DalType dalType, string version)
{
return DataType.GetDataType(ms, dalType, version);
}
/// <summary>
/// 导入时：把中文列头翻译成英文列头（同时处理列头结构）。
/// 并返回字典：key:列头,value:格式化名
/// </summary>
public static Dictionary<string, string> FormatterTitle(MDataTable dt, MDataRow info, string objName)
{
if (info == null)
{
return GridConfig.SetHeaderField(dt, objName);
}
else
{
Dictionary<string, string> formatDic = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
MDataTable infoConfig = GetExcelInfo(info.Get<string>(0));
if (infoConfig != null)
{
//附加自定义列。
foreach (var configRow in infoConfig.Rows)
{
string formatter = configRow.Get<string>(Config_ExcelInfo.Formatter);
if (!string.IsNullOrEmpty(formatter) && formatter[0] != '#')//增加默认值的列。
{
string excelName = configRow.Get<string>(Config_ExcelInfo.ExcelName);
if (!dt.Columns.Contains(excelName))
{
MCellStruct ms = new MCellStruct(excelName, System.Data.SqlDbType.NVarChar);
ms.TableName = configRow.Get<string>(Config_ExcelInfo.TableName);
dt.Columns.Insert(dt.Columns.Count - 1, ms);
}
}
}
MDataRow infoRow;
foreach (MCellStruct item in dt.Columns)
{
infoRow = infoConfig.FindRow(Config_ExcelInfo.ExcelName + "='" + item.ColumnName + "'");
if (infoRow == null && item.ColumnName.IndexOf('_') > 0) // 兼容只找一级的映射列。
{
string columnName = item.ColumnName.Split('_')[0];
infoRow = infoConfig.FindRow(Config_ExcelInfo.ExcelName + "='" + columnName + "'");
}
if (infoRow != null)
{
string field = infoRow.Get<string>(Config_ExcelInfo.Field);
if (string.IsNullOrEmpty(field))
{
continue;
}
item.Description = item.ColumnName;//把中文列名放到描述里。
item.TableName = infoRow.Get<string>(Config_ExcelInfo.TableName);
if (string.Compare(item.ColumnName, field, StringComparison.OrdinalIgnoreCase) != 0)
{
int index = dt.Columns.GetIndex(field);
if (index < 0)
{
item.ColumnName = field;//
}
else // 字段同名
{
item.ColumnName = item.TableName + "." + field;
//修改上一个，也增加表名。
dt.Columns[index].ColumnName = dt.Columns[index].TableName + "." + dt.Columns[index].ColumnName;
}
}
string formatter = infoRow.Get<string>(Config_ExcelInfo.Formatter);
if (!string.IsNullOrEmpty(formatter)) // 需要格式化的项
{
if (formatter.Length > 2 && formatter[0] == '#')
{
//item.SqlType = System.Data.SqlDbType.NVarChar;//重置数据类型(int数据将格式成文本）
formatDic.Add(item.ColumnName, formatter.Substring(1).Split(new string[] { "=>" }, StringSplitOptions.None)[0]);
}
else
{
item.DefaultValue = SqlCode.FormatPara(formatter);//如果不是#开头的，设置为默认值（同时处理@参数）。
}
}
}
}
}
return formatDic;
}
}
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;
}
}
private static string GetKey(MCellStruct column, DalType dalType, ref List <MCellStruct> primaryKeyList, string version)
{
string key = SqlFormat.Keyword(column.ColumnName, dalType); //列名。
int groupID = DataType.GetGroup(column.SqlType); //数据库类型。
bool isAutoOrPKey = column.IsPrimaryKey || column.IsAutoIncrement; //是否主键或自增列。
if (dalType != DalType.Access || !isAutoOrPKey || !column.IsAutoIncrement)
{
SqlDbType sdt = column.SqlType;
if (sdt == SqlDbType.DateTime && dalType == DalType.MySql && Convert.ToString(column.DefaultValue) == SqlValue.GetDate)
{
sdt = SqlDbType.Timestamp;
}
key += " " + DataType.GetDataType(column, dalType, version);
}
if (isAutoOrPKey)
{
if (column.IsAutoIncrement)
{
if (primaryKeyList.Count == 0 || (!column.IsPrimaryKey && dalType == DalType.MySql))//MySql 的自增必须是主键.
{
column.IsPrimaryKey = true;
primaryKeyList.Insert(0, column);
}
}
switch (dalType)
{
case DalType.Access:
if (column.IsAutoIncrement)
{
key += " autoincrement(1,1)";
}
else // 主键。
{
if (groupID == 4) //主键又是GUID
{
key += " default GenGUID()";
}
}
break;
case DalType.MsSql:
if (column.IsAutoIncrement)
{
key += " IDENTITY(1,1)";
}
else
{
if (groupID == 4) //主键又是GUID
{
key += " Default (newid())";
}
}
break;
case DalType.Oracle:
if (Convert.ToString(column.DefaultValue) == SqlValue.GUID) //主键又是GUID
{
key += " Default (SYS_GUID())";
}
break;
case DalType.Sybase:
if (column.IsAutoIncrement)
{
key += " IDENTITY";
}
else
{
if (groupID == 4) //主键又是GUID
{
key += " Default (newid())";
}
}
break;
case DalType.MySql:
if (column.IsAutoIncrement)
{
key += " AUTO_INCREMENT";
if (!column.IsPrimaryKey)
{
primaryKeyList.Add(column);
}
}
break;
case DalType.SQLite: //sqlite的AUTOINCREMENT不能写在primarykey前,
if (column.IsAutoIncrement)
{
key += " PRIMARY KEY AUTOINCREMENT";
primaryKeyList.Clear(); //如果有自增加，只允许存在这一个主键。
}
break;
}
key += " NOT NULL";
}
else
{
string defaultValue = string.Empty;
if (Convert.ToString(column.DefaultValue).Length > 0 && groupID < 5)//默认值只能是基础类型有。
{
if (dalType == DalType.MySql)
{
if ((groupID == 0 && (column.MaxSize < 1 || column.MaxSize > 8000)) || (groupID == 2 && key.Contains("datetime"))) //只能对TIMESTAMP类型的赋默认值。
{
goto er;
}
}
defaultValue = SqlFormat.FormatDefaultValue(dalType, column.DefaultValue, 1, column.SqlType);
if (!string.IsNullOrEmpty(defaultValue))
{
if (dalType == DalType.MySql)
{
defaultValue = defaultValue.Trim('(', ')');
}
key += " Default " + defaultValue;
}
}
er:
if (dalType != DalType.Access)
{
if (dalType == DalType.Sybase && column.SqlType == SqlDbType.Bit)
{
if (string.IsNullOrEmpty(defaultValue))
{
key += " Default 0";
}
key += " NOT NULL";//Sybase bit 不允许为Null
}
else
{
key += column.IsCanNull ? " NULL" : " NOT NULL";
}
}
}
if (!string.IsNullOrEmpty(column.Description))
{
switch (dalType)
{
case DalType.MySql:
key += string.Format(" COMMENT '{0}'", column.Description.Replace("'", "''"));
break;
}
}
return(key + ",");
}
/// <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);
}
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>
/// 获取指定数据库的数据类型
/// </summary>
/// <param name="ms">单元格结构</param>
/// <param name="dalType">数据库类型</param>
/// <param name="version">数据库版本号</param>
/// <returns></returns>
public static string GetDataType(MCellStruct ms, DalType dalType, string version)
{
return(DataType.GetDataType(ms, dalType, version));
}
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;
}
