static void Main(string[] args) { DatabaseBuilder builder = new DatabaseBuilder(); SqlParameter p = new SqlParameter(); // p. Column c = new Column(); // c.Properties. // c. // c. ViewPermissions permissions = new ViewPermissions(); permissions.Add("select", "Owner"); permissions.Add("insert", "Owner"); permissions.Add("update", "Owner"); permissions.Add("delete", "Owner"); List <ViewColumn> columns = new List <ViewColumn>(); columns.Add(new ViewColumn("Employees", "Id", true)); columns.Add(new ViewColumn("Employees", "Login", false)); columns.Add(new ViewColumn("Credentials", "Name", false)); columns.Add(new ViewColumn("Credentials", "EMail", false)); columns.Add(new ComputedViewColumn( new ViewColumn("Employees", "Id", false), "dbo.aggregateRolesFunction", "Uprawnienia")); List <string> tables = new List <string>(); tables.Add("Employees"); tables.Add("Credentials"); tables.Add("Roles"); List <ViewJoin> joins = new List <ViewJoin>(); joins.Add(new ViewJoin("Roles", new ViewColumn("Roles", "EmployeeId", true), new ViewColumn("Employees", "Id", true))); joins.Add(new ViewJoin("Credentials", new ViewColumn("Credentials", "Id", true), new ViewColumn("Employees", "CredentialsId", true))); IntegratedView employeesView = new IntegratedView( "testView", columns, permissions, tables, joins); builder.generate(employeesView); Console.WriteLine("Press Enter to Continue"); Console.ReadKey(); }
static void Main(string[] args) { DatabaseBuilder builder = new DatabaseBuilder(); SqlParameter p = new SqlParameter(); // p. Column c = new Column(); // c.Properties. // c. // c. ViewPermissions permissions = new ViewPermissions(); permissions.Add("select", "Owner"); permissions.Add("insert", "Owner"); permissions.Add("update", "Owner"); permissions.Add("delete", "Owner"); List<ViewColumn> columns = new List<ViewColumn>(); columns.Add(new ViewColumn("Employees", "Id", true)); columns.Add(new ViewColumn("Employees", "Login", false)); columns.Add(new ViewColumn("Credentials", "Name", false)); columns.Add(new ViewColumn("Credentials", "EMail", false)); columns.Add(new ComputedViewColumn( new ViewColumn("Employees", "Id",false), "dbo.aggregateRolesFunction", "Uprawnienia")); List<string> tables = new List<string>(); tables.Add("Employees"); tables.Add("Credentials"); tables.Add("Roles"); List<ViewJoin> joins = new List<ViewJoin>(); joins.Add(new ViewJoin("Roles", new ViewColumn("Roles", "EmployeeId",true), new ViewColumn("Employees", "Id",true))); joins.Add(new ViewJoin("Credentials", new ViewColumn("Credentials", "Id",true), new ViewColumn("Employees", "CredentialsId",true))); IntegratedView employeesView = new IntegratedView( "testView", columns, permissions, tables, joins); builder.generate(employeesView); Console.WriteLine("Press Enter to Continue"); Console.ReadKey(); }
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 }
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 }