Roslynpad - connect to SQL database add nuget package for EF, Dapper and Ado.Net

Question

How can i use Roslynpad to connect to SQL Server using Ado.Net, Dapper and Entity Framework?

Answer 1

This roslynpad snippet should help you to connect to your database.

Add package to roslynpad

You can add packages by typing the name in the textbox in the middle of a query and adding the using statement by hand

This is the result

#r "nuget: Microsoft.Data.SqlClient, 5.1.1"
using Microsoft.Data.SqlClient;

Change the connection string conStr and use your db-server, databasename, username and password.

string conStr =  @"Data Source=<dbSrv>;Initial Catalog=<dbname>;"; // only splitted 
conStr += conStr + "Integrated Security=False;"; // to 
conStr += conStr + "TrustServerCertificate=true;"; // avoid
conStr += conStr + "User id=<user>;Password=<pass>"; // a scrollbar

Ado.Net and Roslynpad

Then you can connect to your database

#r "nuget: Microsoft.Data.SqlClient, 5.1.1"
using Microsoft.Data.SqlClient;

string conStr = "<your-string-goes-here>";


var p = new List<Product>();  // list to store the results from the sql query
            
string sqlQuery =  "SELECT ID as Id, ProductName FROM dbo.Products;";
using (SqlConnection connection = new SqlConnection(conStr))
{
    SqlCommand command = new SqlCommand(sqlQuery, connection);
    connection.Open();
    using (SqlDataReader reader = command.ExecuteReader())
    {
       while (reader.Read())
       {              
         // add result row to list
         p.Add(new Product{Id= (int)reader[0] , ProductName =(string)reader[1]});
       }
    }

}
// show list content
p.Dump();


public class Product
{
    public int Id{ get; set; }
    public string ProductName { get; set; }        
};

Dapper inside Roslynpad

Using dapper to get records from sql-server can be done with this code

#r "nuget: Microsoft.Data.SqlClient, 5.1.1"
#r "nuget: System.Data.Common, 4.3.0"
#r "nuget: Dapper, 2.0.151"

using System.Data;
using Microsoft.Data.SqlClient;
using Dapper;

string conStr = "<yourConnectionString>";

string sqlQuery =  "SELECT ID as Id, ProductName FROM dbo.Products;";

Product product;
IEnumerable<Product> products;
using (IDbConnection  connection = new SqlConnection(conStr))
{
   products = connection.Query<Product>(sqlQuery);       
}
products.Dump();
product = products.FirstOrDefault<Product>();
product.Dump();

public class Product
{
    public int Id{ get; set; }
    public string ProductName { get; set; }        
};

Using Entity Framework in Roslynpad

This code below shows how to set up a connection to your database and create a mapping between your class-members and different colum-names in your table

The start of the Query

#r "nuget: Microsoft.Data.SqlClient, 5.1.1"
#r "nuget: Microsoft.EntityFrameworkCore, 7.0.11"
#r "nuget: Microsoft.EntityFrameworkCore.SqlServer, 7.0.11"

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.SqlServer;
using System;
using System.Collections.Generic;
using System.Diagnostics;
 
 static class Program {
    [STAThread]
    static void Main() {
      ApplicationDbContext context = new ApplicationDbContext();
      var models = context.Models.ToList();
      models.Dump();
    }
 }

And after Program.Main you have to add this code as well

Entity in Code

public sealed class CsModel
{    
    public decimal ModelId { get; set; }
    public string Name { get; set; }
    public double? Price { get; set; }
}

ApplicationDbContext class

public  class ApplicationDbContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        string cnn = @"<your-connection>";
        optionsBuilder.UseSqlServer(cnn);
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
         // ... see below
    }    
    //entities
    public DbSet<CsModel> Models { get; set; }    
}

Mapping inside OnModelCreating

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<CsModel>(entity =>
        {
            entity.HasKey(e => e.ModelId)
                .HasName("PK__CS_MODEL__1E");

            entity.ToTable("CS_MODEL", "dbo");

            entity.Property(e => e.ModelId)
                .HasColumnType("numeric(19, 0)")
                .ValueGeneratedOnAdd()
                .HasColumnName("MODEL_ID");

            entity.Property(e => e.Name)
                .HasMaxLength(255)
                .IsUnicode(false)
                .HasColumnName("NAME");

            entity.Property(e => e.Price).HasColumnName("PRICE");
        });