C# StoredProcedure.Create方法代码示例

        public static void Go()
        {
            var server = new Server(".");
            Database database;

            if (server.Databases.Contains("Test_Randal_Sql") == false)
            {
                database = new Database(server, "Test_Randal_Sql");
                database.Create();
            }
            else
                database = server.Databases["Test_Randal_Sql"];

            if(database.StoredProcedures.Contains("mySp") == false)
            {
                var sp = new StoredProcedure(database, "mySp");
                sp.TextMode = false;
                sp.AnsiNullsStatus = false;
                sp.QuotedIdentifierStatus = false;
                sp.TextBody = "return -1";
                sp.Create();
            }

            if (database.UserDefinedFunctions.Contains("myFunc") == false)
            {
                var func = new UserDefinedFunction(database, "myFunc");
                func.TextMode = false;
                func.ExecutionContext = ExecutionContext.Caller;
                func.FunctionType = UserDefinedFunctionType.Scalar;
                func.ImplementationType = ImplementationType.TransactSql;
                func.DataType = DataType.Int;
                func.TextBody = "begin return(-1); end";
                func.Create();
            }

            if(database.Views.Contains("myView") == false)
            {
                var view = new View(database, "myView");

                view.TextMode = false;
                view.AnsiNullsStatus = false;
                view.TextBody = "select 42 [AnswerToEverything]";
                view.Create();
            }
        }

        //Sample only creates a procedure name with an empty procedure body. You can use TSQL to create the
        //procedure script.
        private void CreateProcedure(string procedureName)
        {
            if (!this.ServiceBroker.Parent.StoredProcedures.Contains(procedureName))
            {
                StoredProcedure sproc = new StoredProcedure
                    (this.ServiceBroker.Parent, procedureName);
                sproc.TextMode = false;
                sproc.TextBody = String.Empty;
                sproc.Create();
            }

        }

        private void CreateSelectProcedure(Schema spSchema, Table tbl)
        {
            String procName;
            StringBuilder sbSQL = new StringBuilder();
            StringBuilder sbSelect = new StringBuilder();
            StringBuilder sbWhere = new StringBuilder();
            StoredProcedure sp;
            StoredProcedureParameter parm;

            try
            {
                // Create stored procedure name from user entry and table name
                procName = PrefixTextBox.Text + tbl.Name + @"Select";

                if (DropOnlyCheckBox.CheckState == CheckState.Checked)
                {
                    DropStoredProcedure(procName, spSchema);
                }
                else
                {
                    DropStoredProcedure(procName, spSchema);
                    ScriptTextBox.AppendText(string.Format(
                        System.Globalization.CultureInfo.InvariantCulture,
                        Properties.Resources.CreatingStoredProcedure,
                        spSchema.ToString(), BracketObjectName(procName))
                        + Environment.NewLine);
                    ScrollToBottom();

                    // Create the new stored procedure object
                    sp = new StoredProcedure(tbl.Parent, procName, spSchema.Name);
                    sp.TextMode = false;
                    foreach (Column col in tbl.Columns)
                    {
                        // Select columns
                        if (sbSelect.Length > 0)
                        {
                            sbSelect.Append(", " + Environment.NewLine);
                        }

                        // Note: this does not fix object names with embedded brackets
                        sbSelect.Append("\t[");
                        sbSelect.Append(col.Name);
                        sbSelect.Append(@"]");

                        // Create parameters and where clause from indexed fields
                        if (col.InPrimaryKey == true)
                        {
                            // Parameter columns
                            parm = new StoredProcedureParameter(sp, "@"
                                + col.Name);
                            parm.DataType = col.DataType;
                            parm.DataType.MaximumLength
                                = col.DataType.MaximumLength;
                            sp.Parameters.Add(parm);

                            // Where columns
                            if (sbWhere.Length > 0)
                            {
                                sbWhere.Append(" " + Environment.NewLine + "\tAND ");
                            }

                            // Note: this does not fix object names with embedded brackets
                            sbWhere.Append(@"[");
                            sbWhere.Append(col.Name);
                            sbWhere.Append(@"] = @");
                            sbWhere.Append(col.Name);
                        }
                    }

                    // Put where clause into string
                    if (sbWhere.Length > 0)
                    {
                        sbWhere.Insert(0, @"WHERE ");
                    }

                    sbrStatus.Text = string.Format(System.Globalization.CultureInfo.InvariantCulture,
                        Properties.Resources.Creating, procName);
                    sbSQL.Append("SELECT ");
                    sbSQL.Append(sbSelect);
                    sbSQL.Append(" " + Environment.NewLine + "FROM ");
                    sbSQL.Append(tbl.ToString());
                    sbSQL.Append(" " + Environment.NewLine);
                    sbSQL.Append(sbWhere);
                    sp.TextBody = sbSQL.ToString();
                    sp.Create();
                }
            }
            catch (SmoException ex)
            {
                ExceptionMessageBox emb = new ExceptionMessageBox(ex);
                emb.Show(this);
            }
            finally
            {
                // Clean up.
                sbSQL = null;
                sbSelect = null;
                sbWhere = null;
                sp = null;
                parm = null;
//.........这里部分代码省略.........

        internal void generate(IntegratedView employeesView)
        {
            connectionString =
               "Data Source=localhost\\SQLEXPRESS;"
             + "Initial Catalog=szpifDatabase;"
             + "Integrated Security=SSPI;";
            string procName = "get" + employeesView.ViewName;
            // Create an instance of the server
            connection = new SqlConnection(connectionString);
            Server server = new Server(new ServerConnection(connection));
            SqlCommand scommand = new SqlCommand();
            scommand.CommandText = "IF OBJECT_ID('" + procName + "') IS NOT NULL " +
            "DROP PROCEDURE " + procName;
            scommand.Connection = connection;
            connection.Open();
            scommand.ExecuteNonQuery();
            connection.Close();

            // I want to add the stored procedure to the "MyDatabase" Database
            Database db = server.Databases["SzpifDatabase"];

            // Create a Stored Procedure 
            StoredProcedure getView = //new StoredProcedure();
               new StoredProcedure(db, "get" + employeesView.ViewName);

            getView.TextMode = false;
            getView.AnsiNullsStatus = false;
            getView.QuotedIdentifierStatus = false;

            // GetClubByID requires the ID of the Club as an Input Parameter
            //StoredProcedureParameter idParam =
            //        new StoredProcedureParameter(getView, "@ID", DataType.Int);
            //getView.Parameters.Add(idParam);

            // The SQL Text
            string command = "SELECT DISTINCT ";
            string columns = "";
            for (int i = 0; i < employeesView.Columns.Count; ++i )
            {
                columns += employeesView.Columns[i].getFullName();
                if(i+1 < employeesView.Columns.Count)columns +=", ";
            }
            command += columns;
            command += " FROM ";
            command += " "+employeesView.TableNames[0]+" " 
                          +employeesView.TableNames[0]+"1"+ " ";
            string joins = "";
            string mainName = employeesView.TableNames[0];
            foreach (ViewJoin vj in employeesView.Joins)
            {
                joins += vj.getText();
            }
            command += joins;
            getView.TextBody = command;

            getView.Create();

            // teraz tworzę procedurę do update'owania
//CREATE PROCEDURE updateEmployeeViewForAdministration
//  @Id			int,
//  @Login		nvarchar(40),
//  @Name			nvarchar(40),
//  @EMail		nvarchar(40)
//AS
//    update Employees set Login = @Login  where Id = @Id    
//    update Credentials set Name = @Name, EMail = @EMail where Id = 
//    (select CredentialsId from Employees where Id = @Id)
//GO

        }

        public void GrantAccessToSchemaObjects()
        {
            var helper = new TestHelper();
            try
            {
                var schema = helper.GetSchema();
                //schema.Owner = helper.GetUser().Name;
                //schema.Alter();

                var table = new Table(helper.GetDatabase(), "Table1", schema.Name);
                table.Columns.Add(new Column(table, "Col1", DataType.Int));
                table.Columns.Add(new Column(table, "Col2", DataType.NVarCharMax));
                table.Create();
                helper.AddCleanup(table);

                var view = new View(helper.GetDatabase(), "View1", schema.Name)
                    {
                        TextMode = false,
                        TextBody = String.Format("SELECT Col1, Col2 FROM [{0}].[{1}]", table.Schema, table.Name)
                    };
                //view.TextHeader = String.Format("CREATE VIEW [{0}].[{1}] AS", view.Schema, view.Name);
                view.Create();
                helper.AddCleanup(view);

                var scalarTsqlFn = new UserDefinedFunction(helper.GetDatabase(), "ScalarTsqlFunction", schema.Name)
                    {
                        TextMode = false,
                        DataType = DataType.DateTime,
                        ExecutionContext = ExecutionContext.Caller,
                        FunctionType = UserDefinedFunctionType.Scalar,
                        ImplementationType = ImplementationType.TransactSql,
                        TextBody = "BEGIN RETURN GETDATE() END"
                    };
                scalarTsqlFn.Create();
                helper.AddCleanup(scalarTsqlFn);

                var inlineTsqlFn = new UserDefinedFunction(helper.GetDatabase(), "InlineTsqlFunction", schema.Name)
                    {
                        TextMode = false,
                        ExecutionContext = ExecutionContext.Caller,
                        FunctionType = UserDefinedFunctionType.Inline,
                        ImplementationType = ImplementationType.TransactSql,
                        TextBody = String.Format("RETURN SELECT * FROM [{0}].[{1}]", view.Schema, view.Name)
                    };
                inlineTsqlFn.Create();
                helper.AddCleanup(inlineTsqlFn);

                // TODO: Create table valued function

                // TODO: Create Clr scalar func

                // TODO: Create Clr inline func (Exists?)

                // TODO: Create Clr table valued func

                // TODO: Create Clr Aggregate

                var proc = new StoredProcedure(helper.GetDatabase(), "sproc1", schema.Name)
                    {
                        TextMode = false,
                        AnsiNullsStatus = false,
                        QuotedIdentifierStatus = false,
                        TextBody = String.Format("SELECT * FROM [{0}].[{1}]()", inlineTsqlFn.Schema, inlineTsqlFn.Name)
                    };
                proc.Create();
                helper.AddCleanup(proc);

                // TODO: Create Clr Sproc

                // TODO: Create Constraint
                // TODO: Create Queue
                // TODO: Create Statistic
                // TODO: Create Synonym

                var user = helper.GetUser();

                var permissable = new IObjectPermission[]
                    {
                        table,
                        view,
                        scalarTsqlFn,
                        inlineTsqlFn,
                        proc,
                    };

                permissable.Do(tg => tg.GrantAll(user.Name));
                permissable.Do(tg => tg.DenyAll(user.Name));
                permissable.Do(tg => tg.RevokeAll(user.Name));

                // change all owners
                table.Owner = user.Name;
                table.Alter();

                view.Owner = user.Name;
                view.Alter();

                scalarTsqlFn.Owner = user.Name;
                scalarTsqlFn.Alter();

                inlineTsqlFn.Owner = user.Name;
//.........这里部分代码省略.........