Dapper.md

1. Create the Windows Form project
2. Open M$ SQL Studio Manegement
3. Login as User Auth
4. Right click on databases -> New Database...
5. Name It acordinly with your project and Ok
6. Click in <your_database_name> and open Tables
7. Right click on 'Tables' -> New -> Table
8. Name it acordinly, but at least ensure that:

• Has a integer column ID (and with Right Click -> Set as Primary Key, set as PK)
• Has name varchar(50) or at least something similar
• Has set ID in the Table Properties as 'Identity Column'
• Other String properties

IMPORTANT: Remenber to select the ID row, and extend 'Column Properties' above the code and go in 'Identity Specification' open it and in 'Is Identity' select 'Yes'

9. Use CTRL+S, or Click on Diskete on top menu to Save, save it with proper naming

Stored Procedures

10. Criar as procedures no SQL Studio (CTRLL+N)

Add

CREATE PROC ContactAddOrEdit
@ContactID int,
@Name varchar(50),
@Phone varchar(50),
@Address varchar(250)
AS
BEGIN
  IF(@ContactID = 0)
BEGIN
  INSERT INTO Contact
  (
	Name,
	Phone,
	Address
  ) 
  VALUES
  (
  @Name,
  @Phone,
  @Address
  )
END
ELSE
BEGIN
  UPDATE Contact
  SET
  Name = @Name,
  Phone = @Phone,
  Address = @Address
  WHERE ContactID = @ContactID
END
END

GetAll/Search

CREATE PROC ContactViewAllOrSearch
@SearchText varchar(50)
AS BEGIN
	SELECT *
	FROM Contact
	WHERE @SearchText='' OR NAME LIKE '%'+@SearchText+'%'
END

Delete by ID

CREATE PROC ContactDeleteByID
@ContactID int
AS BEGIN
	DELETE FROM Contact
	WHERE ContactID = @ContactID
END

Form

11. Design the form at least with:

• Buttons: Save, Delete and Cancel
• TextFields and Label to Capture and exibhit the Objects Data
• One TextField with a Button for search
• One DataGrid to show all captured objects list

Events

12. Click on the Bolt in Properties of the Save button
13. Import 'System.Data.SqlClient' and create a Object of SqlConnection
14. Pass the Connection String in the constructor of the class

using System.Data.SqlClient;
// ...
SqlConnection sqlConn = new SqlConnection(@"Data Source=DESKTOP-49QPU82;Initial Catalog=DapperTest;Integrated Security=True");
// ...
private void BtnSave_Click(object sender, EventArgs e)
{
	if (sqlConn.State == ConnectionState.Closed)
                sqlConn.Open();
}

15. Add Dapper via NuGET
16. Add the import of Dapper

using Dapper;

17. Create an object of DynamicParameters called 'params'
18. Use the 'params' method .Add( String, prop_type ) to pass the object propertyes defines with '@' in SQL

using Dapper;
// ...
int ContactID = 0;
// ...
DynamicParameters param = new DynamicParameters();
param.Add("@ContactID", ContactID);
param.Add("@Name", txtName.Text.Trim());
param.Add("@Phone", txtName.Text.Trim());
param.Add("@Address", txtName.Text.Trim());

19. Add a connection execution method on final of the click method

• Pass as string the Stored Procedure name as 1st arg
• Pass the params (DynamicParameters) as 2nd arg
• Pass the Enum CommandType of the type of execution as 3rd (StoredProcedure in this case)

sqlConn.Execute("ContactAddOrEdit", param, commandType: CommandType.StoredProcedure);

20. Select all and do a Ricght Click -> Snippet -> Surrond with... -> try
21. On catch an exception use 'MessageBox.Show(e.Message);' to show the user the error
22. then add an finally and put an conection .Close() inside

---

23. Crie uma classe que irá receber os dados recebidos do BD

class Contact
        {
            public int ContactID { get; set; }
            public string Name { get; set; }
            public string Phone { get; set; }
            public string Address { get; set; }
        }

24. Crie um método de Form FillDataGrid que irá mostrar os Objetos no DataGrid

void FillDataGridView()
{
            DynamicParameters param = new DynamicParameters();
            param.Add("@SearchText", txtSearch.Text.Trim());
            List<Contact> list = sqlCon.Query<Contact>("ContactViewAllOrSearch", param, commandType: CommandType.StoredProcedure)
                .ToList<Contact>();
}

25. Then we'll set the list as Data Source to the DataGridView and the first columns returned as false

dataView.DataSource = list;
dataView.Columns[0].Visible = false;