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