Stored procedure in LinQ c #

Question

Stored procedure in LinQ c #

Navigation

#1 by (1 votes)

2

Hello developers good day, I am with a problem it turns out that my project does not have to have any stored procedure in SQLServer and they asked me to do everything from linQ the issue is that I have not found how to create the stored procedure in the code just like calling the procedure from it (which I do not use because I was forbidden to occupy SP in SQL Server ).

here I have the SP code that I made in the Sql obviously already delete it from it and I want to know how to create this same code in C# using LinQ

Create Procedure [dbo].[sp_i_programa_semanal] 
(@BarNom varchar(50),
@Fecha datetime,
@h1 numeric (10, 4),
@h2 numeric (10, 4),
@h3 numeric (10, 4),
@h4 numeric (10, 4),
@h5 numeric (10, 4),
@h6 numeric (10, 4),
@h7 numeric (10, 4),
@h8 numeric (10, 4),
@h9 numeric (10, 4),
@h10 numeric (10, 4),
@h11 numeric (10, 4),
@h12 numeric (10, 4),
@h13 numeric (10, 4),
@h14 numeric (10, 4),
@h15 numeric (10, 4),
@h16 numeric (10, 4),
@h17 numeric (10, 4),
@h18 numeric (10, 4),
@h19 numeric (10, 4),
@h20 numeric (10, 4),
@h21 numeric (10, 4),
@h22 numeric (10, 4),
@h23 numeric (10, 4),
@h24 numeric (10, 4))

    As
    Begin
    Set nocount on

    Declare @codigo_barra int
    Declare @count_reg int
    --select * from tb_programa_semanal
    --sp_help tb_programa_semanal


    select @codigo_barra = codigo_barra
    from  tb_barra 
    where descripcion_barra = @BarNom

    select @count_reg = count(*) 
    from tb_programa_semanal 
    where codigo_barra = @codigo_barra
    and Fecha = @Fecha
    if (@count_reg = 0)
    Begin
            insert into tb_programa_semanal (Codigo_barra,Fecha,FP_h1,FP_h2,FP_h3,FP_h4,FP_h5,FP_h6,
                                            FP_h7,FP_h8,FP_h9,FP_h10,FP_h11,FP_h12,FP_h13,FP_h14,FP_h15,
                                            FP_h16,FP_h17,FP_h18,FP_h19,FP_h20,FP_h21,FP_h22,FP_h23,FP_h24)
            values (@codigo_barra,@Fecha,@h1,@h2,@h3,@h4,@h5,@h6,@h7,@h8,@h9,@h10,@h11,@h12,@h13,@h14,
                    @h15,@h16,@h17,@h18,@h19,@h20,@h21,@h22,@h23,@h24)
    End --fin if (@count_reg = 0)
    Else 
    Begin
        update tb_programa_semanal
        set FP_h1 = @h1,
            FP_h2 = @h2,
            FP_h3 = @h3,
            FP_h4 = @h4,
            FP_h5 = @h5,
            FP_h6 = @h6,
            FP_h7 = @h7,
            FP_h8 = @h8,
            FP_h9 = @h9,
            FP_h10 = @h10,
            FP_h11 = @h11,
            FP_h12 = @h12,
            FP_h13 = @h13,
            FP_h14 = @h14,
            FP_h15 = @h15,
            FP_h16 = @h16,
            FP_h17 = @h17,
            FP_h18 = @h18,
            FP_h19 = @h19,
            FP_h20 = @h20,
            FP_h21 = @h21,
            FP_h22 = @h22,
            FP_h23 = @h23,
            FP_h24 = @h24
        where codigo_barra = @codigo_barra
        and Fecha = @Fecha
    End --fin else



End --fin Create


GO

If exists (Select 1 from sysobjects where name = 'sp_i_programa_semanal')
Begin
  Select ' -- SP: sp_i_programa_semanal creado >>'
End

One thing that I need to add the data to fill that SP comes from an excel file that I already uploaded

c# asp linq stored-procedure

asked by Joel Baez 25.04.2018 в 18:02

source

1 answer

How do I install angle 4? Replace characters of a string in python using Lists

score 1 · Accepted Answer

Well, you have 2 options, use sql code from your code or use an ORM.

I do not recommend using sql code in your application, there are people who like it but I think it "dirties" the code.

If you want to use sql code anyway, I would use a micro-orm as a dapper, with it you can send sql code to the server and you can map the result of the query to objects of your classes.

The second option, the ORM, I like more. Almost all ORMs support CRUD functions (create, read, update, delete), and some allow you to use linq to create queries, such as Entity Framework.

The problem with ORMs is that they can be very messy depending on the ORM, configure them and all that. To avoid complications I use a library called Linq2db, it brings everything necessary to create linq queries, it supports raw functions, it does not require much configuration, and it is quite fast.

In Linq2db, you need to first create classes for each table you're going to work with, something like this:

using System;
using LinqToDB.Mapping;

[Table(Name = "Products")]
public class Product
{
    [PrimaryKey, Identity]
    public int ProductID { get; set; }

    [Column(Name = "ProductName"), NotNull]
    public string Name { get; set; }

    // ... agregas una propiedad para cada columna...
 }

Since you created all the classes of each table, you create a class that inherits from DataConnection, this class is the one that allows the connection to the bdd and that allows you to create queries and use methods to add, modify and delete records. There you declare each table using the clades that you created previously, something like this:

public class DbNorthwind : LinqToDB.Data.DataConnection
{
    public DbNorthwind() : base("Northwind") { }

    public ITable<Product> Product => GetTable<Product>();
     public ITable<Category> Category => GetTable<Category>();

     // ... le sigues con cada tabla...
}

It has several constructors, in some you can directly pass the connection string, or, as in the example that I put, you can pass the configuration string saved in your app.config. It's something you play a little.

Finally, to use all this, for example to make a query with linq:

using (var db = new DbNorthwind())
{
     var query = from p in db.Product
                         where p.ProductID > 25
                         orderby p.Name descending
                         select p;

     return query.ToList();

}

Or to update records:

using (var db = new DbNorthwind())
{
    db.Product
       .Where(p => p.ProductID == product.ProductID)
       .Set(p => p.Name, product.Name)
       .Set(p => p.UnitPrice, product.UnitPrice)
      .Update();
}

What Linq2db does is convert the linq expression into an sql query, send it to the server, the server returns the result, and linq2db maps the result to your classes. You can use joins and various sql things from linq.

If you are interested, take a look: github.com/linq2db/linq2db

EDIT: There is also a way to create all the classes and the DataConection automatically, in case your bdd has many tables, this is the project:

link