public override IList <SchemaObject> Create(SchemaFile file) { var newSchemaList = new List <SchemaObject>(); foreach (var createTypeTableStatement in Fragments) { var columns = createTypeTableStatement .Definition .ColumnDefinitions .GetFields(Logger, file) .ToList(); var dbObject = new UserDefinedTableType() { Database = createTypeTableStatement.Name.DatabaseIdentifier?.Value ?? file.Context.Name, Schema = createTypeTableStatement.Name.SchemaIdentifier?.Value ?? SchemaObject.DefaultSchema, Identifier = createTypeTableStatement.Name.BaseIdentifier.Value, File = file, Columns = columns, }; newSchemaList.Add(dbObject); } return(newSchemaList); }
private void HandleUserDefinedTableType(UserDefinedTableType userDefinedTableType) { var scripter = new Scripter(_server) { Options = { ScriptData = true, IncludeHeaders = false } }; string[] script = scripter.EnumScript(new SqlSmoObject[] { userDefinedTableType }).ToArray(); if (script.Length != 1) { throw new InvalidOperationException(string.Format(CultureInfo.CurrentCulture, "Could not script user defined table type. Expected one line but was: {0}", string.Join(Environment.NewLine, script))); } AppendLine("db.Execute(@\"{0}\");", 3, script[0]); }
/// <summary> /// Script a UserDefinedTableType using SMO /// </summary> /// <param name="objectName">UserDefinedTableType name</param> /// <param name="schemaName">Schema name</param> /// <returns>String collection of scripts</returns> internal StringCollection GetUserDefinedTableTypeScripts(string objectName, string schemaName, ScriptingOptions scriptingOptions = null) { try { UserDefinedTableType smoObject = string.IsNullOrEmpty(schemaName) ? new UserDefinedTableType(this.Database, objectName) : new UserDefinedTableType(this.Database, objectName, schemaName); smoObject.Refresh(); return(smoObject.Script()); } catch (Exception ex) { Logger.Write(LogLevel.Error, "Exception at PeekDefinition GetUserDefinedTableTypeScripts : " + ex.Message); return(null); } }
public static string ScriptUserDefinedTableType( UserDefinedTableType tableType ) { var options = new ScriptingOptions(); options.DriIncludeSystemNames = true; options.NoFileGroup = true; options.NoCollation = true; var returnSQL = new StringBuilder(); var dbScript = tableType.Script(options); foreach (var item in dbScript) { returnSQL.AppendLine(item); } return(returnSQL.ToString()); }
public void TestSetup() { //start with default options string connectionString = ConfigurationManager.AppSettings["ConnectionString"]; string[] options = { connectionString }; RunOptions.Current.Logger = new TestLogger(); RunOptions.Current.Init(options); SqlConnection connection = new SqlConnection(connectionString); ServerConnection serverConnection = new ServerConnection(connection); Server server = new Server(serverConnection); Microsoft.SqlServer.Management.Smo.Database database = server.Databases[connection.Database]; if (database == null) { throw new ApplicationException("Database " + connection.Database + " not found."); } server.SetDefaultInitFields(typeof(View), true); database.PrefetchObjects(typeof(View)); for (int i = database.Views.Count - 1; i >= 0; i--) { View view = database.Views[i]; if (view.IsSystemObject) { continue; } view.Drop(); } server.SetDefaultInitFields(typeof(Table), true); database.PrefetchObjects(typeof(Table)); foreach (Table table in database.Tables) { if (table.IsSystemObject) { continue; } for (int i = table.ForeignKeys.Count - 1; i >= 0; i--) { table.ForeignKeys[i].Drop(); } } for (int i = database.Tables.Count - 1; i >= 0; i--) { Table table = database.Tables[i]; if (table.IsSystemObject) { continue; } table.Drop(); } server.SetDefaultInitFields(typeof(StoredProcedure), true); database.PrefetchObjects(typeof(StoredProcedure)); for (int i = database.StoredProcedures.Count - 1; i >= 0; i--) { StoredProcedure procedure = database.StoredProcedures[i]; if (procedure.IsSystemObject) { continue; } procedure.Drop(); } server.SetDefaultInitFields(typeof(UserDefinedFunction), true); database.PrefetchObjects(typeof(UserDefinedFunction)); for (int i = database.UserDefinedFunctions.Count - 1; i >= 0; i--) { UserDefinedFunction function = database.UserDefinedFunctions[i]; if (function.IsSystemObject) { continue; } function.Drop(); } server.SetDefaultInitFields(typeof(UserDefinedTableType), true); database.PrefetchObjects(typeof(UserDefinedTableType)); for (int i = database.UserDefinedTableTypes.Count - 1; i >= 0; i--) { UserDefinedTableType tableType = database.UserDefinedTableTypes[i]; if (!tableType.IsUserDefined) { continue; } tableType.Drop(); } server.SetDefaultInitFields(typeof(FullTextCatalog), true); for (int i = database.FullTextCatalogs.Count - 1; i >= 0; i--) { FullTextCatalog catalog = database.FullTextCatalogs[i]; catalog.Drop(); } }
private void _TreeView_AfterSelect(object sender, TreeViewEventArgs e) { Cursor csr = null; try { csr = this.Cursor; this.Cursor = Cursors.WaitCursor; // 清空右边的 detail 类控件显示 _SplitContainer.Panel2.Controls.Clear(); TreeNode node = e.Node; object tag = node.Tag; Type tagType = tag.GetType(); if (tag == null) { node.Tag = node.Text; // 保持 tag 不空,方便判断 } if (typeof(Database) == tagType && node.Nodes.Count == 0) { #region Make Empty Sub Tree Nodes TreeNode tn; tn = node.Nodes.Add("Tables"); tn.SelectedImageKey = tn.ImageKey = "SQL_Folder.png"; tn.Tag = "Tables"; tn = node.Nodes.Add("Views"); tn.SelectedImageKey = tn.ImageKey = "SQL_Folder.png"; tn.Tag = "Views"; tn = node.Nodes.Add("Stored Procedures"); tn.SelectedImageKey = tn.ImageKey = "SQL_Folder.png"; tn.Tag = "Stored Procedures"; tn = node.Nodes.Add("Functions"); tn.SelectedImageKey = tn.ImageKey = "SQL_Folder.png"; tn.Tag = "Functions"; tn = node.Nodes.Add("UserDefinedDataTypes"); tn.SelectedImageKey = tn.ImageKey = "SQL_Folder.png"; tn.Tag = "UserDefinedDataTypes"; Database db = (Database)tag; // 根据 SQL 的库的版本来决定是否生成某些节点 if (db.CompatibilityLevel >= CompatibilityLevel.Version100) { tn = node.Nodes.Add("UserDefinedTableTypes"); tn.SelectedImageKey = tn.ImageKey = "SQL_Folder.png"; tn.Tag = "UserDefinedTableTypes"; } if (db.CompatibilityLevel >= CompatibilityLevel.Version90) { tn = node.Nodes.Add("User Schema"); tn.SelectedImageKey = tn.ImageKey = "SQL_Folder.png"; tn.Tag = "User Schema"; } tn = node.Nodes.Add("Extended Properties"); tn.SelectedImageKey = tn.ImageKey = "SQL_Folder.png"; tn.Tag = "Extended Properties"; #endregion } else if (typeof(Database) == tagType && node.Nodes.Count > 0) { Database db = (Database)tag; // 不再重复的生成子节点 } else if (typeof(Table) == tagType) { Table t = (Table)tag; CTable ct = new CTable(t); ct.Dock = DockStyle.Fill; _SplitContainer.Panel2.Controls.Add(ct); } else if (typeof(UserDefinedTableType) == tagType) { UserDefinedTableType t = (UserDefinedTableType)tag; CUserTableType ct = new CUserTableType(t); ct.Dock = DockStyle.Fill; _SplitContainer.Panel2.Controls.Add(ct); // FMain_UI_Binder.MakeByTable(_ToolStrip, t); } else if (typeof(Microsoft.SqlServer.Management.Smo.View) == tagType) { Microsoft.SqlServer.Management.Smo.View v = (Microsoft.SqlServer.Management.Smo.View)tag; CView cv = new CView(v); cv.Dock = DockStyle.Fill; _SplitContainer.Panel2.Controls.Add(cv); } else if (typeof(Column) == tagType) { // todo } else if (typeof(StoredProcedure) == tagType) { StoredProcedure sp = (StoredProcedure)tag; CStoredProcedure csp = new CStoredProcedure(sp); csp.Dock = DockStyle.Fill; _SplitContainer.Panel2.Controls.Add(csp); } else if (typeof(UserDefinedFunction) == tagType) { UserDefinedFunction fun = (UserDefinedFunction)tag; CFunction cfun = new CFunction(fun); cfun.Dock = DockStyle.Fill; _SplitContainer.Panel2.Controls.Add(cfun); } else if (typeof(ExtendedProperty) == tagType) { ExtendedProperty ep = (ExtendedProperty)tag; CExtendedProperty cep = new CExtendedProperty(ep); cep.Dock = DockStyle.Fill; _SplitContainer.Panel2.Controls.Add(cep); } else if (typeof(string) == tagType) { string name = (string)tag; if (name == "Tables") { Database db = (Database)node.Parent.Tag; if (node.Nodes.Count == 0) { List <Table> uts = Utils.GetUserTables(db); foreach (Table tbl in uts) { TreeNode tn = new TreeNode(tbl.Schema + "." + tbl.Name); tn.SelectedImageKey = tn.ImageKey = "SQL_Table.png"; tn.Tag = tbl; node.Nodes.Add(tn); foreach (Column c in tbl.Columns) { TreeNode n = new TreeNode(c.Name); n.SelectedImageKey = n.ImageKey = "SQL_Column.png"; n.Tag = c; tn.Nodes.Add(n); } foreach (ExtendedProperty ep in tbl.ExtendedProperties) { TreeNode n = new TreeNode(ep.Name); n.SelectedImageKey = n.ImageKey = "sql_constrain.png"; n.Tag = ep; tn.Nodes.Add(n); } } } // ... } else if (name == "Views") { Database db = (Database)node.Parent.Tag; if (node.Nodes.Count == 0) { List <Microsoft.SqlServer.Management.Smo.View> uvs = Utils.GetUserViews(db); foreach (Microsoft.SqlServer.Management.Smo.View v in uvs) { TreeNode tn = new TreeNode(v.Schema + "." + v.Name); tn.SelectedImageKey = tn.ImageKey = "SQL_Table.png"; tn.Tag = v; node.Nodes.Add(tn); foreach (Column c in v.Columns) { TreeNode n = new TreeNode(c.Name); n.SelectedImageKey = n.ImageKey = "SQL_Column.png"; n.Tag = c; tn.Nodes.Add(n); } foreach (ExtendedProperty ep in v.ExtendedProperties) { TreeNode n = new TreeNode(ep.Name); n.SelectedImageKey = n.ImageKey = "sql_constrain.png"; n.Tag = ep; tn.Nodes.Add(n); } } } } else if (name == "Stored Procedures") { Database db = (Database)node.Parent.Tag; if (node.Nodes.Count == 0) { List <StoredProcedure> sps = Utils.GetUserStoredProcedures(db); foreach (StoredProcedure sp in sps) { TreeNode tn = new TreeNode(sp.Schema + "." + sp.Name); tn.SelectedImageKey = tn.ImageKey = "SQL_StoredProcedure.png"; tn.Tag = sp; node.Nodes.Add(tn); foreach (ExtendedProperty ep in sp.ExtendedProperties) { TreeNode n = new TreeNode(ep.Name); n.SelectedImageKey = n.ImageKey = "sql_constrain.png"; n.Tag = ep; tn.Nodes.Add(n); } } } } else if (name == "Functions") { Database db = (Database)node.Parent.Tag; if (node.Nodes.Count == 0) { List <UserDefinedFunction> ufs = Utils.GetUserFunctions(db); foreach (UserDefinedFunction fun in ufs) { TreeNode tn = new TreeNode(fun.Schema + "." + fun.Name); if (fun.FunctionType == UserDefinedFunctionType.Table || fun.FunctionType == UserDefinedFunctionType.Inline) { tn.SelectedImageKey = tn.ImageKey = "SQL_Function_Table.png"; } else { tn.SelectedImageKey = tn.ImageKey = "SQL_Function_Scale.png"; } tn.Tag = fun; node.Nodes.Add(tn); foreach (ExtendedProperty ep in fun.ExtendedProperties) { TreeNode n = new TreeNode(ep.Name); n.SelectedImageKey = n.ImageKey = "sql_constrain.png"; n.Tag = ep; tn.Nodes.Add(n); } } } } else if (name == "UserDefinedDataTypes") { Database db = (Database)node.Parent.Tag; if (node.Nodes.Count == 0) { List <UserDefinedDataType> uddts = Utils.GetUserDefinedDataTypes(db); foreach (UserDefinedDataType o in uddts) { TreeNode tn = new TreeNode(o.Schema + "." + o.Name + "(" + o.SystemType + " " + o.MaxLength.ToString() + ")"); tn.SelectedImageKey = tn.ImageKey = "SQL_Function_Scale.png"; tn.Tag = o; node.Nodes.Add(tn); } } } else if (name == "UserDefinedTableTypes") { Database db = (Database)node.Parent.Tag; if (node.Nodes.Count == 0) { List <UserDefinedTableType> utts = Utils.GetUserDefinedTableTypes(db); foreach (UserDefinedTableType tbl in utts) { TreeNode tn = new TreeNode(tbl.Schema + "." + tbl.Name); tn.SelectedImageKey = tn.ImageKey = "SQL_Table.png"; tn.Tag = tbl; node.Nodes.Add(tn); foreach (Column c in tbl.Columns) { TreeNode n = new TreeNode(c.Name); n.SelectedImageKey = n.ImageKey = "SQL_Column.png"; n.Tag = c; tn.Nodes.Add(n); } foreach (ExtendedProperty ep in tbl.ExtendedProperties) { TreeNode n = new TreeNode(ep.Name); n.SelectedImageKey = n.ImageKey = "sql_constrain.png"; n.Tag = ep; tn.Nodes.Add(n); } } } } else if (name == "User Schema") { Database db = (Database)node.Parent.Tag; if (node.Nodes.Count == 0) { List <Schema> uss = Utils.GetUserSchemas(db); foreach (Schema a in uss) { TreeNode tn = new TreeNode(a.Name); tn.SelectedImageKey = tn.ImageKey = "SQL_Schema.png"; tn.Tag = a; node.Nodes.Add(tn); } } } else if (name == "Extended Properties") { Database db = (Database)node.Parent.Tag; if (node.Nodes.Count == 0) { List <ExtendedProperty> eps = Utils.GetExtendedProperties(db); foreach (ExtendedProperty a in eps) { TreeNode tn = new TreeNode(a.Name); tn.SelectedImageKey = tn.ImageKey = "SQL_Schema.png"; tn.Tag = a; node.Nodes.Add(tn); } } } } } catch (Exception ex) { MessageBox.Show(ex.Message); } finally { this.Cursor = csr; // Restore the original cursor } }
public static string Gen(UserDefinedTableType t) { StringBuilder sb = new StringBuilder(); return(sb.ToString()); }
public UserDefinedTableTypeSourceCodeFileClassFactory(UserDefinedTableType userDefinedTableType) { this.UserDefinedTableType = userDefinedTableType; }
public CUserTableType(UserDefinedTableType t) { InitializeComponent(); _t = t; }
public static string Gen(Database db, string ns, string oon2) { #region Header Server server = db.Parent; string s = "", s1 = ""; List <StoredProcedure> sps = Utils.GetUserStoredProcedures(db); List <Table> uts = Utils.GetUserTables(db); List <View> uvs = Utils.GetUserViews(db); List <UserDefinedFunction> ufs = Utils.GetUserFunctions(db); List <UserDefinedTableType> udtts = Utils.GetUserDefinedTableTypes(db); StringBuilder sb = new StringBuilder(@"using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; using System.Text; namespace " + ns + @" { public static partial class OB {" ); #endregion #region SPs sb.Append(@" #region Stored Procedures "); foreach (StoredProcedure sp in sps) { string spn = Utils.GetEscapeName(sp); // 方法名 string mn = Utils.GetMethodName(sp); if (string.IsNullOrEmpty(mn)) { mn = spn; } // 架构名 string sn = Utils.GetEscapeName(sp.Schema); // 最终方法名 mn = (Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportSchema ? (sn + "_") : ("")) + mn; // 结果集类型(表名或视图名) string rt = Utils.GetResultType(sp); // 结果集类型所属 Schema string rts = Utils.GetResultTypeSchema(sp); // 兼容老的设定(拆分 rt 的内容为 rt + rts)(rts 为空时 rt 的值应为 int, dt, ds, object 之一。。。以 “[” 开头必然为 [schema].[name] 结构) if (string.IsNullOrEmpty(rts)) { if (rt.StartsWith("[")) { rts = rt.Substring(1, rt.IndexOf("].[", 0) - 1); rt = rt.Substring(rts.Length + 4, rt.Length - rts.Length - 5); } } //返回值是否为单行 bool isSingleLine = Utils.GetIsSingleLineResult(sp); // 返回值的描述(如果不是 int, dt, ds, object 而是指向某个表或表类型,则在前面加上 单行或多行的描述性文字) string rtn = isSingleLine ? "单行" : "多行"; if (!string.IsNullOrEmpty(rts)) { rtn = rtn + rt; } // 所属行为 string behavior = Utils.GetBehavior(sp); // 单行的结果集类型名 string srt = rt; // backup string sqlrt = rt; // 将 rt 转为 c# result type if (!string.IsNullOrEmpty(rts)) { object o = uts.Find(delegate(Table t) { return(t.Name == rt && t.Schema == rts); }); if (o != null) { string btn = Utils.GetEscapeName((Table)o); rt = "OO." + btn + "Collection"; srt = "OO." + btn; } else { o = uvs.Find(delegate(View t) { return(t.Name == rt && t.Schema == rts); }); if (o != null) { string btn = Utils.GetEscapeName((View)o); rt = "OO." + btn + "Collection"; srt = "OO." + btn; } else { o = udtts.Find(delegate(UserDefinedTableType t) { System.Diagnostics.Debug.Print(t.Name + " " + t.Schema); return(t.Name == rt && t.Schema == rts); }); if (o != null) { string btn = Utils.GetEscapeName((UserDefinedTableType)o); rt = oon2 + "." + btn + "Collection"; srt = oon2 + "." + btn; } else { rt = Utils.EP_ResultType_DataTable; // 如果指向的数据库对象未找到,则将结果设为 DataTable } } } } sb.Append(Utils.GetSummary(sp, 2)); for (int j = 0; j < sp.Parameters.Count; j++) { StoredProcedureParameter p = sp.Parameters[j]; string pn = Utils.GetEscapeName(p); string psum = Utils.GetDescription(p); if (!string.IsNullOrEmpty(psum)) { sb.Append(@" /// <param name=""" + pn + @""">" + psum + @"</param>"); } } sb.Append(@" /// <returns>" + rtn + @"</returns>"); sb.Append(@" public static " + (isSingleLine ? srt : rt) + @" " + mn + @"("); for (int j = 0; j < sp.Parameters.Count; j++) { StoredProcedureParameter p = sp.Parameters[j]; string pn = Utils.GetEscapeName(p); string typename; if (j > 0) { sb.Append(@", "); } if (p.DataType.SqlDataType == SqlDataType.UserDefinedTableType) { typename = oon2 + "." + Utils.GetEscapeName(p.DataType) + "Collection"; } else { typename = Utils.GetNullableDataType(p); } sb.Append(@" " + (p.IsOutputParameter ? "ref" : "") + @" " + typename + " " + pn); } sb.Append(@") { SqlCommand cmd = DC.NewCmd_" + spn + @"();"); foreach (StoredProcedureParameter p in sp.Parameters) { string pn = Utils.GetEscapeName(p); sb.Append(@" if (" + pn + @" == null) cmd.Parameters[""" + pn + @"""].Value = DBNull.Value; else cmd.Parameters[""" + pn + @"""].Value = " + pn + (p.DataType.SqlDataType == SqlDataType.UserDefinedTableType ? ".GetDataTable()" : "") + ";"); } if (rt == Utils.EP_ResultType_Int) { sb.Append(@" SQLHelper.ExecuteNonQuery(cmd);" ); foreach (StoredProcedureParameter p in sp.Parameters) { if (!p.IsOutputParameter) { continue; } string pn = Utils.GetEscapeName(p); string typename = Utils.GetNullableDataType(p); if (Utils.CheckIsStringType(p)) { sb.Append(@" " + pn + @" = cmd.Parameters[""" + pn + @"""].Value as string;"); } else { sb.Append(@" if (cmd.Parameters[""" + pn + @"""].Value == null || cmd.Parameters[""" + pn + @"""].Value == DBNull.Value) " + pn + @" = null; else " + pn + @" = new " + typename + @"((" + Utils.GetDataType(p) + @")cmd.Parameters[""" + pn + @"""].Value);"); } } sb.Append(@" string s = cmd.Parameters[""RETURN_VALUE""].Value.ToString(); if (string.IsNullOrEmpty(s)) return 0; return int.Parse(s); }" ); } else if (rt == Utils.EP_ResultType_DataSet) { sb.Append(@" DataSet ds = SQLHelper.ExecuteDataSet(cmd);" ); foreach (StoredProcedureParameter p in sp.Parameters) { if (!p.IsOutputParameter) { continue; } string pn = Utils.GetEscapeName(p); string typename = Utils.GetNullableDataType(p); if (Utils.CheckIsStringType(p)) { sb.Append(@" " + pn + @" = cmd.Parameters[""" + pn + @"""].Value as string;"); } else { sb.Append(@" if (cmd.Parameters[""" + pn + @"""].Value == null || cmd.Parameters[""" + pn + @"""].Value == DBNull.Value) " + pn + @" = null; else " + pn + @" = new " + typename + @"((" + Utils.GetDataType(p) + @")cmd.Parameters[""" + pn + @"""].Value);"); } } sb.Append(@" return ds; }" ); } else if (rt == Utils.EP_ResultType_DataTable) { sb.Append(@" DataTable dt = SQLHelper.ExecuteDataTable(cmd);" ); foreach (StoredProcedureParameter p in sp.Parameters) { if (!p.IsOutputParameter) { continue; } string pn = Utils.GetEscapeName(p); string typename = Utils.GetNullableDataType(p); if (Utils.CheckIsStringType(p)) { sb.Append(@" " + pn + @" = cmd.Parameters[""" + pn + @"""].Value as string;"); } else { sb.Append(@" if (cmd.Parameters[""" + pn + @"""].Value == null || cmd.Parameters[""" + pn + @"""].Value == DBNull.Value) " + pn + @" = null; else " + pn + @" = new " + typename + @"((" + Utils.GetDataType(p) + @")cmd.Parameters[""" + pn + @"""].Value);"); } } sb.Append(@" return dt; }" ); } else if (rt == Utils.EP_ResultType_Object) { sb.Append(@" Object result = SQLHelper.ExecuteScalar(cmd);" ); foreach (StoredProcedureParameter p in sp.Parameters) { if (!p.IsOutputParameter) { continue; } string pn = Utils.GetEscapeName(p); string typename = Utils.GetNullableDataType(p); if (Utils.CheckIsStringType(p)) { sb.Append(@" " + pn + @" = cmd.Parameters[""" + pn + @"""].Value as string;"); } else { sb.Append(@" if (cmd.Parameters[""" + pn + @"""].Value == null || cmd.Parameters[""" + pn + @"""].Value == DBNull.Value) " + pn + @" = null; else " + pn + @" = new " + typename + @"((" + Utils.GetDataType(p) + @")cmd.Parameters[""" + pn + @"""].Value);"); } } sb.Append(@" return result; }" ); } else { sb.Append(@" " + rt + @" os = new " + rt + @"(); using(SqlDataReader sdr = SQLHelper.ExecuteDataReader(cmd)) { while(sdr.Read()) { os.Add(new " + srt + @"("); if (uts.Find(delegate(Table target) { return(target.Name == sqlrt && target.Schema == rts); }) != null) { Table tb = uts.Find(delegate(Table target) { return(target.Name == sqlrt && target.Schema == rts); }); for (int j = 0; j < tb.Columns.Count; j++) { Column c = tb.Columns[j]; if (j > 0) { sb.Append(@", "); } if (c.Nullable) { s1 = Utils.CheckIsBinaryType(c) ? ("sdr.GetSqlBinary(" + j + @").Value") : (Utils.CheckIsValueType(c) ? ("new " + Utils.GetNullableDataType(c) + @"(sdr." + Utils.GetDataReaderMethod(c) + @"(" + j + @"))") : ("sdr." + Utils.GetDataReaderMethod(c) + @"(" + j + @")")); sb.Append(@"sdr.IsDBNull(" + j + @") ? null : " + s1); } else { if (Utils.CheckIsBinaryType(c)) { sb.Append(@"sdr.GetSqlBinary(" + j + @").Value"); } else { sb.Append(@"sdr." + Utils.GetDataReaderMethod(c) + @"(" + j + @")"); } } } } else if (uvs.Find(delegate(View target) { return(target.Name == sqlrt && target.Schema == rts); }) != null) { View tb = uvs.Find(delegate(View target) { return(target.Name == sqlrt && target.Schema == rts); }); for (int j = 0; j < tb.Columns.Count; j++) { Column c = tb.Columns[j]; if (j > 0) { sb.Append(@", "); } if (c.Nullable) { s1 = Utils.CheckIsBinaryType(c) ? ("sdr.GetSqlBinary(" + j + @").Value") : (Utils.CheckIsValueType(c) ? ("new " + Utils.GetNullableDataType(c) + @"(sdr." + Utils.GetDataReaderMethod(c) + @"(" + j + @"))") : ("sdr." + Utils.GetDataReaderMethod(c) + @"(" + j + @")")); sb.Append(@"sdr.IsDBNull(" + j + @") ? null : " + s1); } else { if (Utils.CheckIsBinaryType(c)) { sb.Append(@"sdr.GetSqlBinary(" + j + @").Value"); } else { sb.Append(@"sdr." + Utils.GetDataReaderMethod(c) + @"(" + j + @")"); } } } } else { UserDefinedTableType tb = udtts.Find(delegate(UserDefinedTableType target) { return(target.Name == sqlrt && target.Schema == rts); }); for (int j = 0; j < tb.Columns.Count; j++) { Column c = tb.Columns[j]; if (j > 0) { sb.Append(@", "); } if (c.Nullable) { s1 = Utils.CheckIsBinaryType(c) ? ("sdr.GetSqlBinary(" + j + @").Value") : (Utils.CheckIsValueType(c) ? ("new " + Utils.GetNullableDataType(c) + @"(sdr." + Utils.GetDataReaderMethod(c) + @"(" + j + @"))") : ("sdr." + Utils.GetDataReaderMethod(c) + @"(" + j + @")")); sb.Append(@"sdr.IsDBNull(" + j + @") ? null : " + s1); } else { if (Utils.CheckIsBinaryType(c)) { sb.Append(@"sdr.GetSqlBinary(" + j + @").Value"); } else { sb.Append(@"sdr." + Utils.GetDataReaderMethod(c) + @"(" + j + @")"); } } } } sb.Append(@")); } } "); foreach (StoredProcedureParameter p in sp.Parameters) { if (!p.IsOutputParameter) { continue; } string pn = Utils.GetEscapeName(p); string typename = Utils.GetNullableDataType(p); if (Utils.CheckIsStringType(p)) { sb.Append(@" " + pn + @" = cmd.Parameters[""" + pn + @"""].Value as string;"); } else { sb.Append(@" if (cmd.Parameters[""" + pn + @"""].Value == null || cmd.Parameters[""" + pn + @"""].Value == DBNull.Value) " + pn + @" = null; else " + pn + @" = new " + typename + @"((" + Utils.GetDataType(p) + @")cmd.Parameters[""" + pn + @"""].Value);"); } } if (isSingleLine) { sb.Append(@" if (os.Count > 0) return os[0]; return null;" ); } else { sb.Append(@" return os;" ); } sb.Append(@" }" ); } sb.Append(Utils.GetSummary(sp, 2)); sb.Append(@" /// <returns>" + rtn + @"</returns>"); if (string.IsNullOrEmpty(behavior) || behavior == "None") { } else { sb.Append(@" [System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType." + behavior + @")]"); } sb.Append(@" public static " + (isSingleLine ? srt : rt) + @" " + mn + @"(DI." + spn + @"Parameters p) { SqlCommand cmd = DC.NewCmd_" + spn + @"(p);"); foreach (StoredProcedureParameter p in sp.Parameters) { string pn = Utils.GetEscapeName(p); sb.Append(@" if (p.CheckIs" + pn + @"Changed()) { object o = p." + pn + @"; if (o == null) cmd.Parameters[""" + pn + @"""].Value = DBNull.Value; else cmd.Parameters[""" + pn + @"""].Value = o; }" ); } if (rt == Utils.EP_ResultType_Int) { sb.Append(@" SQLHelper.ExecuteNonQuery(cmd);" ); foreach (StoredProcedureParameter p in sp.Parameters) { if (!p.IsOutputParameter) { continue; } string pn = Utils.GetEscapeName(p); string typename = Utils.GetNullableDataType(p); if (Utils.CheckIsStringType(p)) { sb.Append(@" p." + pn + @" = cmd.Parameters[""" + pn + @"""].Value as string;"); } else { sb.Append(@" if (cmd.Parameters[""" + pn + @"""].Value == null || cmd.Parameters[""" + pn + @"""].Value == DBNull.Value) p." + pn + @" = null; else p." + pn + @" = new " + typename + @"((" + Utils.GetDataType(p) + @")cmd.Parameters[""" + pn + @"""].Value);"); } } sb.Append(@" string s = cmd.Parameters[""RETURN_VALUE""].Value.ToString(); if (string.IsNullOrEmpty(s)) { p.SetReturnValue(0); return 0; } else { p.SetReturnValue(int.Parse(s)); return p._ReturnValue; } }" ); } else if (rt == Utils.EP_ResultType_DataSet) { sb.Append(@" DataSet ds = SQLHelper.ExecuteDataSet(cmd);" ); foreach (StoredProcedureParameter p in sp.Parameters) { if (!p.IsOutputParameter) { continue; } string pn = Utils.GetEscapeName(p); string typename = Utils.GetNullableDataType(p); if (Utils.CheckIsStringType(p)) { sb.Append(@" p." + pn + @" = cmd.Parameters[""" + pn + @"""].Value as string;"); } else { sb.Append(@" if (cmd.Parameters[""" + pn + @"""].Value == null || cmd.Parameters[""" + pn + @"""].Value == DBNull.Value) p." + pn + @" = null; else p." + pn + @" = new " + typename + @"((" + Utils.GetDataType(p) + @")cmd.Parameters[""" + pn + @"""].Value);"); } } sb.Append(@" string s = cmd.Parameters[""RETURN_VALUE""].Value.ToString(); if (string.IsNullOrEmpty(s)) { p.SetReturnValue(0); } else { p.SetReturnValue(int.Parse(s)); } return ds; }" ); } else if (rt == Utils.EP_ResultType_DataTable) { sb.Append(@" DataTable dt = SQLHelper.ExecuteDataTable(cmd);" ); foreach (StoredProcedureParameter p in sp.Parameters) { if (!p.IsOutputParameter) { continue; } string pn = Utils.GetEscapeName(p); string typename = Utils.GetNullableDataType(p); if (Utils.CheckIsStringType(p)) { sb.Append(@" p." + pn + @" = cmd.Parameters[""" + pn + @"""].Value as string;"); } else { sb.Append(@" if (cmd.Parameters[""" + pn + @"""].Value == null || cmd.Parameters[""" + pn + @"""].Value == DBNull.Value) p." + pn + @" = null; else p." + pn + @" = new " + typename + @"((" + Utils.GetDataType(p) + @")cmd.Parameters[""" + pn + @"""].Value);"); } } sb.Append(@" string s = cmd.Parameters[""RETURN_VALUE""].Value.ToString(); if (string.IsNullOrEmpty(s)) { p.SetReturnValue(0); } else { p.SetReturnValue(int.Parse(s)); } return dt; }" ); } else if (rt == Utils.EP_ResultType_Object) { sb.Append(@" Object result = SQLHelper.ExecuteScalar(cmd);" ); foreach (StoredProcedureParameter p in sp.Parameters) { if (!p.IsOutputParameter) { continue; } string pn = Utils.GetEscapeName(p); string typename = Utils.GetNullableDataType(p); if (Utils.CheckIsStringType(p)) { sb.Append(@" p." + pn + @" = cmd.Parameters[""" + pn + @"""].Value as string;"); } else { sb.Append(@" if (cmd.Parameters[""" + pn + @"""].Value == null || cmd.Parameters[""" + pn + @"""].Value == DBNull.Value) p." + pn + @" = null; else p." + pn + @" = new " + typename + @"((" + Utils.GetDataType(p) + @")cmd.Parameters[""" + pn + @"""].Value);"); } } sb.Append(@" string s = cmd.Parameters[""RETURN_VALUE""].Value.ToString(); if (string.IsNullOrEmpty(s)) { p.SetReturnValue(0); } else { p.SetReturnValue(int.Parse(s)); } return result; }" ); } else { sb.Append(@" " + rt + @" os = new " + rt + @"(); using(SqlDataReader sdr = SQLHelper.ExecuteDataReader(cmd)) { while(sdr.Read()) { os.Add(new " + srt + @"("); if (uts.Find(delegate(Table target) { return(target.Name == sqlrt && target.Schema == rts); }) != null) { Table tb = uts.Find(delegate(Table target) { return(target.Name == sqlrt && target.Schema == rts); }); for (int j = 0; j < tb.Columns.Count; j++) { Column c = tb.Columns[j]; if (j > 0) { sb.Append(@", "); } if (c.Nullable) { s1 = Utils.CheckIsBinaryType(c) ? ("sdr.GetSqlBinary(" + j + @").Value") : (Utils.CheckIsValueType(c) ? ("new " + Utils.GetNullableDataType(c) + @"(sdr." + Utils.GetDataReaderMethod(c) + @"(" + j + @"))") : ("sdr." + Utils.GetDataReaderMethod(c) + @"(" + j + @")")); sb.Append(@"sdr.IsDBNull(" + j + @") ? null : " + s1); } else { if (Utils.CheckIsBinaryType(c)) { sb.Append(@"sdr.GetSqlBinary(" + j + @").Value"); } else { sb.Append(@"sdr." + Utils.GetDataReaderMethod(c) + @"(" + j + @")"); } } } } else if (uvs.Find(delegate(View target) { return(target.Name == sqlrt && target.Schema == rts); }) != null) { View tb = uvs.Find(delegate(View target) { return(target.Name == sqlrt && target.Schema == rts); }); for (int j = 0; j < tb.Columns.Count; j++) { Column c = tb.Columns[j]; if (j > 0) { sb.Append(@", "); } if (c.Nullable) { s1 = Utils.CheckIsBinaryType(c) ? ("sdr.GetSqlBinary(" + j + @").Value") : (Utils.CheckIsValueType(c) ? ("new " + Utils.GetNullableDataType(c) + @"(sdr." + Utils.GetDataReaderMethod(c) + @"(" + j + @"))") : ("sdr." + Utils.GetDataReaderMethod(c) + @"(" + j + @")")); sb.Append(@"sdr.IsDBNull(" + j + @") ? null : " + s1); } else { if (Utils.CheckIsBinaryType(c)) { sb.Append(@"sdr.GetSqlBinary(" + j + @").Value"); } else { sb.Append(@"sdr." + Utils.GetDataReaderMethod(c) + @"(" + j + @")"); } } } } else { UserDefinedTableType tb = udtts.Find(delegate(UserDefinedTableType target) { return(target.Name == sqlrt && target.Schema == rts); }); for (int j = 0; j < tb.Columns.Count; j++) { Column c = tb.Columns[j]; if (j > 0) { sb.Append(@", "); } if (c.Nullable) { s1 = Utils.CheckIsBinaryType(c) ? ("sdr.GetSqlBinary(" + j + @").Value") : (Utils.CheckIsValueType(c) ? ("new " + Utils.GetNullableDataType(c) + @"(sdr." + Utils.GetDataReaderMethod(c) + @"(" + j + @"))") : ("sdr." + Utils.GetDataReaderMethod(c) + @"(" + j + @")")); sb.Append(@"sdr.IsDBNull(" + j + @") ? null : " + s1); } else { if (Utils.CheckIsBinaryType(c)) { sb.Append(@"sdr.GetSqlBinary(" + j + @").Value"); } else { sb.Append(@"sdr." + Utils.GetDataReaderMethod(c) + @"(" + j + @")"); } } } } sb.Append(@")); } } "); foreach (StoredProcedureParameter p in sp.Parameters) { if (!p.IsOutputParameter) { continue; } string pn = Utils.GetEscapeName(p); string typename = Utils.GetNullableDataType(p); if (Utils.CheckIsStringType(p)) { sb.Append(@" p." + pn + @" = cmd.Parameters[""" + pn + @"""].Value as string;"); } else { sb.Append(@" if (cmd.Parameters[""" + pn + @"""].Value == null || cmd.Parameters[""" + pn + @"""].Value == DBNull.Value) p." + pn + @" = null; else p." + pn + @" = new " + typename + @"((" + Utils.GetDataType(p) + @")cmd.Parameters[""" + pn + @"""].Value);"); } } sb.Append(@" string s = cmd.Parameters[""RETURN_VALUE""].Value.ToString(); if (string.IsNullOrEmpty(s)) { p.SetReturnValue(0); } else { p.SetReturnValue(int.Parse(s)); } "); if (isSingleLine) { sb.Append(@" if (os.Count > 0) return os[0]; return null;" ); } else { sb.Append(@" return os;" ); } sb.Append(@" }" ); } } sb.Append(@" #endregion "); #endregion #region Footer sb.Append(@" } } "); return(sb.ToString()); #endregion }
public UserDefinedTableTypeRecordClassFactory(UserDefinedTableType userDefinedTableType) { this.UserDefinedTableType = userDefinedTableType; }
internal static string FName(this UserDefinedTableType self) => $"{self.Parent.Name.BracketObjectName()}.{self.Schema.BracketObjectName()}.{self.Name.BracketObjectName()}";
public static bool SwitchToMo(this UserDefinedTableType self, Database inMemDatabase, Database traditional, ref string error, ILog logger) { var hasPrimaryKey = false; if (inMemDatabase.UserDefinedTableTypes.Contains(self.Name, self.Schema)) { logger.Log("\t" + "Already exists", self.FName()); return(true); } var hasIdentities = false; var newTable = new UserDefinedTableType(inMemDatabase, self.Name, self.Schema); foreach (Column c in self.Columns) { var newColumn = new Column(newTable, c.Name); //newColumn.Default = c.Default; newColumn.CopyPropertiesFrom(c); TableExtension.SupportUnsupported(newColumn, c, traditional, logger, ref error, ref hasIdentities, false); newTable.Columns.Add(newColumn); } foreach (Index i in self.Indexes) { if (i.IndexKeyType != IndexKeyType.DriPrimaryKey) { continue; } var idx = new Index(newTable, i.Name) { IndexType = IndexType.NonClusteredIndex, IndexKeyType = IndexKeyType.DriPrimaryKey }; foreach (IndexedColumn ic in i.IndexedColumns) { idx.IndexedColumns.Add(new IndexedColumn(idx, ic.Name)); } newTable.Indexes.Add(idx); hasPrimaryKey = true; break; } newTable.IsMemoryOptimized = true; if (hasPrimaryKey == false) { var pkColumn = new Column(newTable, "pk" + newTable.Name) { DataType = { SqlDataType = SqlDataType.Int }, Identity = true, IdentitySeed = 1, IdentityIncrement = 1, Nullable = false }; newTable.Columns.Add(column: pkColumn); var idx = new Index(newTable, pkColumn.Name) { IndexType = IndexType.NonClusteredIndex, IndexKeyType = IndexKeyType.DriPrimaryKey }; idx.IndexedColumns.Add(new IndexedColumn(idx, pkColumn.Name)); newTable.Indexes.Add(idx); hasPrimaryKey = true; } try { logger.Log("Create UDT ", newTable.FName()); newTable.Create(); return(true); } catch (Exception ex) { //if (Debugger.IsAttached) // Debugger.Break(); error = string.Join(Environment.NewLine + "\t", ex.CollectThemAll(ex1 => ex1.InnerException) .Select(ex1 => ex1.Message)); return(false); } }