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 }