private void AddAssemblyButton_Click(System.Object sender,
System.EventArgs e)
{
Cursor csr = null;
Database db;
SqlAssembly asm;
UserDefinedFunction udf;
UserDefinedFunctionParameter parm;
ListViewItem AssemblyListViewItem;
try
{
csr = this.Cursor; // Save the old cursor
this.Cursor = Cursors.WaitCursor; // Display the waiting cursor
// Get selected database
db = (Database)DatabasesComboBox.SelectedItem;
asm = new SqlAssembly(db, "UtilityConversion");
asm.Owner = "dbo";
asm.AssemblySecurityLevel = AssemblySecurityLevel.Safe;
// This allows the assembly to be on a different server from SQL Server
// Use string array version which serializes the assembly
asm.Create(new String[] { AssemblyFileTextBox.Text });
udf = new UserDefinedFunction(db, "StringToInt32");
udf.TextMode = false;
udf.ImplementationType = ImplementationType.SqlClr;
udf.AssemblyName = "UtilityConversion";
udf.ClassName = "Microsoft.Samples.SqlServer.Conversions";
udf.MethodName = "StringToInt32";
udf.FunctionType = UserDefinedFunctionType.Scalar;
udf.DataType = DataType.Int;
parm = new UserDefinedFunctionParameter(udf, "@Input");
udf.Parameters.Add(parm);
parm.DataType = DataType.NVarChar(255);
udf.Create();
ShowAssemblies(true);
// Select the assembly just added
AssemblyListViewItem = AssembliesListView.FindItemWithText(
asm.Name);
AssemblyListViewItem.Selected = true;
AssemblyListViewItem.EnsureVisible();
}
catch (SmoException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
finally
{
this.Cursor = csr; // Restore the original cursor
}
}
private static string MakeParameterForFunction(UserDefinedFunctionParameter parType)
{
if (IsDateTime(parType.DataType))
{
return("'" + DateTime.Now.ToString("dd MMM yyyy HH:mm:SS") + "'");
}
if (IsDate(parType.DataType))
{
return("'" + DateTime.Now.ToString("dd MMM yyyy") + "'");
}
if (IsNumeric(parType.DataType))
{
return("0");
}
return("''");
}
public void CreateUdfFunction(string name, UserDefinedFunctionType functionType, DataType dataType, string body, params DbParameter[] parameters)
{
var function = new UserDefinedFunction(SmoDatabase, name);
function.TextMode = false;
function.IsSchemaBound = true;
function.FunctionType = functionType;
foreach (var parameter in parameters)
{
var fParameter = new UserDefinedFunctionParameter(function, parameter.Name, parameter.DataType);
function.Parameters.Add(fParameter);
}
function.DataType = dataType;
function.TextBody = body;
function.Create();
}
public static string Gen(Database db, string ns, string dsn, string dsn2)
{
#region Header
Server server = db.Parent;
string s = "";
List <StoredProcedure> sps = Utils.GetUserStoredProcedures(db);
List <Table> uts = Utils.GetUserTables(db);
List <View> uvs = Utils.GetUserViews(db);
List <UserDefinedFunction> ufs = Utils.GetUserFunctions(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 DB
{" );
#endregion
#region Functions
sb.Append(@"
#region User Defined Functions
");
foreach (UserDefinedFunction f in ufs)
{
string fn = Utils.GetEscapeName(f);
// 方法名
string mn = Utils.GetMethodName(f);
if (string.IsNullOrEmpty(mn))
{
mn = fn;
}
// 架构名
string sn = Utils.GetEscapeName(f.Schema);
// 最终方法名
mn = (Utils._CurrrentDALGenSetting_CurrentScheme.IsSupportSchema ? (sn + "_") : ("")) + mn;
sb.Append(Utils.GetSummary(f, 2));
for (int j = 0; j < f.Parameters.Count; j++)
{
UserDefinedFunctionParameter p = f.Parameters[j];
string pn = Utils.GetEscapeName(p);
string psum = Utils.GetDescription(p);
if (!string.IsNullOrEmpty(psum))
{
sb.Append(@"
/// <param name=""" + pn + @""">" + psum + @"</param>");
}
}
if (f.FunctionType == UserDefinedFunctionType.Table || f.FunctionType == UserDefinedFunctionType.Inline)
{
sb.Append(@"
[System.ComponentModel.DataObjectMethodAttribute(System.ComponentModel.DataObjectMethodType.Select)]
public static " + dsn + @"." + fn + @"DataTable " + mn + @"(");
for (int j = 0; j < f.Parameters.Count; j++)
{
UserDefinedFunctionParameter p = f.Parameters[j];
string pn = Utils.GetEscapeName(p);
if (j > 0)
{
sb.Append(@", ");
}
sb.Append(Utils.GetNullableDataType(p) + " " + pn);
}
sb.Append(@")
{
SqlCommand cmd = DC.NewCmd_" + fn + @"();");
for (int i = 0; i < f.Parameters.Count; i++)
{
UserDefinedFunctionParameter p = f.Parameters[i];
string pn = Utils.GetEscapeName(p);
sb.Append(@"
if (" + pn + @" == null) cmd.Parameters[""" + pn + @"""].Value = DBNull.Value;
else cmd.Parameters[""" + pn + @"""].Value = " + pn + ";");
}
sb.Append(@"
" + dsn + @"." + fn + @"DataTable dt = new " + dsn + @"." + fn + @"DataTable();
SQLHelper.ExecuteDataTable(dt, cmd);
return dt;
}" );
sb.Append(@"
public static int " + mn + @"(" + dsn + @"." + fn + @"DataTable dt");
for (int j = 0; j < f.Parameters.Count; j++)
{
UserDefinedFunctionParameter p = f.Parameters[j];
string pn = Utils.GetEscapeName(p);
sb.Append(@", ");
sb.Append(Utils.GetNullableDataType(p) + " " + pn);
}
sb.Append(@")
{
SqlCommand cmd = DC.NewCmd_" + fn + @"();");
for (int i = 0; i < f.Parameters.Count; i++)
{
UserDefinedFunctionParameter p = f.Parameters[i];
string pn = Utils.GetEscapeName(p);
sb.Append(@"
if (" + pn + @" == null) cmd.Parameters[""" + pn + @"""].Value = DBNull.Value;
else cmd.Parameters[""" + pn + @"""].Value = " + pn + ";");
}
sb.Append(@"
return SQLHelper.ExecuteDataTable(dt, cmd);
}" );
}
if (f.FunctionType != UserDefinedFunctionType.Table && f.FunctionType != UserDefinedFunctionType.Inline)
{
sb.Append(@"
public static " + Utils.GetNullableDataType(f) + @" " + mn + @"(");
for (int i = 0; i < f.Parameters.Count; i++)
{
UserDefinedFunctionParameter p = f.Parameters[i];
string pn = Utils.GetEscapeName(p);
if (i > 0)
{
sb.Append(", ");
}
sb.Append(Utils.GetNullableDataType(p) + " " + pn);
}
sb.Append(@")
{
SqlCommand cmd = DC.NewCmd_" + fn + @"();");
for (int i = 0; i < f.Parameters.Count; i++)
{
UserDefinedFunctionParameter p = f.Parameters[i];
string pn = Utils.GetEscapeName(p);
sb.Append(@"
cmd.Parameters[""" + pn + @"""].Value = " + pn + ";");
}
string ntn = Utils.GetNullableDataType(f);
if (Utils.CheckIsStringType(f))
{
sb.Append(@"
object o = SQLHelper.ExecuteScalar(cmd);
if(o == DBNull.Value) return null;
return (string)o;" );
}
else if (f.DataType.SqlDataType == SqlDataType.Variant)
{
sb.Append(@"
return SQLHelper.ExecuteScalar(cmd);" );
}
else
{
sb.Append(@"
object o = SQLHelper.ExecuteScalar(cmd);
if(o == DBNull.Value) return null;
return new " + ntn + "((" + Utils.GetDataType(f) + @")o);");
}
sb.Append(@"
}
");
}
}
sb.Append(@"
#endregion
");
#endregion
#region Footer
sb.Append(@"
}
}
");
return(sb.ToString());
#endregion
}
public static string Gen(Database db, string ns)
{
#region Header
Server server = db.Parent;
List <Table> uts = Utils.GetUserTables(db);
List <View> uvs = Utils.GetUserViews(db);
List <StoredProcedure> sps = Utils.GetUserStoredProcedures(db);
List <UserDefinedFunction> ufs = Utils.GetUserFunctions(db);
string s = "";
StringBuilder sb = new StringBuilder();
sb.Append(@"using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
namespace " + ns + @"
{
public static partial class DC
{" );
#endregion
#region Functions
sb.Append(@"
#region User Defined Functions
");
foreach (UserDefinedFunction f in ufs)
{
string tbn = Utils.GetEscapeName(f);
sb.Append(@"
private static SqlCommand _" + tbn + @"_cmd = null;
private static object _" + tbn + @"_cmd_sync = new object();
public static SqlCommand NewCmd_" + tbn + @"()
{
if (_" + tbn + @"_cmd != null) return _" + tbn + @"_cmd.Clone();
lock(_" + tbn + @"_cmd_sync)
{");
if (f.FunctionType == UserDefinedFunctionType.Table || f.FunctionType == UserDefinedFunctionType.Inline)
{
sb.Append(@"
_" + tbn + @"_cmd = new SqlCommand(""SELECT * FROM [" + Utils.GetEscapeSqlObjectName(f.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(f.Name) + @"](");
for (int i = 0; i < f.Parameters.Count; i++)
{
UserDefinedFunctionParameter p = f.Parameters[i];
string pn = Utils.GetEscapeName(p);
if (i > 0)
{
sb.Append(", ");
}
sb.Append("@" + pn);
}
sb.Append(@")"");");
for (int i = 0; i < f.Parameters.Count; i++)
{
UserDefinedFunctionParameter p = f.Parameters[i];
string pn = Utils.GetEscapeName(p);
sb.Append(@"
_" + tbn + @"_cmd.Parameters.Add(new SqlParameter(""" + pn + @""", " + Utils.GetSqlDbType(p) + @", " + p.DataType.MaximumLength.ToString() + @", ParameterDirection.Input, false, " + p.DataType.NumericPrecision.ToString() + @", " + p.DataType.NumericScale.ToString() + @", """ + pn + @""", DataRowVersion.Current, null));");
}
sb.Append(@"
return _" + tbn + @"_cmd.Clone();");
}
if (f.FunctionType != UserDefinedFunctionType.Table && f.FunctionType != UserDefinedFunctionType.Inline)
{
sb.Append(@"
_" + tbn + @"_cmd = new SqlCommand(""SELECT [" + Utils.GetEscapeSqlObjectName(f.Schema) + @"].[" + Utils.GetEscapeSqlObjectName(f.Name) + @"](");
for (int i = 0; i < f.Parameters.Count; i++)
{
UserDefinedFunctionParameter p = f.Parameters[i];
string pn = Utils.GetEscapeName(p);
if (i > 0)
{
sb.Append(", ");
}
sb.Append("@" + pn);
}
sb.Append(@")"");");
for (int i = 0; i < f.Parameters.Count; i++)
{
UserDefinedFunctionParameter p = f.Parameters[i];
string pn = Utils.GetEscapeName(p);
sb.Append(@"
_" + tbn + @"_cmd.Parameters.Add(new SqlParameter(""" + pn + @""", " + Utils.GetSqlDbType(p) + @", " + p.DataType.MaximumLength.ToString() + @", ParameterDirection.Input, false, " + p.DataType.NumericPrecision.ToString() + @", " + p.DataType.NumericScale.ToString() + @", """ + pn + @""", DataRowVersion.Current, null));");
}
sb.Append(@"
return _" + tbn + @"_cmd.Clone();");
}
sb.Append(@"
}
}" );
}
sb.Append(@"
#endregion
");
#endregion
#region Footer
sb.Append(@"
}
}
");
return(sb.ToString());
#endregion
}
private void AddAssemblyButton_Click(System.Object sender,
System.EventArgs e)
{
Cursor csr = null;
Database db;
SqlAssembly asm;
UserDefinedFunction udf;
UserDefinedFunctionParameter parm;
ListViewItem AssemblyListViewItem;
try
{
csr = this.Cursor; // Save the old cursor
this.Cursor = Cursors.WaitCursor; // Display the waiting cursor
// Get selected database
db = (Database)DatabasesComboBox.SelectedItem;
asm = new SqlAssembly(db, "UtilityConversion");
asm.Owner = "dbo";
asm.AssemblySecurityLevel = AssemblySecurityLevel.Safe;
// This allows the assembly to be on a different server from SQL Server
// Use string array version which serializes the assembly
asm.Create(new String[] { AssemblyFileTextBox.Text });
udf = new UserDefinedFunction(db, "StringToInt32");
udf.TextMode = false;
udf.ImplementationType = ImplementationType.SqlClr;
udf.AssemblyName = "UtilityConversion";
udf.ClassName = "Microsoft.Samples.SqlServer.Conversions";
udf.MethodName = "StringToInt32";
udf.FunctionType = UserDefinedFunctionType.Scalar;
udf.DataType = DataType.Int;
parm = new UserDefinedFunctionParameter(udf, "@Input");
udf.Parameters.Add(parm);
parm.DataType = DataType.NVarChar(255);
udf.Create();
ShowAssemblies(true);
// Select the assembly just added
AssemblyListViewItem = AssembliesListView.FindItemWithText(
asm.Name);
AssemblyListViewItem.Selected = true;
AssemblyListViewItem.EnsureVisible();
}
catch (SmoException ex)
{
ExceptionMessageBox emb = new ExceptionMessageBox(ex);
emb.Show(this);
}
finally
{
this.Cursor = csr; // Restore the original cursor
}
}
