Beispiel #1
0
 public string GetFlywayFileName(CStoredProcedure storedProcedure)
 {
     if (storedProcedure.DatabaseType == DataStoreTypes.SqlServer)
     {
         return
             ($"{FlywayVersionNumber}__Create_Procedure_{storedProcedure.Schema.SchemaName}_{storedProcedure.StoredProcedureName}.sql");
     }
     else
     {
         return
             ($"{FlywayVersionNumber}__Create_Function_{storedProcedure.Schema.SchemaName}_{storedProcedure.StoredProcedureName}.sql");
     }
 }
Beispiel #2
0
        public string Convert(CStoredProcedure storedProcedure)
        {
            var converter             = new CStoredProcedureToCreateProcedureStatementConverter();
            var createStoredProcedure = converter.Convert(storedProcedure);

            //todo: this should be done when creating the CStoredProcedure.StoredProcBody
            //var snakeCaseVisitor = new SqlServerSnakeCaseVisitor();
            //createStoredProcedure.Accept(snakeCaseVisitor);

            string scriptOut;
            var    scriptGen = new Sql120ScriptGenerator();

            scriptGen.GenerateScript(createStoredProcedure, out scriptOut);
            return(scriptOut);
        }
Beispiel #3
0
        public CStoredProcedure Convert(CTable table)
        {
            var storedProcedure = new CStoredProcedure(DataStoreTypes.SqlServer)
            {
                DataOperationIs = COperationIs.Delete | COperationIs.CRUD
            };

            storedProcedure.Schema = new CSchema {
                SchemaName = $"{table.Schema.SchemaName}Api"
            };
            storedProcedure.StoredProcedureName = $"{table.TableName}Delete";
            //storedProcedure.ResultSetName = table.TableName;
            storedProcedure.Parameter = new List <CStoredProcedureParameter>();

            var pkColumns = table.GetPrimaryKeyColumns();

            foreach (var pkColumn in pkColumns)
            {
                var parameter = new CStoredProcedureParameter
                {
                    ParameterName = $"{pkColumn.ColumnName}",
                    ParameterType = pkColumn.ColumnType,

                    ParameterTypeRaw = pkColumn.ColumnTypeRaw,
                    ParameterLength  = pkColumn.ColumnLength,
                    SourceColumn     = pkColumn
                };
                storedProcedure.Parameter.Add(parameter);
            }
            var stringBuilder = new StringBuilder();

            stringBuilder.AppendLine($"DELETE FROM [{table.Schema.SchemaName}].[{table.TableName}] ");
            stringBuilder.AppendLine("WHERE");
            var first = true;

            foreach (var pkColumn in pkColumns)
            {
                if (!first)
                {
                    stringBuilder.Append(" AND ");
                }
                stringBuilder.AppendLine($"\t\t[{pkColumn.ColumnName}] = @{pkColumn.ColumnName}");
                first = false;
            }
            storedProcedure.StoredProcedureBody = stringBuilder.ToString();
            return(storedProcedure);
        }
        public CClass BuildResultEntityClass(CStoredProcedure storedProcedure, string resultSetName, KDataLayerProject dataLayerKProject)
        {
            if (string.IsNullOrEmpty(resultSetName))
            {
                return(null);
            }

            var converter = new CStoredProcedureToCClassConverter();
            var @class    = converter.ConvertByResultSet(storedProcedure);

            //overrite the default namespace logic
            @class.Namespace = new CNamespace
            {
                NamespaceName =
                    $"{dataLayerKProject.CompanyName}.{dataLayerKProject.ProjectName}{dataLayerKProject.NamespaceSuffix}.Data.Entities"
            };
            return(@class);
        }
