private static void WriteProcedure(DatabaseStoredProcedure procedure, ProcedureWriter procWriter)
        {
            foreach (var argument in procedure.Arguments)
            {
                if (argument.Out)
                {
                    //we don't deal with INOUT parameters.
                    procWriter.AddOutputParameter(argument.Name, argument.DatabaseDataType);
                    continue;
                }
                //an IN sproc
                procWriter.AddParameter(argument.Name, argument.DatabaseDataType);
            }
            procWriter.BeginProcedure();

            var sql = procedure.Sql.Trim()
                //standardize to windows line endings
                .Replace("\r\n","\n").Replace("\r","\n").Replace("\n","\r\n");
            //remove the BEGIN and END as the procWriter writes these
            if (sql.StartsWith("BEGIN", StringComparison.OrdinalIgnoreCase))
            {
                sql = sql.Substring(5);
            }
            if (sql.EndsWith("END", StringComparison.OrdinalIgnoreCase))
            {
                sql = sql.Substring(0, sql.Length - 3).Trim();
            }

            procWriter.AddSql(sql);
        }
Пример #2
0
 public SprocWriter(DatabaseStoredProcedure storedProcedure, string ns)
 {
     _namespace = ns;
     _storedProcedure = storedProcedure;
     _logic = new SprocLogic(_storedProcedure);
     _sprocResultType = _logic.ResultType;
     _cb = new ClassBuilder();
 }
Пример #3
0
 internal SprocResultWriter(DatabaseStoredProcedure storedProcedure, string ns, ClassBuilder classBuilder)
 {
     _namespace = ns;
     _storedProcedure = storedProcedure;
     _logic = new SprocLogic(_storedProcedure);
     _resultClassName = _logic.ResultClassName;
     _cb = classBuilder;
 }
Пример #4
0
        /// <summary>
        /// Calls the specified procedure to find the result type.
        /// </summary>
        /// <param name="procedure">The procedure.</param>
        public void ExecuteProcedure(DatabaseStoredProcedure procedure)
        {
            var executionName = procedure.Name;
            if (!string.IsNullOrEmpty(procedure.Package))
                executionName = procedure.Package + "." + procedure.Name;

            //for Oracle, sprocs with REF CURSORs indicate it returns something.
            if (_isOracle && !procedure.Arguments.Any(a => a.DatabaseDataType == "REF CURSOR"))
                return;

            using (var resultSet = new DataSet { Locale = CultureInfo.InvariantCulture })
            {
                using (DbConnection connection = _factory.CreateConnection())
                {
                    connection.ConnectionString = _schema.ConnectionString;
                    using (var command = _factory.CreateCommand())
                    {
                        command.Connection = connection;
                        command.CommandText = executionName;
                        command.CommandTimeout = 5;
                        command.CommandType = CommandType.StoredProcedure;

                        AddParameters(procedure, command);

                        connection.Open();
                        using (DbTransaction tx = connection.BeginTransaction())
                        {
                            command.Transaction = tx;

                            using (DbDataAdapter adapter = _factory.CreateDataAdapter())
                            {
                                adapter.SelectCommand = command;

                                try
                                {
                                    adapter.FillSchema(resultSet, SchemaType.Source);
                                }
                                catch (DbException exception)
                                {
                                    //ignore any db exceptions
                                    Debug.WriteLine(executionName + Environment.NewLine
                                                    + exception.Message);
                                }
                                catch (Exception exception) //for exceptions that don't derive from DbException
                                {
                                    //ignore any db exceptions
                                    Debug.WriteLine(executionName + Environment.NewLine
                                                    + exception.Message);
                                }

                            }
                            tx.Rollback();
                        }
                    }
                }
                UpdateProcedure(procedure, resultSet);
            }
        }
 private static void PrepareStoredProcedureNames(DatabaseStoredProcedure sproc, INamer namer)
 {
     if (string.IsNullOrEmpty(sproc.NetName))
         sproc.NetName = namer.Name(sproc);
     foreach (var argument in sproc.Arguments)
     {
         if (string.IsNullOrEmpty(argument.NetName))
             argument.NetName = namer.Name(argument);
     }
 }
