1-) C# RMOS - Dapper New Net Core 6 ve Masaüstü Basit Kullanımı 2
.net core api 6.0 github link : https://github.com/ramazanhaber/DapperApi
masaüstü github link : https://github.com/ramazanhaber/DapperNew
asenkron için incele : https://www.youtube.com/watch?v=n0zkkoL8eNs veya https://www.youtube.com/watch?v=wErQH7foJIY
bu daha iyi olabilir https://www.youtube.com/watch?v=MbZc19iWNn0 koları burada https://github.com/faisalcse1/dapper-example
// NuGet Managerdan dapper indir yetmezse Dapper.Contrib yükle
SQL BAĞLANTI HATASI ALIRSAN BUNU ÇALIŞTIR
private void EnsureConnectionOpen() { if (context.State == ConnectionState.Closed) { context.Open(); } |
1. ADIM Ogrenciler.cs CLASI OLUŞTUR
using System.ComponentModel.DataAnnotations.Schema; using System.ComponentModel.DataAnnotations;
namespace DapperApi { [Table("Ogrenciler")] public class Ogrenciler { [Key] public int id { get; set; } public string ad { get; set; } public int yas { get; set; } } }
|
2. ADIM DatabaseHelper.cs
using Dapper; using Newtonsoft.Json; using System.Data; namespace DapperApi.Helper { // CREATE BY RAMBO public class DatabaseHelper { private readonly IDbConnection _connection; public DatabaseHelper(IDbConnection connection) { _connection = connection; } public DataTable ExecuteQueryToDataTable(string query) { var reader = _connection.ExecuteReader(query); var resultTable = new DataTable(); resultTable.Load(reader); return resultTable; } public string ExecuteQueryToJson(string query) { var result = _connection.Query<dynamic>(query).ToList(); string jsonResult = JsonConvert.SerializeObject(result); return jsonResult; } public bool exec(string query) { try { _connection.Execute(query); return true; } catch (Exception ex) { return false; } } private string ConvertDataTableToJson(DataTable table) { return JsonConvert.SerializeObject(table); } } }
|
2. ADIM AsyncDatabaseHelper.cs
using Dapper; using Newtonsoft.Json; using System.Data; namespace DapperApi.Helper { public class AsyncDatabaseHelper { private readonly IDbConnection _connection; public AsyncDatabaseHelper(IDbConnection connection) { _connection = connection; } public async Task<DataTable> ExecuteQueryToDataTableAsync(string query) { using var reader = await _connection.ExecuteReaderAsync(query); var resultTable = new DataTable(); resultTable.Load(reader); return resultTable; } public async Task<string> ExecuteQueryToJsonAsync(string query) { var result = (await _connection.QueryAsync<dynamic>(query)).ToList(); string jsonResult = JsonConvert.SerializeObject(result); return jsonResult; } public async Task<bool> ExecAsync(string query) { try { await _connection.ExecuteAsync(query); return true; } catch (Exception ex) { // You might want to log the exception somewhere for further analysis. return false; } } private string ConvertDataTableToJson(DataTable table) { return JsonConvert.SerializeObject(table); } } }
|
3. ADIM KULLANIMI BasitOgrenciController.cs
using Dapper; using DapperApi.Helper; using Microsoft.AspNetCore.Mvc; using Microsoft.Data.SqlClient; using Microsoft.Extensions.Configuration; using System.Data; namespace DapperApi.Controllers { [Route("api/[controller]")] [ApiController] public class BasitOgrenciController : ControllerBase { private readonly IDbConnection _connection; private readonly DatabaseHelper _databaseHelper; private readonly IConfiguration _configuration;
public BasitOgrenciController(DatabaseConnections connections, IConfiguration configuration) { _connection = connections.DefaultConnection; // ilk veri tabanı _databaseHelper = new DatabaseHelper(connections.SecondConnection); // ikinci veri tabanı _configuration = configuration;
} [HttpPost] [Route("GetOgrenciler")] public ActionResult<IEnumerable<Ogrenciler>> GetOgrenciler() { var ogrenciler = _connection.Query<Ogrenciler>("SELECT * FROM Ogrenciler"); return Ok(ogrenciler); } [HttpPost] [Route("GetOgrenciById")] public ActionResult<Ogrenciler> GetOgrenciById(int id) { var ogrenci = _connection.QuerySingleOrDefault<Ogrenciler>("SELECT * FROM Ogrenciler WHERE id = @id", new { id = id }); if (ogrenci == null) { return NotFound(); } return Ok(ogrenci); } [HttpPost] [Route("PostOgrenci")] public ActionResult<Ogrenciler> PostOgrenci(Ogrenciler ogrenci) { string query = "INSERT INTO Ogrenciler (Ad, Yas) OUTPUT INSERTED.id VALUES (@Ad, @Yas)"; _connection.Execute(query, ogrenci); return Ok(); } [HttpPost] [Route("PostOgrenciDon")] public ActionResult<Ogrenciler> PostOgrenciDon(Ogrenciler ogrenci) { string query = "INSERT INTO Ogrenciler (Ad, Yas) OUTPUT INSERTED.id VALUES (@Ad, @Yas)"; int newId = _connection.ExecuteScalar<int>(query, ogrenci); ogrenci.id = newId; return CreatedAtAction(nameof(GetOgrenciById), new { id = ogrenci.id }, ogrenci); } [HttpPost] [Route("UpdateOgrenci")] public IActionResult UpdateOgrenci(Ogrenciler ogrenci) { string query = "UPDATE Ogrenciler SET Ad = @Ad, Yas = @Yas WHERE id = @id"; _connection.Execute(query, ogrenci); return Ok(); } [HttpPost] [Route("DeleteOgrenci")] public IActionResult DeleteOgrenci(int id) { string query = "DELETE FROM Ogrenciler WHERE id = @id"; _connection.Execute(query, new { id = id }); return Ok(); } [HttpPost] [Route("QueryToJsonveQueryToDataTableveExec")] public IActionResult QueryToJsonveQueryToDataTableveExec(string query) { string json = _databaseHelper.ExecuteQueryToJson(query); DataTable dataTable = _databaseHelper.ExecuteQueryToDataTable(query); bool sonuc = _databaseHelper.exec(query); return Ok(json); }
[HttpPost] [Route("dinamikconnection")] public IActionResult dinamikconnection(string query) { string connectionString = _configuration.GetConnectionString("DefaultConnection"); using IDbConnection dbConnection = new SqlConnection(connectionString); var ogrenciler = dbConnection.Query<Ogrenciler>("SELECT * FROM Ogrenciler"); return Ok(ogrenciler); } } }
|
3. ADIM KULLANIMI AsyncOgrenciController.cs
using DapperApi.Helper; using DapperApi.Models; using Microsoft.AspNetCore.Mvc; using Microsoft.Data.SqlClient; using System.Data; using Dapper; namespace DapperApi.Controllers { [Route("api/[controller]")] [ApiController] public class AsyncOgrenciController : ControllerBase { private readonly IDbConnection _connection; private readonly AsyncDatabaseHelper _databaseHelper; private readonly IConfiguration _configuration; public AsyncOgrenciController(DatabaseConnections connections, IConfiguration configuration) { _connection = connections.DefaultConnection; // ilk veri tabanı _databaseHelper = new AsyncDatabaseHelper(connections.SecondConnection); // ikinci veri tabanı _configuration = configuration; } [HttpPost] [Route("GetOgrenciler")] public async Task<ActionResult<IEnumerable<Ogrenciler>>> GetOgrenciler() { var ogrenciler = await _connection.QueryAsync<Ogrenciler>("SELECT * FROM Ogrenciler"); return Ok(ogrenciler); } [HttpPost] [Route("GetOgrencilerGenelModel")] public async Task<ActionResult<GenelModel>> GetOgrencilerGenelModel() { var ogrenciler = await _connection.QueryAsync<Ogrenciler>("SELECT * FROM Ogrenciler"); GenelModel genelModel = new GenelModel(); genelModel.Data = ogrenciler; return Ok(genelModel); } [HttpPost] [Route("GetOgrenciById")] public async Task<ActionResult<Ogrenciler>> GetOgrenciById(int id) { var ogrenci = await _connection.QuerySingleOrDefaultAsync<Ogrenciler>("SELECT * FROM Ogrenciler WHERE id = @id", new { id = id }); if (ogrenci == null) { return NotFound(); } return Ok(ogrenci); } [HttpPost] [Route("PostOgrenci")] public async Task<IActionResult> PostOgrenci(Ogrenciler ogrenci) { string query = "INSERT INTO Ogrenciler (Ad, Yas) OUTPUT INSERTED.id VALUES (@Ad, @Yas)"; await _connection.ExecuteAsync(query, ogrenci); return Ok(); } [HttpPost] [Route("PostOgrenciDon")] public async Task<ActionResult<Ogrenciler>> PostOgrenciDon(Ogrenciler ogrenci) { string query = "INSERT INTO Ogrenciler (Ad, Yas) OUTPUT INSERTED.id VALUES (@Ad, @Yas)"; int newId = await _connection.ExecuteScalarAsync<int>(query, ogrenci); ogrenci.id = newId; return CreatedAtAction(nameof(GetOgrenciById), new { id = ogrenci.id }, ogrenci); } [HttpPost] [Route("UpdateOgrenci")] public async Task<IActionResult> UpdateOgrenci(Ogrenciler ogrenci) { string query = "UPDATE Ogrenciler SET Ad = @Ad, Yas = @Yas WHERE id = @id"; await _connection.ExecuteAsync(query, ogrenci); return Ok(); } [HttpPost] [Route("DeleteOgrenci")] public async Task<IActionResult> DeleteOgrenci(int id) { string query = "DELETE FROM Ogrenciler WHERE id = @id"; await _connection.ExecuteAsync(query, new { id = id }); return Ok(); } [HttpPost] [Route("QueryToJsonveQueryToDataTableveExec")] public async Task<IActionResult> QueryToJsonveQueryToDataTableveExec(string query) { string json = await _databaseHelper.ExecuteQueryToJsonAsync(query); DataTable dataTable = await _databaseHelper.ExecuteQueryToDataTableAsync(query); bool sonuc = await _databaseHelper.ExecAsync(query); return Ok(json); } [HttpPost] [Route("dinamikconnection")] public async Task<IActionResult> dinamikconnection(string query) { string connectionString = _configuration.GetConnectionString("DefaultConnection"); using IDbConnection dbConnection = new SqlConnection(connectionString); var ogrenciler = await dbConnection.QueryAsync<Ogrenciler>("SELECT * FROM Ogrenciler"); return Ok(ogrenciler); } } }
|
4. ADIM KULLANIMI Program.cs
using DapperApi; using Microsoft.Data.SqlClient; using System.Data;
var builder = WebApplication.CreateBuilder(args);
builder.Services.AddCors(); builder.Services.AddControllers(); builder.Services.AddEndpointsApiExplorer(); builder.Services.AddSwaggerGen();
string defaultConnectionString = builder.Configuration.GetConnectionString("DefaultConnection"); IDbConnection defaultConnection = new SqlConnection(defaultConnectionString);
string secondConnectionString = builder.Configuration.GetConnectionString("SecondConnection"); IDbConnection secondConnection = new SqlConnection(secondConnectionString);
builder.Services.AddSingleton(new DatabaseConnections(defaultConnection, secondConnection));
var app = builder.Build(); app.UseCors(builder => builder .AllowAnyHeader() .AllowAnyMethod() .AllowAnyOrigin() );
if (app.Environment.IsDevelopment() || app.Environment.IsProduction()) { app.UseDeveloperExceptionPage(); app.UseSwagger(); app.UseSwaggerUI(c => { c.SwaggerEndpoint("/swagger/v1/swagger.json", "myapi v1"); }); } app.UseHttpsRedirection(); app.UseAuthorization(); app.MapControllers(); app.Run();
|
4.1. ADIM DatabaseConnections.cs
using System.Data; namespace DapperApi { public class DatabaseConnections { public IDbConnection DefaultConnection { get; } public IDbConnection SecondConnection { get; } public DatabaseConnections(IDbConnection defaultConnection, IDbConnection secondConnection) { DefaultConnection = defaultConnection; SecondConnection = secondConnection; } } }
|
5. ADIM appsettings.json
{ "Logging": { "LogLevel": { "Default": "Information", "Microsoft.AspNetCore": "Warning" } }, "ConnectionStrings": { "DefaultConnection": "Server=RAMBO3;Database=Deneme1;User ID=sa;Password=22;TrustServerCertificate=true;", "SecondConnection": "Server=RAMBO3;Database=Deneme1;User ID=sa;Password=11;TrustServerCertificate=true;" }, "AllowedHosts": "*" }
|
SON ADIM. MASA ÜSTÜ KULLANIMI
using Dapper; using System; using System.Data.SqlClient; using System.Windows.Forms; namespace DapperNew { public partial class Form1 : Form { // NuGet Managerdan dapper ve Dapper.Contrib yükle
public Form1() { InitializeComponent(); } public static string connectionString = @"Server=RAMBO3;Database=Deneme1;User ID=sa;Password=111;";
private void Form1_Load(object sender, EventArgs e) { listele(); } public void listele() { using (SqlConnection connection = new SqlConnection(connectionString)) { var liste = connection.Query<Ogrenciler>("SELECT * FROM Ogrenciler"); gridControl1.DataSource = liste; } }
private void btnEkle_Click(object sender, EventArgs e) { Ogrenciler ogrenci=new Ogrenciler(); ogrenci.ad = txtAd.Text; ogrenci.yas = Convert.ToInt32(txtYas.Text); using (SqlConnection connection = new SqlConnection(connectionString)) { string query = "INSERT INTO Ogrenciler (ad, yas) VALUES (@ad, @yas)"; connection.Execute(query, ogrenci); //connection.ExecuteAsync(query, ogrenci); // hız için kullanılabilir } listele(); } private void btnGuncelle_Click(object sender, EventArgs e) { if (gridView1.FocusedRowHandle < 0) return; Ogrenciler ogrenci = (Ogrenciler)gridView1.GetFocusedRow() as Ogrenciler;
ogrenci.ad = txtAd.Text; ogrenci.yas = Convert.ToInt32(txtYas.Text);
using (SqlConnection connection = new SqlConnection(connectionString)) { string query = "UPDATE Ogrenciler SET ad = @ad, yas = @yas WHERE id = @id"; connection.Execute(query, ogrenci); } listele(); } private void btnSil_Click(object sender, EventArgs e) { if (gridView1.FocusedRowHandle < 0) return;
Ogrenciler ogrenci = (Ogrenciler)gridView1.GetFocusedRow() as Ogrenciler;
using (SqlConnection connection = new SqlConnection(connectionString)) { string query = "DELETE FROM Ogrenciler WHERE id = @id"; connection.Execute(query, new { id = ogrenci.id }); }
listele(); } private void btnListele_Click(object sender, EventArgs e) { listele();
} } }
|