Beispiel #5
0
        public CreateProcedureStatement Convert(CStoredProcedure storedProcedure)
        {
            //build body

            string[] parts = { storedProcedure.Schema.SchemaName, storedProcedure.StoredProcedureName };

            var createStoredProcedure = new CreateProcedureStatement();

            ///set schema and table name
            ///
            var schemaObjectName = new SchemaObjectName();

            schemaObjectName.Identifiers.Add(new Identifier {
                Value = storedProcedure.Schema.SchemaName
            });
            schemaObjectName.Identifiers.Add(new Identifier {
                Value = storedProcedure.StoredProcedureName
            });

            createStoredProcedure.ProcedureReference      = new ProcedureReference();
            createStoredProcedure.ProcedureReference.Name = schemaObjectName;

            //add parameters


            foreach (var param in storedProcedure.Parameter)
            {
                if (param.ParameterTypeIsUserDefined)
                {
                    var dataType = new UserDataTypeReference();
                    dataType.Name = new SchemaObjectName();
                    dataType.Name.Identifiers.Add(new Identifier {
                        Value = param.ParameterTypeRaw
                    });
                    if (param.ParameterLength > 0)
                    {
                        dataType.Parameters.Add(new IntegerLiteral {
                            Value = param.ParameterLength.ToString()
                        });
                    }

                    createStoredProcedure.Parameters.Add(new ProcedureParameter
                    {
                        VariableName = new Identifier {
                            Value = $"@{param.ParameterName}"
                        },
                        DataType = dataType,
                        Value    = param.DefaultToNull ? new NullLiteral() : null,
                        Modifier = param.ParameterTypeRaw.ToLower() == "sysname" ? ParameterModifier.None: ParameterModifier.ReadOnly //todo
                    });
                }
                else
                {
                    var dataType      = new SqlDataTypeReference();
                    var parameterName = param.ParameterName;
                    if (param.IsCollection)
                    {
                        parameterName += "_Collection";// temp solution for comma separate collection parameters
                    }
                    if (param.ParameterTypeRaw == "enum")
                    {
                        dataType.SqlDataTypeOption = SqlDataTypeOption.Int; //todo: review this
                    }
                    else
                    {
                        dataType.SqlDataTypeOption = SqlMapper.SqlTypeToSqlDataTypeOption(param.ParameterTypeRaw);
                    }

                    if (param.ParameterLength > 0)
                    {
                        dataType.Parameters.Add(new IntegerLiteral {
                            Value = param.ParameterLength.ToString()
                        });
                    }

                    createStoredProcedure.Parameters.Add(new ProcedureParameter
                    {
                        VariableName = new Identifier {
                            Value = $"@{parameterName}"
                        },
                        DataType = dataType,
                        Value    = param.DefaultToNull ? new NullLiteral() : null
                    });
                }
            }

            var parser = new TSql120Parser(false);

            createStoredProcedure.StatementList = new StatementList();
            if (!string.IsNullOrEmpty(storedProcedure.StoredProcedureBody))
            {
                IList <ParseError> errors;
                var script2 =
                    parser.Parse(new StringReader(storedProcedure.StoredProcedureBody), out errors) as TSqlScript;
                if (errors.Count > 0)
                {
                    RaiseSqlErrorsException(errors, storedProcedure.StoredProcedureBody);
                }
                foreach (var batch2 in script2.Batches)
                {
                    foreach (var statement in batch2.Statements)
                    {
                        createStoredProcedure.StatementList.Statements.Add(statement);
                    }
                }
            }

            return(createStoredProcedure);
        }