Пример #6
0
 private void CreateResult(ResultType resultType, DatabaseStoredProcedure storedProcedure, string script)
 {
     var result = new CompareResult
         {
             SchemaObjectType = SchemaObjectType.StoredProcedure,
             ResultType = resultType,
             Name = storedProcedure.Name,
             SchemaOwner = storedProcedure.SchemaOwner,
             Script = script
         };
     _results.Add(result);
 }
        public override string AddProcedure(DatabaseStoredProcedure procedure)
        {
            if (string.IsNullOrEmpty(procedure.Sql))
            {
                //the procedure.Sql contains the BEGIN to END statements, not the CREATE PROCEDURE and arguments.
                //for now, just comment
                return "-- add procedure " + procedure.Name;
            }

            var name = procedure.Name;
            var procWriter = new ProcedureWriter(name, null);
            WriteProcedure(procedure, procWriter);

            return procWriter.End();
        }
Пример #8
0
        private void WriteSproc(DatabaseStoredProcedure sproc)
        {
            if (sproc == null) return;
            using (_cb.BeginNest("private static DbConnection CreateConnection()"))
            {
                _cb.AppendLine(@"const string connectionString = @""" + _schema.ConnectionString + "\";");
                _cb.AppendLine(@"const string provider = @""" + _schema.Provider + "\";");
                _cb.AppendLine("var factory = DbProviderFactories.GetFactory(provider);");
                _cb.AppendLine("var connection = factory.CreateConnection();");
                _cb.AppendLine("connection.ConnectionString = connectionString;");
                _cb.AppendLine("return connection;");
            }

            _cb.AppendLine("[TestMethod]");
            using (_cb.BeginNest("public void Test" + sproc.NetName + "()"))
            {
                using (_cb.BeginNest("using (var connection = CreateConnection())"))
                {
                    _cb.AppendLine("var cmd = new " + sproc.NetName + "(connection);");
                    var list = new List<string>();
                    foreach (var arg in sproc.Arguments)
                    {
                        if (arg.DataType == null)
                        {
                            list.Add("null");
                        }
                        else if (arg.DataType.IsString)
                        {
                            list.Add("a");
                        }
                        else if (arg.DataType.IsNumeric)
                        {
                            list.Add("1");
                        }
                        else if (arg.DataType.IsDateTime)
                        {
                            list.Add("DateTime.Now");
                        }
                    }
                    _cb.AppendLine("var result = cmd.Execute(" + string.Join(", ", list.ToArray()) + ");");
                    _cb.AppendLine("//test whether it worked!");
                }
            }
        }
Пример #9
0
 private static void UpdateArgumentDataTypes(IDictionary <string, DataType> dataTypes, DatabaseStoredProcedure sproc)
 {
     foreach (DatabaseArgument arg in sproc.Arguments)
     {
         arg.DataType = FindDataType(dataTypes, arg.DatabaseDataType);
     }
 }
Пример #10
0
 public virtual string DropProcedure(DatabaseStoredProcedure procedure)
 {
     return string.Format(CultureInfo.InvariantCulture,
         "DROP PROCEDURE {0}{1};",
         SchemaPrefix(procedure.SchemaOwner),
         Escape(procedure.Name))
         + _sqlFormatProvider.RunStatements();
 }
Пример #11
0
 public virtual string AddProcedure(DatabaseStoredProcedure procedure)
 {
     //CREATE PROCEDURE cannot be combined with other statements in a batch, so be preceeded by and terminate with a "GO" (sqlServer) or "/" (Oracle)
     var sql = procedure.Sql;
     if (string.IsNullOrEmpty(sql))
     {
         //without the sql, we can't do anything
         return "-- add procedure " + procedure.Name;
     }
     if (sql.TrimStart().StartsWith("PROCEDURE ", StringComparison.OrdinalIgnoreCase))
     {
         return "CREATE " + sql + _sqlFormatProvider.RunStatements();
     }
     //helpfully, SqlServer includes the create statement
     //MySQL doesn't, so this will need to be overridden
     return sql + _sqlFormatProvider.RunStatements();
 }
