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