Beispiel #6
0
        private string InferStoredProcedures(KProtoFile kProtoFile)
        {
            var codeWriter = new CodeWriter();


            var convert = new CStoredProcedureToSSqlServerStoredProcedureConverter();

            foreach (var service in kProtoFile.GeneratedProtoFile.ProtoService)
            {
                foreach (var rpc in service.Rpc)
                {
                    var request = rpc.GetInnerMessageOrRequest();
                    //if  (!request.ProtoField.Any(f=>f.FieldType != GrpcType.__message))
                    //    continue;

                    var storedProc = new CStoredProcedure(DataStoreTypes.Unknown)
                    {
                        DerivedFrom         = request, // rpc,
                        Schema              = InferSchema(service),
                        StoredProcedureName = rpc.RpcName,
                        ResultSetName       = rpc.Response.MessageName + "Dto"//"ResultSet"
                    };

                    foreach (var pp in request.ProtoField)
                    {
                        var parameter = pp;
                        if (parameter.FieldType == GrpcType.__message)
                        {
                            //if the inner message is just a single scalar field, lets use that
                            var m = rpc.ProtoService.ProtoFile.ProtoMessage.FirstOrDefault(pm => pm.MessageName == parameter.MessageType);
                            if (m.ProtoField.Count == 1)
                            {
                                parameter = m.ProtoField.First();

                                /*
                                 * if (parameter.FieldType == GrpcType.__message)
                                 * {
                                 *  m = rpc.ProtoService.ProtoFile.ProtoMessage.FirstOrDefault(pm =>
                                 *      pm.MessageName == parameter.MessageType);
                                 *  if (m.ProtoField.Count == 1)
                                 *  {
                                 *      parameter = m.ProtoField.First();
                                 *
                                 *  }
                                 *  else if (m.ProtoField.Count > 1)
                                 *  {
                                 *      Debugger.Break();
                                 *
                                 *  }
                                 * }*/
                            }
                            else if (m.ProtoField.Count > 1)
                            {
                                /*
                                 * //todo: we need to pass table valued parameters to query
                                 * Debugger.Break();
                                 * parameter = m.ProtoField.First();
                                 *
                                 * if (parameter.FieldType == GrpcType.__message)
                                 * {
                                 *  m = rpc.ProtoService.ProtoFile.ProtoMessage.FirstOrDefault(pm =>
                                 *      pm.MessageName == parameter.MessageType);
                                 *  if (m.ProtoField.Count == 1)
                                 *  {
                                 *      parameter = m.ProtoField.First();
                                 *
                                 *  }
                                 *  else if (m.ProtoField.Count > 1)
                                 *  {
                                 *      //todo: we need to pass table valued parameters to query
                                 *      Debugger.Break();
                                 *      parameter = m.ProtoField.First();
                                 *
                                 *  }
                                 * }
                                 */
                            }
                        }

                        var p = InferStoredProcedureParameter(parameter);
                        if (!storedProc.Parameter.Exists(p2 => p2.ParameterName == p.ParameterName))
                        {
                            storedProc.Parameter.Add(p);
                        }
                    }

                    var tables   = new List <CTable>();
                    var request2 = rpc.GetInnerMessageOrRequest();

                    InferTable(tables, rpc, request2, null);
                    var table = tables.First();

                    if (rpc.OperationIs.HasFlag(COperationIs.Get) ||
                        rpc.OperationIs.HasFlag(COperationIs.Find) ||
                        rpc.OperationIs.HasFlag(COperationIs.List) ||
                        rpc.OperationIs.HasFlag(COperationIs.Read) ||
                        rpc.OperationIs.HasFlag(COperationIs.Dequeue) ||
                        rpc.OperationIs.HasFlag(COperationIs.Check)
                        )
                    {
                        storedProc.StoredProcedureBody = $@"SELECT {table.ColumnAsColumnList()} FROM {table.Schema.SchemaName.WrapReservedAndSnakeCase(DataStoreTypes.SqlServer, false)}.{table.TableName.WrapReservedAndSnakeCase(DataStoreTypes.SqlServer, false)}";
                    }
                    else if (rpc.OperationIs.HasFlag(COperationIs.Set) ||
                             rpc.OperationIs.HasFlag(COperationIs.Update) ||
                             rpc.OperationIs.HasFlag(COperationIs.Create))
                    {
                        storedProc.StoredProcedureBody = $@"PRINT 'todo: UPDATE {table.Schema.SchemaName}.{table.TableName} SET ....'";
                    }

                    codeWriter.WriteLine(convert.Convert(storedProc));
                    codeWriter.WriteLine();
                    codeWriter.WriteLine("GO");
                    codeWriter.WriteLine();

                    this.StoredProcedure.Add(new KStoredProcedure
                    {
                        StoredProcedureName = storedProc.StoredProcedureName,
                        Schema = storedProc.Schema.SchemaName,
                        ReturnsMultipleRows = storedProc.ReturnsMultipleRows,

                        ResultSetName            = storedProc.ResultSetName,
                        ParameterSetName         = storedProc.ParameterSetName,
                        DerivedFrom              = storedProc.DerivedFrom,
                        GeneratedStoredProcedure = storedProc
                    });
                }
            }
            return(codeWriter.ToString());
        }
        public CStoredProcedure Convert(CTable table)
        {
            var storedProcedure = new CStoredProcedure(DataStoreTypes.SqlServer);

            storedProcedure.Schema = new CSchema {
                SchemaName = $"{table.Schema.SchemaName}Api"
            };
            storedProcedure.StoredProcedureName = $"{table.TableName}Update";
            storedProcedure.ResultSetName       = table.TableName;
            storedProcedure.Parameter           = new List <CStoredProcedureParameter>();
            foreach (var column in table.Column)
            {
                var parameter = new CStoredProcedureParameter
                {
                    ParameterName    = $"{column.ColumnName}",
                    ParameterType    = column.ColumnType,
                    ParameterTypeRaw = column.ColumnTypeRaw,
                    ParameterLength  = column.ColumnLength,
                    SourceColumn     = column
                };
                storedProcedure.Parameter.Add(parameter);
            }
            var stringBuilder = new StringBuilder();

            stringBuilder.AppendLine($"UPDATE [{table.Schema.SchemaName}].[{table.TableName}] ");
            stringBuilder.AppendLine("SET");

            var pkColumns = table.GetPrimaryKeyColumns();
            var first     = true;

            foreach (var column in table.Column)
            {
                if (column.IsIdentity)
                {
                    continue;
                }

                if (pkColumns.Contains(column))
                {
                    continue;
                }

                if (!first)
                {
                    stringBuilder.Append("\t\t,");
                }

                stringBuilder.AppendLine($"{column.ColumnName} = @{column.ColumnName}");
                first = false;
            }
            stringBuilder.AppendLine("WHERE");
            first = true;
            foreach (var pkColumn in pkColumns)
            {
                if (!first)
                {
                    stringBuilder.Append(" AND ");
                }
                stringBuilder.AppendLine($"\t\t{pkColumn.ColumnName} = @{pkColumn.ColumnName}");
            }

            storedProcedure.StoredProcedureBody = stringBuilder.ToString();
            return(storedProcedure);
        }
