C# First dapper App

Question 1

Get connectionString from app.config

Answer 1

ConfigurationManager.ConnectionStrings[name].ConnectionString

(In the static helper class using a static method)

Question 2

Add connection string in config.app

Answer 2

In configuration

< add name=“sampledb” connectionstring=“server=.;database=sample;trusted_connection=true;” ProviderName= “system.data.sqlClient”>

Question 3

Step to have a simple c# app connected to sql with windows form

Answer 3

Define class
Add helper to get connection string
Define connectionstring in config.app
Define ui
Add dataaccess class
Use an instance of dataaccess to get the result (usually db)
Add dapper reference in DataAccess class

Question 4

How to not make crash a function that you havent implement yet

Answer 4

Add in the code:
Throw new NotaimplementedException();

Question 5

Connect a list to a listbox

Answer 5

Mylistbox.DataSource=peopleList
MyListBox.DisplayMember=“myProperty”

With peopleList is a list.
No need to have a bindingsource anymore.
The property is in double quote, make sure to write it properly.

Question 6

Property that return a string that is a list of existing property using $ sign

Answer 6

Return $”{property1} {property2} ({property3})”

Question 7

Add dapper reference

Answer 7

Go to reference, right click, select manage nuget packages,browse dapper, can add using dapper at the top of your dataAccess class

Question 8

Using statement with dapper

Answer 8

Using (IDBConnection connection =system.data.sqlclient.sqlconnection(myConnectionString))
{

}

If you have an helper, instead of myConnectionString then
Helper.connval(myname);

Question 9

Dapper query with direct sql and result is return by a function

Answer 9

Return Connection.query($”select * from people where name=‘{myName}’ “).ToList();

With connection of type IDBConnection

Question 10

Dapper query with store procedure

Answer 10

Var output= connection.Query(“dbo.mystoreProcedure @myProcVariable”,new { myProcVariable=myfunctionName} )

We create a new dynamic class instance with {}

Question 11

Insert record with Dapper

Answer 11

Using line is the same. Need to create a list of element to insert even if only one.

connection.execute(“addperson @first name @lastname @email”, people)

People is the list of object:
List People=new List()
people.Add(new {FirstName=“John”,LastName=“Doe”};

Question 12

Create instance of a class and set property right away (no constructor)

Answer 12

Person myPerson = new Person {Firstname=“x”, LastName=“y”…};

Question 13

Search people button function

Answer 13

Private void SearchButtonClick(object sender, eventArgs e)
{
DataAccess db=new DataAccess();
People=db.GetPeople(seachtextbox.text);
}

Question 14

Dapper: select query with join person-cell Phone

Answer 14

String sql=@”select pe.,ph. from dbo.Person pe left join dbo.Phone ph on pe.CellPhoneId=ph.id;”;
Var people=conn.Query(sql,(person, phone)=> {person.CellPhone=phone; return person;});

Question 15

display person firstname, last name and phone from people

Answer 15

Foreach( var p in people)
{
Console.WriteLine($”{p.FirstName} {p.LastName} Cell: {p.CellPhone?.CellNumber}”);
}

Question 16

Dapper: select query with join person-cell Phone with parameter

Answer 16

var p=new { LastName=lastName}; 
String sql=@”select pe.*,ph.* from dbo.Person pe left join dbo.Phone ph on pe.CellPhoneId=ph.id where p.LastName=@LastName;”;
Var people=conn.Query(sql, (person, phone) => {person.CellPhone=phone; return person;},p);

Question 17

Dapper, multiple sql queries

Answer 17

String sql=”select * from dbo.Person; select * from dbo.Phone;”
List people = null;
List phones=null;
Using(var Lists=conn.QueryMultiple(sql))
{
people=Lists.read().ToList();
phones=Lists.Read().Tolist();
}

Question 18

Dapper, multiple sql queries with parameters: last name and partial phone number

Answer 18

String sql=”select * from dbo.Person where LastName=@LastName; select * from dbo.Phone where PhoneNumber like ‘%’ + PartialPhoneNumber + ‘%’ ;”
Var p = new
{
LastName=lastName;
PartialPhoneNumber=partialPhoneNumber;
}
List people = null;
List phones=null;
Using(var Lists=conn.QueryMultiple(sql,p))
{
people=Lists.read().ToList();
phones=Lists.Read().Tolist();
}

Question 19

Transaction: Insert person then try to update all id to 1.

Answer 19

String sql=$@ “INSERT INTO dbo.Person (FirstName,LastName) VALUES (@FirstName,@LastName);”;
Var p= new DynamicParameter();
p.Add(“@FirstName”,firstName);
p.Add(“@LastName”,lastName);
conn.Open();
using (var trans =conn.BeginTransaction())
{
Int recordUpdated=conn.execute(sql,p,trans);
Try { conn.Execute(“Update dbo.Person SET id=1”); trans.Commit;}
Catch (Exception ex) { Console.WriteLine($“Error :{ex.Message}“) ; trans.RollBack(); }
}

Question 20

Define DataTable (firstName, LastName) and put data in it

Answer 20

Private Static DataTable getTroopers()
{
Var Output=new DataTable();
Output.Columns.Add(“FirstName”,typeof(string));
Output.Columns.Add(“LastName”,typeof(string));
Output.Rows.Add(“John”,”Smith”);
Output.Rows.Add(“Lea”,”Doe”);
Return Output
}

Question 21

Using get troopers and UDT to insert multiple records

Answer 21

var troopers=getTroopers();
var p =new
{ people=troopers.AsTableValuedParameter(BasicUDT) };
Int recordAffected=conn.Execute(“dbo.PersonInsert,p,commandType: commandType.StoredProcedure);