Esempio n. 1
0
        public List <Poco.Column> GetColumns(int tableId)
        {
            var lstColumns = new List <Poco.Column>();

            try
            {
                var query = "SELECT T0.column_id, T0.name, T0.system_type_id, " +
                            "(SELECT CASE WHEN COUNT(1) > 0 THEN 1 ELSE 0 END FROM sys.index_columns TT where TT.object_id = T0.object_id and TT.column_id = T0.column_id) AS pk " +
                            "FROM sys.columns T0 " +
                            $"WHERE object_id = {tableId} " +
                            "ORDER BY column_id";

                var dt = MssqlHelper.ExecuteQuery(query);
                foreach (DataRow dr in dt.Rows)
                {
                    var col = new Poco.Column();
                    col.Id           = int.Parse(dr["column_id"].ToString());
                    col.Name         = dr["name"].ToString();
                    col.Type         = Types.FirstOrDefault(x => x.Id == int.Parse(dr["system_type_id"].ToString()));
                    col.IsPrimaryKey = (dr["pk"].ToString() == "1");

                    lstColumns.Add(col);
                }

                return(lstColumns);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Esempio n. 2
0
        public void StoredProcedure(Poco.Table table, string PathRoot)
        {
            try
            {
                string nameTable = StringExtension.ToPascalCase(table.Name);

                string pathStoredProcedure = Path.Combine(PathRoot, "StoredProcedure");
                string fileStoredProcedure = $"{nameTable}.sql";

                if (Directory.Exists(pathStoredProcedure) == false)
                {
                    Directory.CreateDirectory(pathStoredProcedure);
                }

                string pathFileStoredProcedure = Path.Combine(pathStoredProcedure, fileStoredProcedure);
                if (File.Exists(pathFileStoredProcedure) == true)
                {
                    File.Delete(pathFileStoredProcedure);
                }

                using (StreamWriter objReader = new StreamWriter(pathFileStoredProcedure))
                {
                    #region Cabecera
                    objReader.WriteLine("set ANSI_NULLS ON");
                    objReader.WriteLine("set QUOTED_IDENTIFIER ON");
                    objReader.WriteLine("GO");
                    objReader.WriteLine("");
                    #endregion

                    List <Poco.Column> lstColumns          = this.GetColumns(table.Id);
                    List <Poco.Column> lstColumnsWithoutPk = lstColumns.Where(x => x.IsPrimaryKey == false).ToList();
                    Poco.Column        objPrimaryKey       = lstColumns.FirstOrDefault(x => x.IsPrimaryKey == true);

                    #region Insert

                    objReader.WriteLine($"CREATE PROCEDURE Sp{nameTable}Insertar");

                    string head = $"\tINSERT INTO {nameTable} (";
                    string foot = "\tVALUES (";
                    string ret  = "";

                    for (int i = 0; i < lstColumns.Count; i++)
                    {
                        string nameColumn = lstColumns[i].Name;
                        string typeColumn = lstColumns[i].Type.Name.ToUpper();
                        string comma      = (i == lstColumns.Count - 1) ? "" : ",";

                        if (lstColumns[i].IsPrimaryKey)
                        {
                            objReader.WriteLine($"@{nameColumn} AS {typeColumn} OUTPUT{comma}");

                            ret = $"\tSET @{nameColumn} = @@IDENTITY";
                        }
                        else
                        {
                            objReader.WriteLine($"@{nameColumn} AS {typeColumn}{comma}");

                            string separator = (i == lstColumns.Count - 1) ? ")" : ",";
                            head += $"{nameColumn}{separator}";
                            foot += $"@{nameColumn}{separator}";
                        }
                    }

                    objReader.WriteLine("AS");
                    objReader.WriteLine("BEGIN");

                    objReader.WriteLine(head);
                    objReader.WriteLine(foot);
                    objReader.WriteLine(ret);

                    objReader.WriteLine("END");
                    objReader.WriteLine("GO");

                    objReader.WriteLine("");

                    #endregion

                    #region Update

                    objReader.WriteLine($"CREATE PROCEDURE Sp{nameTable}Actualizar");

                    for (int i = 0; i < lstColumns.Count; i++)
                    {
                        string nameColumn = lstColumns[i].Name;
                        string typeColumn = lstColumns[i].Type.Name.ToUpper();
                        string comma      = (i == lstColumns.Count - 1) ? "" : ",";

                        objReader.WriteLine($"@{nameColumn} AS {typeColumn}{comma}");
                    }

                    objReader.WriteLine("AS");
                    objReader.WriteLine("BEGIN");
                    objReader.WriteLine($"\tUPDATE {nameTable}");

                    for (int i = 0; i < lstColumnsWithoutPk.Count; i++)
                    {
                        string nameColumn = lstColumnsWithoutPk[i].Name.Trim();
                        string comma      = (i == lstColumnsWithoutPk.Count - 1) ? "" : ",";
                        if (i == 0)
                        {
                            objReader.WriteLine($"\tSET {nameColumn} = @{nameColumn}{comma}");
                        }
                        else
                        {
                            objReader.WriteLine($"\t{nameColumn} = @{nameColumn}{comma}");
                        }
                    }

                    if (objPrimaryKey != null)
                    {
                        string nameColumn = objPrimaryKey.Name.Trim();
                        objReader.WriteLine($"\tWHERE {nameColumn} = @{nameColumn}");
                    }

                    objReader.WriteLine("END");
                    objReader.WriteLine("GO");
                    objReader.WriteLine("");

                    #endregion

                    #region Delete

                    objReader.WriteLine($"CREATE PROCEDURE Sp{nameTable}Eliminar");

                    if (objPrimaryKey != null)
                    {
                        string nameColumnPK = objPrimaryKey.Name.Trim();
                        string typeColumnPK = objPrimaryKey.Type.Name.ToUpper();
                        objReader.WriteLine($"@{nameColumnPK} AS {typeColumnPK}");
                    }

                    objReader.WriteLine("AS");
                    objReader.WriteLine("BEGIN");
                    objReader.WriteLine($"\tDELETE FROM {nameTable}");

                    if (objPrimaryKey != null)
                    {
                        string nameColumnPK = objPrimaryKey.Name.Trim();
                        objReader.WriteLine($"\tWHERE {nameColumnPK} = @{nameColumnPK}");
                    }

                    objReader.WriteLine("END");
                    objReader.WriteLine("GO");
                    objReader.WriteLine("");

                    #endregion

                    #region List
                    objReader.WriteLine($"CREATE PROCEDURE Sp{nameTable}Listar");
                    objReader.WriteLine("AS");
                    objReader.WriteLine("BEGIN");
                    for (int i = 0; i < lstColumns.Count; i++)
                    {
                        string nameColumn = lstColumns[i].Name.Trim();
                        string comma      = (i == lstColumns.Count - 1) ? "" : ",";
                        if (i == 0)
                        {
                            objReader.WriteLine($"\tSELECT {nameColumn}{comma}");
                        }
                        else
                        {
                            objReader.WriteLine($"\t{nameColumn}{comma}");
                        }
                    }

                    objReader.WriteLine($"\tFROM {nameTable} WITH(NOLOCK)");
                    objReader.WriteLine("END");
                    objReader.WriteLine("GO");
                    objReader.WriteLine("");

                    #endregion

                    #region Get

                    objReader.WriteLine($"CREATE PROCEDURE Sp{nameTable}Obtener");

                    if (objPrimaryKey != null)
                    {
                        string nameColumnPK = objPrimaryKey.Name.Trim();
                        string typeColumnPK = objPrimaryKey.Type.Name.ToUpper();
                        objReader.WriteLine($"@{nameColumnPK} AS {typeColumnPK}");
                    }

                    objReader.WriteLine("AS");
                    objReader.WriteLine("BEGIN");

                    for (int i = 0; i < lstColumns.Count; i++)
                    {
                        string nameColumn = lstColumns[i].Name.Trim();
                        string comma      = (i == lstColumns.Count - 1) ? "" : ",";
                        if (i == 0)
                        {
                            objReader.WriteLine($"\tSELECT TOP 1 {nameColumn}{comma}");
                        }
                        else
                        {
                            objReader.WriteLine($"\t{nameColumn}{comma}");
                        }
                    }

                    objReader.WriteLine($"\tFROM {nameTable} WITH(NOLOCK)");

                    if (objPrimaryKey != null)
                    {
                        string nameColumnPK = objPrimaryKey.Name.Trim();
                        objReader.WriteLine($"\tWHERE {nameColumnPK} = @{nameColumnPK}");
                    }

                    objReader.WriteLine("END");
                    objReader.WriteLine("GO");
                    objReader.WriteLine("");

                    #endregion

                    #region First

                    objReader.WriteLine($"CREATE PROCEDURE Sp{nameTable}Primero");
                    objReader.WriteLine("AS");
                    objReader.WriteLine("BEGIN");

                    objReader.WriteLine("\tSELECT TOP 1 ");
                    for (int i = 0; i < lstColumns.Count; i++)
                    {
                        string nameColumn = lstColumns[i].Name.Trim();
                        string comma      = (i == lstColumns.Count - 1) ? "" : ",";
                        objReader.WriteLine($"\t{nameColumn}{comma}");
                    }

                    objReader.WriteLine($"\tFROM {nameTable} WITH(NOLOCK)");

                    if (objPrimaryKey != null)
                    {
                        string nameColumnPK = objPrimaryKey.Name.Trim();
                        objReader.WriteLine($"\tORDER BY  {nameColumnPK} ASC");
                    }

                    objReader.WriteLine("END");
                    objReader.WriteLine("GO");
                    objReader.WriteLine("");

                    #endregion

                    #region Last

                    objReader.WriteLine($"CREATE PROCEDURE Sp{nameTable}Ultimo");
                    objReader.WriteLine("AS");
                    objReader.WriteLine("BEGIN");

                    objReader.WriteLine("\tSELECT TOP 1 ");
                    for (int i = 0; i < lstColumns.Count; i++)
                    {
                        string nameColumn = lstColumns[i].Name.Trim();
                        string comma      = (i == lstColumns.Count - 1) ? "" : ",";
                        objReader.WriteLine($"\t{nameColumn}{comma}");
                    }

                    objReader.WriteLine($"\tFROM {nameTable} WITH(NOLOCK)");

                    if (objPrimaryKey != null)
                    {
                        string nameColumnPK = objPrimaryKey.Name.Trim();
                        objReader.WriteLine($"\tORDER BY  {nameColumnPK} DESC");
                    }

                    objReader.WriteLine("END");
                    objReader.WriteLine("GO");
                    objReader.WriteLine("");

                    #endregion

                    #region Previous

                    objReader.WriteLine($"CREATE PROCEDURE Sp{nameTable}Anterior");

                    if (objPrimaryKey != null)
                    {
                        string nameColumnPK = objPrimaryKey.Name.Trim();
                        string typeColumnPK = objPrimaryKey.Type.Name.ToUpper();
                        objReader.WriteLine($"@{nameColumnPK} AS {typeColumnPK}");
                    }

                    objReader.WriteLine("AS");
                    objReader.WriteLine("BEGIN");

                    objReader.WriteLine("\tSELECT TOP 1 ");
                    for (int i = 0; i < lstColumns.Count; i++)
                    {
                        string nameColumn = lstColumns[i].Name.Trim();
                        string comma      = (i == lstColumns.Count - 1) ? "" : ",";
                        objReader.WriteLine($"\t{nameColumn}{comma}");
                    }


                    objReader.WriteLine($"\tFROM {nameTable} WITH(NOLOCK)");

                    if (objPrimaryKey != null)
                    {
                        string nameColumnPK = objPrimaryKey.Name.Trim();
                        objReader.WriteLine($"\tWHERE {nameColumnPK} < @{nameColumnPK}");
                        objReader.WriteLine($"\tORDER BY  {nameColumnPK} DESC");
                    }

                    objReader.WriteLine("END");
                    objReader.WriteLine("GO");
                    objReader.WriteLine("");

                    #endregion

                    #region Next

                    objReader.WriteLine($"CREATE PROCEDURE Sp{nameTable}Siguiente");

                    if (objPrimaryKey != null)
                    {
                        string nameColumnPK = objPrimaryKey.Name.Trim();
                        string typeColumnPK = objPrimaryKey.Type.Name.ToUpper();
                        objReader.WriteLine($"@{nameColumnPK} AS {typeColumnPK}");
                    }

                    objReader.WriteLine("AS");
                    objReader.WriteLine("BEGIN");

                    objReader.WriteLine("\tSELECT TOP 1 ");
                    for (int i = 0; i < lstColumns.Count; i++)
                    {
                        string nameColumn = lstColumns[i].Name.Trim();
                        string comma      = (i == lstColumns.Count - 1) ? "" : ",";
                        objReader.WriteLine($"\t{nameColumn}{comma}");
                    }


                    objReader.WriteLine($"\tFROM {nameTable} WITH(NOLOCK)");

                    if (objPrimaryKey != null)
                    {
                        string nameColumnPK = objPrimaryKey.Name.Trim();
                        objReader.WriteLine($"\tWHERE {nameColumnPK} > @{nameColumnPK}");
                        objReader.WriteLine($"\tORDER BY  {nameColumnPK} ASC");
                    }

                    objReader.WriteLine("END");
                    objReader.WriteLine("GO");
                    objReader.WriteLine("");

                    #endregion

                    objReader.Close();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }