Showing posts with label Context.Database execute SP. Show all posts
Showing posts with label Context.Database execute SP. Show all posts

Sunday, October 31, 2021

use ADO.NET via Context.Database

The DbContext.Database property allows us to perform ADO.NET as sown below. 

 //Execute Reader

    using (var ctx= new DatabaseContext())
    using (var cmd= ctx.Database.GetDbConnection().CreateCommand())
    {
        cmd.CommandText = "SELECT * From Table1";
        cmd.Database.OpenConnection();
        using (var result = command.ExecuteReader())
        {
            // do something with result
        }
    } 


//Execute SP


using (var ctx= new DatabaseContext())
    using (var cmd= ctx.Database.GetDbConnection().CreateCommand())
    {
               cmd.CommandText = "dbo.someSP"
                _dataContext.Connection.Open()
                cmd.CommandType = CommandType.StoredProcedure

                Dim param As DbParameter = command.CreateParameter()
                param.DbType = DbType.Int16
                param.ParameterName = "@param1"
                param.Value = 'value1"
                cmd.Parameters.Add(param)

                param = cmd.CreateParameter()
                param.DbType = DbType.DateTime
                param.ParameterName = "@param2"
                param.Value = "value2"
                cmd.Parameters.Add(param)

                cmd.ExecuteNonQuery()
                _dataContext.Connection.Close()
        }
    } 


Cheers,

Samita