Пример #12
0
 private static void UpdateArgumentDataTypes(IDictionary<string, DataType> dataTypes, DatabaseStoredProcedure sproc)
 {
     foreach (DatabaseArgument arg in sproc.Arguments)
     {
         arg.DataType = FindDataType(dataTypes, arg.DatabaseDataType);
     }
 }
 public string AddProcedure(DatabaseStoredProcedure procedure)
 {
     return _migration.AddProcedure(procedure);
 }
Пример #14
0
 public void BuildProcedure(DatabaseStoredProcedure storedProcedure)
 {
     try
     {
         var txt = _migrationGenerator.AddProcedure(storedProcedure);
         Clipboard.SetText(txt, TextDataFormat.UnicodeText);
     }
     catch (Exception exception)
     {
         Debug.WriteLine(exception.Message);
     }
 }
Пример #15
0
 private static void AddParameters(DatabaseStoredProcedure procedure, DbCommand command)
 {
     foreach (var argument in procedure.Arguments)
     {
         if (argument.Ordinal == 0 && !argument.In && string.Equals(argument.Name, "RETURN_VALUE", StringComparison.OrdinalIgnoreCase))
             continue;
         var parameter = command.CreateParameter();
         AddParameter(parameter, argument);
         command.Parameters.Add(parameter);
     }
 }
 private static DatabaseStoredProcedure CreateProcedureOrFunction(DatabaseSchema databaseSchema, bool isFunction)
 {
     DatabaseStoredProcedure sproc;
     if (isFunction)
     {
         //functions are just a type of stored procedure
         DatabaseFunction fun = new DatabaseFunction();
         databaseSchema.Functions.Add(fun);
         sproc = fun;
     }
     else
     {
         sproc = new DatabaseStoredProcedure();
         databaseSchema.StoredProcedures.Add(sproc);
     }
     return sproc;
 }
