/// <summary> /// 获取表结构 /// </summary> /// <param name="TableName"></param> /// <param name="Version"></param> /// <returns></returns> public static List <Field> GetTableSchema(string TableName, string Version = "V10") { var dtColumns = new DataTable(); List <Field> Fields = new List <Field>(); var sqlColumns = string.Format(@"SELECT * FROM INFORMATION_SCHEMA.COLUMNS t WHERE t.TABLE_NAME = '{0}' ORDER BY t.ORDINAL_POSITION", TableName); switch (Version) { case "V9": dtColumns = H3DBHelper.GetDataTable(sqlColumns); break; default: dtColumns = OThinker.H3.Controllers.AppUtility.Engine.Query.QueryTable(sqlColumns); break; } foreach (DataRow ItemCol in dtColumns.Rows) { Field field = new Field(); field.Name = ItemCol["column_name"].ToString(); field.Type = ItemCol["data_type"].ToString(); if (ItemCol["DATA_TYPE"].ToString().ToLower() == "char" || ItemCol["DATA_TYPE"].ToString().ToLower() == "nvarchar") { field.Length = ItemCol["CHARACTER_MAXIMUM_LENGTH"].ToString(); } Fields.Add(field); } return(Fields); }
/// <summary> /// 创建储存过程 /// </summary> /// <param name="temp"></param> public static void CreateProc(Temp temp) { var DropPeoc = string.Format(@"DROP PROCEDURE {0}", temp.ProcName); H3DBHelper.ExecuteNonQuery(DropPeoc); if (temp.ProCols.Count > 0) { //创建存储过程 var CreateProc = string.Format(@"CREATE PROCEDURE {0} ( @TempTable {1} Readonly ) AS BEGIN SET NOCOUNT ON BEGIN TRANSACTION DELETE FROM {2}; INSERT INTO {2} ({3}) SELECT {3} FROM @TempTable COMMIT TRANSACTION END" , temp.ProcName , temp.TypeName , temp.TableName , string.Join(",\n", temp.ProCols.ToArray())); H3DBHelper.ExecuteNonQuery(CreateProc); } }
/// <summary> /// 创建数据类型 /// </summary> /// <param name="temp"></param> public static void CreateType(Temp temp) { var DropType = string.Format(@"DROP TYPE {0}", temp.TypeName); H3DBHelper.ExecuteNonQuery(DropType); var createtype = string.Format(@"CREATE TYPE {0} AS TABLE({1})" , temp.TypeName , string.Join(",\n", temp.TypeCols.ToArray())); H3DBHelper.ExecuteNonQuery(createtype); }
private void CreateSystemTable() { var sqltable = string.Format(@"SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND Table_Name LIKE 'OT_%'"); var dt = H3DBHelper.GetDataTable(sqltable); foreach (DataRow item in dt.Rows) { CreateDBScript(item["TABLE_NAME"].ToString()); } }
/// <summary> /// 创建表 /// </summary> /// <param name="temp"></param> public static void CreateTable(Temp temp) { //在10版本中创建自定义Table var DropTable = string.Format(@"DROP Table {0}", temp.TableName); H3DBHelper.ExecuteNonQuery(DropTable); var CreateTable = string.Format(@"CREATE TABLE {0} ( {1} )" , temp.TableName , string.Join(",\n", temp.TypeCols.ToArray())); H3DBHelper.ExecuteNonQuery(CreateTable); }
public static string GetTableColumns(string TableName) { var ProCols = new List <string>(); var Schema10 = H3DBHelper.GetTableSchema(TableName, "V10"); var Schema9 = H3DBHelper.GetTableSchema(TableName, "V9"); foreach (var filed in Schema10) { var tt = Schema9.Where(a => a.Name == filed.Name); if (Schema9.Contains(filed)) { ProCols.Add("[" + filed.Name + "]"); } } return(string.Join(",\n", ProCols.ToArray())); }
private void CreateCustomerTable() { var sqltable = string.Format(@"SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND Table_Name LIKE 'I_%'"); var dt = H3DBHelper.GetDataTable(sqltable); foreach (DataRow item in dt.Rows) { var TableName = item["TABLE_NAME"].ToString(); var temp = DataBusiness.GetTableCols(TableName); DataBusiness.CreateTable(temp); DataBusiness.CreateType(temp); DataBusiness.CreateProc(temp); } }
public static void SyncTable(string[] TableName) { try { var ProcName = "Proc_" + TableName[0]; var SqlTable = GetTableSql(TableName); var dt = H3DBHelper.GetDataTable(SqlTable); var Parameters = new List <SqlParameter>(); Parameters.Add(new SqlParameter() { ParameterName = "@TempTable", Value = dt }); var Result = H3DBHelper.ExecuteProcNonQuery(ProcName, Parameters); LogHelper.Info("导入成功:" + TableName); } catch (Exception ex) { LogHelper.Debug("导入报错:" + TableName); LogHelper.Error("导入报错:" + ex.Message); } }
/// <summary> /// 角色同步 /// </summary> private void SyncRole() { //角色 //说明V10版本中的角色相当于V9版本中职务、岗位、编制的集合。所以在同步时,要进行转化 //岗位名称->角色名称 //岗位编码->角色编码 //岗位成员->角色用户 //岗位所在部门->角色管理范围 var sqlorgjob = string.Format(@"SELECT [ObjectID] ,[Code] ,[SuperiorCode] ,[DisplayName] ,[Description] ,[ParentObjectID] ,[ParentPropertyName] ,[ParentIndex] ,[Level] FROM [OT_OrgJob] ORDER BY Code"); var dtjob = H3DBHelper.GetDataTable(sqlorgjob); foreach (DataRow item in dtjob.Rows) { var orgpost = new OThinker.Organization.OrgPost { ObjectID = item["ObjectID"].ToString(), Code = item["Code"].ToString(), Name = item["DisplayName"].ToString(), JobLevel = item["Level"].ToString() == "" ? 0 : int.Parse(item["Level"].ToString()) }; var staff = string.Format(@"SELECT t3.ChildID ,t2.ParentID ,t1.Code FROM OT_OrgJob t1 ,OT_OrgPost t2 ,OT_GroupChild t3 WHERE t1.Code = t2.JobCode AND t2.ObjectID = t3.ParentObjectID AND t1.Code='{0}' ORDER BY t1.Code, t2.Code, t2.ParentID", orgpost.Code); var dtorgstaff = H3DBHelper.GetDataTable(staff); var list = new List <OThinker.Organization.OrgStaff>(); foreach (DataRow item2 in dtorgstaff.Rows) { var orgstaff = new OThinker.Organization.OrgStaff { OUScope = new string[] { item2["ParentID"].ToString() }, UserID = item2["ChildID"].ToString(), ParentObjectID = orgpost.ObjectID }; list.Add(orgstaff); } orgpost.ChildList = list.ToArray(); OThinker.H3.Controllers.AppUtility.Engine.Organization.AddUnit("", orgpost); } //编制名称->角色名称 //编制编码->角色编码 //编制成员->角色用户 //编制管理部门->角色管理范围 //组 var sqlgroup = string.Format(@"SELECT ObjectID ,Name ,Code ,ParentID FROM [OT_Group]"); var dtgroup = H3DBHelper.GetDataTable(sqlgroup); foreach (DataRow item in dtgroup.Rows) { var group = new OThinker.Organization.Group() { ObjectID = item["ObjectID"].ToString(), Name = item["Name"].ToString(), ParentID = item["ParentID"].ToString() }; var groupchild = string.Format(@"SELECT * FROM OT_GroupChild WHERE ParentObjectID = '{0}'", item["ObjectID"].ToString()); var dtchild = H3DBHelper.GetDataTable(groupchild); foreach (DataRow item2 in dtchild.Rows) { var list = new List <OThinker.Organization.GroupChild>(); var staff = new OThinker.Organization.GroupChild { ObjectID = item2["ObjectID"].ToString(), ChildID = item2["ChildID"].ToString(), ParentObjectID = item2["ParentObjectID"].ToString() }; list.Add(staff); group.ChildList = list.ToArray(); } OThinker.H3.Controllers.AppUtility.Engine.Organization.AddUnit("", group); } }