Beispiel #8
0
        public CStoredProcedure Convert(CTable table)
        {
            var storedProcedure = new CStoredProcedure(DataStoreTypes.SqlServer);

            storedProcedure.Schema = new CSchema {
                SchemaName = $"{table.Schema.SchemaName}Api"
            };
            storedProcedure.StoredProcedureName = $"{table.TableName}Add";
            storedProcedure.ResultSetName       = table.TableName;
            storedProcedure.Parameter           = new List <CStoredProcedureParameter>();
            foreach (var column in table.Column)
            {
                if (column.IsIdentity)
                {
                    continue;
                }

                var parameter = new CStoredProcedureParameter
                {
                    ParameterName = $"{column.ColumnName}",
                    ParameterType = column.ColumnType,

                    ParameterTypeRaw = column.ColumnTypeRaw,
                    ParameterLength  = column.ColumnLength,
                    SourceColumn     = column
                };
                storedProcedure.Parameter.Add(parameter);
            }
            var stringBuilder = new StringBuilder();

            stringBuilder.AppendLine($"INSERT INTO [{table.Schema.SchemaName}].[{table.TableName}] ");
            stringBuilder.Append("(");
            var first = true;

            foreach (var column in table.Column)
            {
                if (column.IsIdentity)
                {
                    continue;
                }
                if (!first)
                {
                    stringBuilder.Append("\t\t,");
                }

                stringBuilder.AppendLine(column.ColumnName);
                first = false;
            }
            stringBuilder.AppendLine(")");
            stringBuilder.AppendLine("VALUES");
            stringBuilder.Append("(");
            first = true;
            foreach (var parameter in storedProcedure.Parameter)
            {
                if (!first)
                {
                    stringBuilder.Append("\t\t,");
                }
                first = false;
                stringBuilder.AppendLine(parameter.ParameterName);
            }
            stringBuilder.AppendLine(")");
            stringBuilder.AppendLine();

            var identityCol = table.Column.FirstOrDefault(c => c.IsIdentity);

            if (identityCol != null)
            {
                stringBuilder.AppendLine(
                    $"SELECT CAST(SCOPE_IDENTITY() as {SqlMapper.DbTypeToSqlDbType(identityCol.ColumnType)})");
            }
            storedProcedure.StoredProcedureBody = stringBuilder.ToString();
            return(storedProcedure);
        }
        public string Convert(CStoredProcedure storedProcedure)
        {
            var converter             = new CStoredProcedureToCreateProcedureStatementConverter();
            var createStoredProcedure = converter.Convert(storedProcedure);

            var snakeCaseVisitor = new SnakeCaseVisitor();

            createStoredProcedure.Accept(snakeCaseVisitor);


            var codeWriter = new CodeWriter();

            codeWriter.WriteLine($@"CREATE OR REPLACE FUNCTION {storedProcedure.Schema.SchemaName.WrapReservedAndSnakeCase(storedProcedure.DatabaseType, storedProcedure.ConvertToSnakeCase)}.{storedProcedure.StoredProcedureName.WrapReservedAndSnakeCase(storedProcedure.DatabaseType, storedProcedure.ConvertToSnakeCase)} ");
            codeWriter.WriteLine($@"(");
            bool first = true;

            foreach (var parameter in storedProcedure.Parameter)
            {
                if (!first)
                {
                    codeWriter.Write(", ");
                }
                first = false;

                var parameterType = string.Empty;
                if (parameter.ParameterTypeIsUserDefined)
                {
                    if (parameter.ParameterTypeRaw == "sysname")
                    {
                        //fixup. Todo: implement a better way
                        parameterType             = SqlMapper.NpgsqlDbTypeToPostgres(NpgsqlTypes.NpgsqlDbType.Varchar);
                        parameter.ParameterLength = 128;
                    }
                    else
                    {
                        //todo: need schema included
                        parameterType = $"{parameter.ParameterTypeRawSchema.WrapReservedAndSnakeCase(storedProcedure.DatabaseType, storedProcedure.ConvertToSnakeCase)}.{parameter.ParameterTypeRaw.WrapReservedAndSnakeCase(storedProcedure.DatabaseType, storedProcedure.ConvertToSnakeCase)} []";
                    }
                }
                else
                {
                    parameterType = SqlMapper.NpgsqlDbTypeToPostgres(SqlMapper.DbTypeToNpgsqlDbType(parameter.ParameterType));
                }
                //todo: remove p_ prefix. Too much UI change, better to manually fix the stored procs
                var parameterName = "p_" + parameter.ParameterName;

                codeWriter.Write($@"{parameterName.WrapReservedAndSnakeCase(storedProcedure.DatabaseType, storedProcedure.ConvertToSnakeCase)} {parameterType} ");
                if (parameter.DoesNeedLength())
                {
                    codeWriter.Write($"({parameter.ParameterLength})");
                }
            }
            codeWriter.WriteLine();
            codeWriter.WriteLine($@")");
            if (storedProcedure.ResultSet.Count > 0)
            {
                codeWriter.Write($@"RETURNS TABLE (");

                {
                    var first2 = true;
                    foreach (var resultCol in storedProcedure.ResultSet)
                    {
                        if (!first2)
                        {
                            codeWriter.WriteLine(",");
                        }
                        first2 = false;

                        codeWriter.Write($"{resultCol.ColumnName.WrapReservedAndSnakeCase(storedProcedure.DatabaseType, storedProcedure.ConvertToSnakeCase)}");

                        codeWriter.Write($" {SqlMapper.NpgsqlDbTypeToPostgres(SqlMapper.DbTypeToNpgsqlDbType(resultCol.ColumnType))}");
                        if (resultCol.DoesNeedLength())
                        {
                            codeWriter.Write($"({resultCol.ColumnLength})");
                        }

                        if (!resultCol.IsNullable)
                        {
                            codeWriter.Write(" NOT NULL");
                        }
                    }
                }
                codeWriter.Write($@") ");
            }
            else
            {
                codeWriter.Write("RETURNS void ");
            }
            codeWriter.WriteLine($@"AS $func$");

            codeWriter.WriteLine($@"BEGIN");
            codeWriter.Indent();
            if (storedProcedure.ResultSet.Count > 0)
            {
                codeWriter.WriteLine($@"RETURN QUERY");
            }


            codeWriter.WriteLine("--TODO: Manually convert the sql below to Postgresql");

            if (storedProcedure.ResultSet.Count > 0)
            {
                codeWriter.Write("SELECT ");
                var first3            = true;
                var sampleDataService = new SamplePostgresDataService();
                foreach (var resultCol in storedProcedure.ResultSet)
                {
                    if (!first3)
                    {
                        codeWriter.Write(",");
                    }
                    first3 = false;

                    var npgsqlType   = SqlMapper.DbTypeToNpgsqlDbType(resultCol.ColumnType);
                    var postgresType = SqlMapper.NpgsqlDbTypeToPostgres(npgsqlType);
                    codeWriter.Write($"CAST ({sampleDataService.GetSampleData(npgsqlType, resultCol.ColumnLength)} AS {postgresType} ) AS {resultCol.ColumnName.WrapReservedAndSnakeCase(DataStoreTypes.Postgres, storedProcedure.ConvertToSnakeCase)}");
                }


                codeWriter.WriteLine(";");
            }
            codeWriter.WriteLine("/*");
            //codeWriter.WriteLine(storedProcedure.StoredProcedureBody);


            var scriptGen = new Sql120ScriptGenerator();


            foreach (var statement2 in createStoredProcedure.StatementList.Statements)
            {
                string scriptOut;
                scriptGen.GenerateScript(statement2, out scriptOut);

                codeWriter.WriteLine(scriptOut);
            }

            codeWriter.WriteLine("*/");
            codeWriter.Unindent();
            codeWriter.WriteLine($@"END");
            codeWriter.WriteLine($@"$func$ LANGUAGE plpgsql;");
            return(codeWriter.ToString());
        }
        public CStoredProcedure Convert(CTable table)
        {
            //http://michaeljswart.com/2011/09/mythbusting-concurrent-updateinsert-solutions/

            var storedProcedure = new CStoredProcedure(DataStoreTypes.SqlServer)
            {
                Schema = new CSchema {
                    SchemaName = $"{table.Schema.SchemaName}Api"
                },
                StoredProcedureName = $"{table.TableName}AddUpdate",
                ParameterSetName    = table.TableName,
                ResultSetName       = $"{table.TableName}AddUpdateResult",
                ReturnsMultipleRows = true,
                DataOperationIs     = COperationIs.Add | COperationIs.Update | COperationIs.CRUD
            };

            table.InsertStoredProcedure = storedProcedure;

            storedProcedure.Parameter.AddRange(GetParameters(table));
            var primaryKeyCol              = table.Column.FirstOrDefault(c => c.IsPrimaryKey);
            var insertUpdateColumnList     = GetInsertUpdateColumnList(table);
            var insertUpdateColumnMappings = GetInsertUpdateColumnMappings(table);
            var insertUpdateColumnValues   = GetInsertUpateColumnValues(table);
            var redefines        = GetRedefines(table);
            var pkCompares       = GetPrimaryKeyCompares(table);
            var rowVersionColumn = table.GetRowVersionColumn();
            var insertCondition  = rowVersionColumn != null
                ? $" AND NEW_T.[{primaryKeyCol.ColumnName}] = 0  AND NEW_T.[{rowVersionColumn.ColumnName}] IS NULL "
                : string.Empty;
            var updateCondition = rowVersionColumn != null
                ? $" AND (NEW_T.[{rowVersionColumn.ColumnName}] IS NULL OR T.[{rowVersionColumn.ColumnName}] = NEW_T.[{rowVersionColumn.ColumnName}]) "
                : string.Empty;

            var stringBuilder = new StringBuilder();

            stringBuilder.AppendLine("SET NOCOUNT ON;");
            stringBuilder.AppendLine(
                "SET XACT_ABORT ON;"); //http://michaeljswart.com/2015/10/dont-abandon-your-transactions/
            stringBuilder.AppendLine();
            //create table variable to hold output
            stringBuilder.Append($@"DECLARE @Output TABLE (Action VARCHAR(20),
                                        [{primaryKeyCol.ColumnName}] {
                    SqlMapper.DbTypeToSqlDataTypeOption(primaryKeyCol.ColumnType)
                }");
            if (primaryKeyCol.ColumnLength > 0)
            {
                stringBuilder.Append($"({primaryKeyCol.ColumnLength})");
            }

            var rowVersionCol         = string.Empty;
            var insertedRowVersionCol = string.Empty;

            if (rowVersionColumn != null)
            {
                rowVersionCol         = $", {rowVersionColumn.ColumnName}";
                insertedRowVersionCol =
                    $", inserted.[{rowVersionColumn.ColumnName}] as '[{rowVersionColumn.ColumnName}]'";
                stringBuilder.Append($", [{rowVersionColumn.ColumnName}]  binary(8)");
            }
            stringBuilder.AppendLine($");");


            stringBuilder.AppendLine();
            stringBuilder.AppendLine(
                $@"MERGE [{table.Schema.SchemaName}].[{table.TableName}] WITH (HOLDLOCK) AS T
                    USING (SELECT {redefines} ) as NEW_T
                    ON {pkCompares}
                    WHEN MATCHED {updateCondition} THEN
                     UPDATE
                     SET 
                        {insertUpdateColumnMappings}
                     WHEN NOT MATCHED {insertCondition} THEN

                     INSERT
                     (
                         {insertUpdateColumnList}
                     )
                     VALUES
                     (
                        {insertUpdateColumnValues}
                     )
                    OUTPUT	
                        $action, inserted.[{primaryKeyCol.ColumnName}] as '[{primaryKeyCol.ColumnName}]' {
                        insertedRowVersionCol
                    } INTO @Output;

                    --TODO: Use OUTPUT to insert original values into Audit tables

                    SELECT Action, [{primaryKeyCol.ColumnName}] {rowVersionCol} FROM @Output;");

            /*
             * var identityCol = table.Column.FirstOrDefault(c => c.IsIdentity);
             * if (identityCol != null)
             * {
             *   stringBuilder.AppendLine();
             *   stringBuilder.AppendLine($"SELECT CAST(SCOPE_IDENTITY() as {SqlMapper.DbTypeToSqlDbType( identityCol.ColumnType)})");
             *
             * }
             */
            storedProcedure.StoredProcedureBody = stringBuilder.ToString();

            storedProcedure.ResultSet.Add(new CColumn(storedProcedure)
            {
                ColumnName      = "Action",
                ColumnTypeRaw   = "varchar",
                ColumnSqlDbType = SqlDbType.VarChar,
                ColumnType      = DbType.AnsiString,
                ColumnLength    = 20
            });
            storedProcedure.ResultSet.Add(new CColumn(storedProcedure)
            {
                ColumnName      = primaryKeyCol.ColumnName,
                ColumnTypeRaw   = primaryKeyCol.ColumnTypeRaw,
                ColumnSqlDbType = primaryKeyCol.ColumnSqlDbType,
                ColumnType      = primaryKeyCol.ColumnType,
                ColumnLength    = primaryKeyCol.ColumnLength
            });
            if (rowVersionColumn != null)
            {
                storedProcedure.ResultSet.Add(new CColumn(storedProcedure)
                {
                    ColumnName    = rowVersionColumn.ColumnName,
                    ColumnTypeRaw = rowVersionColumn.ColumnTypeRaw,
                    ColumnType    = rowVersionColumn.ColumnType,

                    ColumnSqlDbType = rowVersionColumn.ColumnSqlDbType,
                    ColumnLength    = rowVersionColumn.ColumnLength
                });
            }
            return(storedProcedure);
        }
        private CProtoRpc BuildRpcFromStoredProc(CProtoFile protoFile, CProtoService protoService, CMethod method,
                                                 CStoredProcedure storedProcedure)
        {
            var rpc = new CProtoRpc(protoService)
            {
                RpcName        = storedProcedure.StoredProcedureName,
                RpcDescription = storedProcedure.StoredProcedureDescription,
                //DomainModelName = storedProcedure.ResultSetName,
                DerivedFrom = storedProcedure
            };

            rpc.Request = new CProtoMessage(rpc)
            {
                IsRequest   = true,
                MessageName = $"{storedProcedure.StoredProcedureName}Request"
            };
            rpc.Response = new CProtoMessage(rpc)
            {
                IsResponse  = true,
                MessageName = $"{storedProcedure.StoredProcedureName}Response"
            };

            var requestMessage = rpc.Request;

            if (!string.IsNullOrEmpty(storedProcedure.ParameterSetName))
            {
                rpc.Request.ProtoField.Add(new CProtoMessageField(storedProcedure)
                {
                    IsScalar    = false,
                    MessageType = storedProcedure.ParameterSetName,
                    FieldName   = storedProcedure.ParameterSetName
                });

                requestMessage = new CProtoMessage(rpc)
                {
                    IsRequest   = true,
                    MessageName = storedProcedure.ParameterSetName
                };
                if (!protoFile.ProtoMessage.Exists(pm => pm.MessageName == requestMessage.MessageName))
                {
                    protoFile.ProtoMessage.Add(requestMessage);
                }
            }
            foreach (var parameter in storedProcedure.Parameter)
            {
                var field = new CProtoMessageField(parameter)
                {
                    FieldName = parameter.ParameterName //.SourceColumn.ColumnName,
                };

                if (!parameter.ParameterTypeIsUserDefined)
                {
                    var sqlType = SqlMapper.ParseValueAsSqlDbType(parameter.ParameterTypeRaw);
                    field.FieldType = SqlMapper.SqlDbTypeToGrpcType(sqlType);
                }
                else
                {
                    //todo: property handle user defined sql types (tables)
                    //lookup table type
                    //for now, use the data type of the first column, assumes single column table
                    var tableType = FindTableType(parameter.ParameterTypeRaw);
                    var converter = new CTableTypeToCClassConverter();
                    var @class    = converter.Convert(tableType.GeneratedTableType);
                    field.FieldType   = GrpcType.__string;
                    field.IsScalar    = false;
                    field.Repeated    = true;
                    field.MessageType = $@"{@class.ClassName}";

                    if (!protoFile.ProtoMessage.Exists(pm => pm.MessageName == field.MessageType))
                    {
                        //create a message
                        var tableTypeDerivedMessage = new CProtoMessage(rpc)
                        {
                            MessageName = field.MessageType
                        };
                        foreach (var property in @class.Property)
                        {
                            var field2 = new CProtoMessageField(property)
                            {
                                FieldName = property.PropertyName,
                                FieldType = SqlMapper.ClrTypeToGrpcType(SqlMapper.ClrTypeAliasToClrType(property.Type))
                            };

                            tableTypeDerivedMessage.ProtoField.Add(field2);
                        }
                        protoFile.ProtoMessage.Add(tableTypeDerivedMessage);
                    }
                }

                requestMessage.ProtoField.Add(field);
            }

            var responseMessage = rpc.Response;

            if (!string.IsNullOrEmpty(storedProcedure.ResultSetName))
            {
                rpc.Response.ProtoField.Add(new CProtoMessageField(null)
                {
                    IsScalar    = false,
                    Repeated    = true,
                    MessageType = storedProcedure.ResultSetName,
                    FieldName   = storedProcedure.ResultSetName
                });

                responseMessage = new CProtoMessage(rpc)
                {
                    IsResponse  = true,
                    MessageName = storedProcedure.ResultSetName
                };
                if (!protoFile.ProtoMessage.Exists(pm => pm.MessageName == responseMessage.MessageName))
                {
                    protoFile.ProtoMessage.Add(responseMessage);
                }
            }
            foreach (var resultColumn in storedProcedure.ResultSet)
            {
                var field = new CProtoMessageField(resultColumn)
                {
                    FieldName = resultColumn.ColumnName,
                    FieldType = SqlMapper.SqlDbTypeToGrpcType(resultColumn.ColumnSqlDbType)
                };

                responseMessage.ProtoField.Add(field);
            }
            return(rpc);
        }
Beispiel #12
0
        public CStoredProcedure Read(string connectionString, string schema, string storedProcedureName, string storedProcedureText)
        {
            this.ConnectionString = connectionString;
            var storedProcedure = new CStoredProcedure(DataStoreTypes.SqlServer);

            storedProcedure.Schema = new CSchema {
                SchemaName = schema
            };
            storedProcedure.StoredProcedureName = storedProcedureName;
            storedProcedure.StoredProcedureBody = GetStoredProcBody(storedProcedureText);

            storedProcedure.Parameter.AddRange(GetParameters(ConnectionString, storedProcedureText, storedProcedure));

            using (var sqlConnection = new SqlConnection(ConnectionString))
            {
                var sqlViewQuery =
                    $@"DECLARE @sql NVARCHAR(MAX) = N'EXEC  [{schema}].[{storedProcedureName}];';
                    SELECT dm.name as 'column_name', t.name as 'data_type',dm.is_nullable,dm.max_length as 'character_maximum_length',is_hidden
    FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1) dm
	join sys.types t on dm.system_type_id = t.system_type_id
    and dm.system_type_id = t.user_type_id
    WHERE is_hidden=0;";

                sqlConnection.Open();
                var sqlCommand = new SqlCommand(sqlViewQuery, sqlConnection);
                var dataReader = sqlCommand.ExecuteReader();


                var dataTable = new DataTable();

                dataTable.Columns.Add(new DataColumn {
                    ColumnName = "column_name"
                });
                dataTable.Columns.Add(new DataColumn {
                    ColumnName = "data_type"
                });
                dataTable.Columns.Add(new DataColumn {
                    ColumnName = "is_nullable"
                });
                dataTable.Columns.Add(new DataColumn {
                    ColumnName = "character_maximum_length", DataType = typeof(int)
                });
                dataTable.Columns.Add(new DataColumn {
                    ColumnName = "storedprocedure_name"
                });
                dataTable.Columns.Add(new DataColumn {
                    ColumnName = "storedprocedure_schema"
                });

                while (dataReader.Read())
                {
                    var dataRow = dataTable.NewRow();
                    dataRow["column_name"] = dataReader["column_name"];
                    dataRow["data_type"]   = dataReader["data_type"];
                    dataRow["is_nullable"] = dataReader["is_nullable"];
                    dataRow["character_maximum_length"] = dataReader["character_maximum_length"];
                    dataRow["storedprocedure_name"]     = storedProcedureName;
                    dataRow["storedprocedure_schema"]   = schema;

                    dataTable.Rows.Add(dataRow);
                }

                foreach (var row2 in dataTable.Rows)
                {
                    var row    = row2 as DataRow;
                    var column = new CColumn(storedProcedure)
                    {
                        ColumnName      = row[0] != DBNull.Value ? (string)row[0] : null,
                        ColumnTypeRaw   = (string)row[1],
                        ColumnSqlDbType = SqlMapper.ParseValueAsSqlDbType((string)row[1]),
                        ColumnType      = SqlMapper.GetDbType((string)row[1]),
                        ColumnLength    = row[3] != DBNull.Value ? (int)row[3] : -1
                    };

                    storedProcedure.ResultSet.Add(column);
                }
                if (storedProcedure.HasResultSet)
                {
                    storedProcedure.ResultSetName = storedProcedure.StoredProcedureName + "Dto"; // "ResultSet";
                }
                return(storedProcedure);
            }
        }
Beispiel #13
0
        private List <CStoredProcedureParameter> GetParameters(string connectionString, string sqlText,
                                                               CStoredProcedure storedProcedure)
        {
            var storedProcedureParameters = new List <CStoredProcedureParameter>();

            var scriptGen = new Sql120ScriptGenerator();

            using (var sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();

                var parser = new TSql120Parser(false);

                var statementList = new StatementList();
                IList <ParseError> errors;
                var script2 = parser.Parse(new StringReader(sqlText), out errors) as TSqlScript;
                if (errors.Count > 0)
                {
                    var errorList = new StringBuilder();
                    foreach (var error in errors)
                    {
                        errorList.AppendLine($"{error.Message}<br/>");
                    }
                    throw new ApplicationException(errorList.ToString());
                }
                foreach (var batch2 in script2.Batches)
                {
                    foreach (var statement in batch2.Statements)
                    {
                        var createProcedureStatement = statement as CreateProcedureStatement;

                        if (createProcedureStatement == null)
                        {
                            continue;
                        }

                        foreach (var param in createProcedureStatement.Parameters)
                        {
                            //(new System.Collections.Generic.Mscorlib_CollectionDebugView<Microsoft.SqlServer.TransactSql.ScriptDom.Literal>
                            //  (((Microsoft.SqlServer.TransactSql.ScriptDom.ParameterizedDataTypeReference)param.DataType).Parameters).Items[0]).Value;
                            var length = 0;
                            if ((param.DataType as ParameterizedDataTypeReference).Parameters.Count > 0)
                            {
                                var lengthString = (param.DataType as ParameterizedDataTypeReference).Parameters[0].Value;
                                if ((param.DataType as ParameterizedDataTypeReference).Parameters[0] is Microsoft.SqlServer.TransactSql.ScriptDom.MaxLiteral)
                                {
                                    length = -1;
                                }
                                else
                                {
                                    length = int.Parse(lengthString);
                                }
                            }
                            var storedProcedureParameter = new CStoredProcedureParameter
                            {
                                ParameterName = param.VariableName.Value.Replace("@", "").Replace("_Collection", ""),

                                ParameterTypeIsUserDefined = param.DataType is UserDataTypeReference,
                                ParameterTypeRaw           = param.DataType.Name.BaseIdentifier.Value,
                                ParameterTypeRawSchema     = param.DataType.Name?.SchemaIdentifier?.Value,
                                SourceColumn =
                                    new CColumn(storedProcedure)
                                {
                                    ColumnName = param.VariableName.Value.Replace("@", "")
                                },
                                IsCollection = param.VariableName.Value.EndsWith("_Collection")
                            };
                            if (length > 0)
                            {
                                storedProcedureParameter.ParameterLength           = length;
                                storedProcedureParameter.SourceColumn.ColumnLength = length;
                            }
                            if (!storedProcedureParameter.ParameterTypeIsUserDefined)
                            {
                                storedProcedureParameter.ParameterType =
                                    SqlMapper.SqlDbTypeToDbType(
                                        SqlMapper.ParseValueAsSqlDbType(param.DataType.Name.BaseIdentifier.Value));
                            }
                            storedProcedureParameters.Add(storedProcedureParameter);
                        }
                    }
                }
                sqlConnection.Close();
            }
            return(storedProcedureParameters);
        }
Beispiel #14
0
        public CClass ConvertByResultSet(CStoredProcedure storedProcedure)
        {
            if (string.IsNullOrEmpty(storedProcedure.ResultSetName))
            {
                throw new Exception("cannot create CClass without a class name");
            }

            var @class = new CClass(storedProcedure.ResultSetName)
            {
                DerivedFrom = storedProcedure,
                Namespace   = new CNamespace {
                    NamespaceName = storedProcedure.Schema.SchemaName
                }
            };

            if (storedProcedure.ResultSet.Exists(r => r.ColumnType == DbType.DateTime2))
            {
                @class.NamespaceRef.Add(new CNamespaceRef()
                {
                    ReferenceTo = new CNamespace()
                    {
                        NamespaceName = "System"
                    }
                });
            }

            if (!string.IsNullOrEmpty(storedProcedure.ResultSetName))
            {
                foreach (var column in storedProcedure.ResultSet)
                {
                    var prop = new CProperty
                    {
                        PropertyName = column.ColumnName,
                        Type         = column.ColumnType.ToClrTypeName(),
                        MaxLength    = column.ColumnLength
                    };
                    @class.Property.Add(prop);
                }
            }


            return(@class);

            /*
             * var view = new SView
             * {
             *  Schema = new SSchema {  SchemaName = storedProcedure.Schema.SchemaName},
             *  ViewName = storedProcedure.StoredProcedureName
             * };
             * view.Column = new List<SColumn>();
             *
             * foreach (var col in storedProcedure.ResultSet)
             * {
             *  var column = new SColumn(view)
             *  {
             *      ColumnName = col.ColumnName,
             *      ColumnTypeRaw = col.ColumnTypeRaw,
             *      ColumnSqlDbType = col.ColumnSqlDbType,
             *      ColumnType = col.ColumnType,
             *      ColumnLength = col.ColumnLength
             *  };
             *  view.Column.Add(column);
             * }
             * return view;
             */
        }