Пример #17
0
        private static void WriteStoredProcedure(string procedures, string directoryPath, string @namespace, DatabaseStoredProcedure sproc, ProjectWriter pw)
        {
            //if no .net classname, don't process
            if (string.IsNullOrEmpty(sproc.NetName)) return;

            var sw = new SprocWriter(sproc, @namespace);
            var txt = sw.Write();
            var fileName = sproc.NetName + ".cs";
            var path = Path.Combine(directoryPath, fileName);
            File.WriteAllText(path, txt);
            pw.AddClass(procedures + @"\" + fileName);
            if (sw.RequiresOracleReference)
            {
                if (sw.RequiresDevartOracleReference)
                    pw.AddDevartOracleReference();
                else
                    pw.AddOracleReference();
            }

            if (sw.HasResultClass)
            {
                var rs = new SprocResultWriter(sproc, @namespace);
                txt = rs.Write();
                fileName = rs.ClassName + ".cs";
                path = Path.Combine(directoryPath, fileName);
                File.WriteAllText(path, txt);
                pw.AddClass(procedures + @"\" + fileName);
            }
        }
Пример #18
0
 /// <summary>
 /// Initializes a new instance of the <see cref="ProcedureWriter"/> class.
 /// </summary>
 /// <param name="storedProcedure">The stored procedure.</param>
 /// <param name="ns">The namespace.</param>
 public ProcedureWriter(DatabaseStoredProcedure storedProcedure, string ns)
 {
     _namespace = ns;
     _storedProcedure = storedProcedure;
     
 }
Пример #19
0
        public void ProcedureTest()
        {
            var schema = PrepareModel();

            var procedure = new DatabaseStoredProcedure { Name = "SelectCategory" };
            var argument = new DatabaseArgument
                           {
                               Name = "p1",
                               DatabaseDataType = "VARCHAR",
                               Length = 10,
                               DataType = DataTypeConverter.FindDataType("VARCHAR", schema.DataTypes, SqlType.SqlServer, null),
                               In = true,
                           };
            procedure.Arguments.Add(argument);
            var rs = new DatabaseResultSet();
            var resultColumn = new DatabaseColumn { Name = "Output", DbDataType = "VARCHAR" };
            DataTypeConverter.AddDataType(resultColumn);
            rs.Columns.Add(resultColumn);
            procedure.ResultSets.Add(rs);
            schema.StoredProcedures.Add(procedure);

            var directory = TestHelper.CreateDirectory("MySprocTest");
            const string @namespace = "MySprocTest";
            var settings = new CodeWriterSettings
            {
                Namespace = @namespace,
                CodeTarget = CodeTarget.Poco,
                WriteStoredProcedures = true
            };

            var target = new CodeWriter(schema, settings);
            target.Execute(directory);

            var procedures = directory.GetDirectories("Procedures").FirstOrDefault();
            if (procedures == null)
                Assert.Fail("Could not find Procedures subdirectory");
            var files = procedures.GetFiles("*.cs");
            var products = files.FirstOrDefault(f => f.Name == "SelectCategory.cs");
            Assert.IsNotNull(products, "Should have written SelectCategory class for SelectCategory procedure");

            var category = files.FirstOrDefault(f => f.Name == "SelectCategoryResult.cs");
            Assert.IsNotNull(category, "Should have written SelectCategoryResult class to the result of the sproc");
        }
Пример #20
0
 public SprocLogic(DatabaseStoredProcedure storedProcedure)
 {
     _storedProcedure = storedProcedure;
     _className = _storedProcedure.NetName ?? (_storedProcedure.NetName = NameFixer.ToPascalCase(_storedProcedure.Name));
 }
 public override string DropProcedure(DatabaseStoredProcedure procedure)
 {
     return null; //doesn't support it
 }
Пример #22
0
        public void BuildProcedureCode(DatabaseSchema databaseSchema, DatabaseStoredProcedure databaseStoredProcedure)
        {
            try
            {
                //grab the data
                if (databaseStoredProcedure.ResultSets.Count == 0)
                {
                    //Delete sprocs won't have resultsets, so will get called multiple times
                    var sprocRunner = new DatabaseSchemaReader.Procedures.ResultSetReader(databaseSchema);
                    sprocRunner.ExecuteProcedure(databaseStoredProcedure);
                }

                //write it
                var sprocWriter = new ProcedureWriter(databaseStoredProcedure, "Domain");
                var txt = sprocWriter.Write();

                Clipboard.SetText(txt, TextDataFormat.UnicodeText);
            }
            catch (Exception exception)
            {
                Clipboard.SetText("//sorry, not available - " + exception.Message,
                    TextDataFormat.UnicodeText);
                Debug.WriteLine(exception.Message);
            }
        }
 private static DatabaseStoredProcedure CreateProcedureOrFunction(DatabaseSchema databaseSchema, List<DatabaseArgument> args)
 {
     //if it's ordinal 0 and no name, it's a function not a sproc
     DatabaseStoredProcedure sproc;
     if (args.Find(delegate(DatabaseArgument arg) { return arg.Ordinal == 0 && string.IsNullOrEmpty(arg.Name); }) != null)
     {
         //functions are just a type of stored procedure
         DatabaseFunction fun = new DatabaseFunction();
         databaseSchema.Functions.Add(fun);
         sproc = fun;
     }
     else
     {
         sproc = new DatabaseStoredProcedure();
         databaseSchema.StoredProcedures.Add(sproc);
     }
     return sproc;
 }
Пример #24
0
 private static void UpdateProcedure(DatabaseStoredProcedure procedure, DataSet resultSet)
 {
     foreach (DataTable table in resultSet.Tables)
     {
         var result = new DatabaseResultSet();
         procedure.ResultSets.Add(result);
         foreach (DataColumn column in table.Columns)
         {
             var dbColumn = new DatabaseColumn();
             dbColumn.Name = column.ColumnName;
             dbColumn.DbDataType = column.DataType.Name;
             dbColumn.Length = column.MaxLength;
             dbColumn.Nullable = column.AllowDBNull;
             result.Columns.Add(dbColumn);
         }
     }
 }