0-) companentler txtId,txtAd,txtYas,gridcontrol1,
1-) Dapper - NuGet Managerdan dapper yükle
2-) System.Configuration .dll sini referancesdan ekle
3-) app.config aşağıdaki gibi olsun
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2"/></startup>
<connectionStrings>
<add name="myConnectionString" connectionString="Server=.;Database=deneme;User ID=sa;Password=123;" providerName="System.Data.SqlClient"/>
</connectionStrings>
</configuration>
4-) modelim veri tabanındaki ile aynı ogrenci.cs
public class ogrenci
{
public int ogrenci_id { get; set; }
public string ogrenci_ad { get; set; }
public string ogrenci_yas { get; set; }
}
5-) class to Datatable metodu
public static DataTable CreateDataTable<T>(IEnumerable<T> list)
{
Type type = typeof(T);
var properties = type.GetProperties();
DataTable dataTable = new DataTable();
foreach (PropertyInfo info in properties)
{
dataTable.Columns.Add(new DataColumn(info.Name, Nullable.GetUnderlyingType(info.PropertyType) ?? info.PropertyType));
}
foreach (T entity in list)
{
object[] values = new object[properties.Length];
for (int i = 0; i < properties.Length; i++)
{
values[i] = properties[i].GetValue(entity);
}
dataTable.Rows.Add(values);
}
return dataTable;
}
6-) Select çekme ve datatableye atma
public void Listele()
{
string asd = ConfigurationManager.ConnectionStrings["myConnectionString"].ToString();
using (var con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ToString()))
{
IEnumerable<ogrenci> ogrenci_nesne = con.Query<ogrenci>("select * from ogrenci");
DataTable dt = CreateDataTable(ogrenci_nesne);
gridControl1.DataSource = dt;
}
}
7-) insert yapma
public void Kaydet()
{
string connectionString = ConfigurationManager.ConnectionStrings["myConnectionString"].ToString();
using (var con = new SqlConnection(connectionString))
{
con.Execute("insert ogrenci(ogrenci_ad,ogrenci_yas) values(@ogrenci_ad,@ogrenci_yas)", new { ogrenci_ad = txtAd.Text, ogrenci_yas = txtYas.Text });
}
}
8-) update
public void Guncelle()
{
string connectionString = ConfigurationManager.ConnectionStrings["myConnectionString"].ToString();
using (var con = new SqlConnection(connectionString))
{
con.Execute("update ogrenci set ogrenci_ad = @ogrenci_ad,ogrenci_yas=@ogrenci_yas where ogrenci_id = @ogrenci_id", new { ogrenci_ad = txtAd.Text, ogrenci_yas = txtYas.Text, ogrenci_id = txtId.Text });
}
}
9-) delete
public void Sil()
{
string connectionString = ConfigurationManager.ConnectionStrings["myConnectionString"].ToString();
using (var con = new SqlConnection(connectionString))
{
con.Execute("delete from ogrenci where ogrenci_id = @ogrenci_id", new { ogrenci_id = txtId.Text });
}
}
10-) arama ve bulduğunu datatableye atma
public void Ara()
{
string connectionString = ConfigurationManager.ConnectionStrings["myConnectionString"].ToString();
using (var con = new SqlConnection(connectionString))
{
string sql = "SELECT * FROM ogrenci WHERE ogrenci_id IN @ogrenci_id";
string[] aranacaklar = txtId.Text.Split(',');
IEnumerable<ogrenci> ogrenci_nesne = con.Query<ogrenci>(sql, new { ogrenci_id = aranacaklar }); // 1, 2, 3, 4, 5
DataTable dt = CreateDataTable(ogrenci_nesne);
gridControl1.DataSource = dt;
}
}
11-) Dapper - Procedure çalışma sonradan eklendi
using (var connec = new SqlConnection(db.conRmosBaglanti))
{
var p = new DynamicParameters();
p.Add("@voucher", varmi);
connec.Execute("RHModifiedTemizleKendi", p, commandType: CommandType.StoredProcedure);
}
11-) Dapper - SQL to Class
declare @TableName sysname = 'Tablo ismini buraya yaz'
declare @Result varchar(max) = 'public class ' + @TableName + '
{'
select @Result = @Result + '
public ' + ColumnType + NullableSign + ' ' + ColumnName + ' { get; set; }
'
from
(
select
replace(col.name, ' ', '_') ColumnName,
column_id ColumnId,
case typ.name
when 'bigint' then 'long'
when 'binary' then 'byte[]'
when 'bit' then 'bool'
when 'char' then 'string'
when 'date' then 'DateTime'
when 'datetime' then 'DateTime'
when 'datetime2' then 'DateTime'
when 'datetimeoffset' then 'DateTimeOffset'
when 'decimal' then 'decimal'
when 'float' then 'float'
when 'image' then 'byte[]'
when 'int' then 'int'
when 'money' then 'decimal'
when 'nchar' then 'string'
when 'ntext' then 'string'
when 'numeric' then 'decimal'
when 'nvarchar' then 'string'
when 'real' then 'double'
when 'smalldatetime' then 'DateTime'
when 'smallint' then 'short'
when 'smallmoney' then 'decimal'
when 'text' then 'string'
when 'time' then 'TimeSpan'
when 'timestamp' then 'DateTime'
when 'tinyint' then 'byte'
when 'uniqueidentifier' then 'Guid'
when 'varbinary' then 'byte[]'
when 'varchar' then 'string'
else 'UNKNOWN_' + typ.name
end ColumnType,
case
when col.is_nullable = 1 and typ.name in ('bigint', 'bit', 'date', 'datetime', 'datetime2', 'datetimeoffset', 'decimal', 'float', 'int', 'money', 'numeric', 'real', 'smalldatetime', 'smallint', 'smallmoney', 'time', 'tinyint', 'uniqueidentifier')
then '?'
else ''
end NullableSign
from sys.columns col
join sys.types typ on
col.system_type_id = typ.system_type_id AND col.user_type_id = typ.user_type_id
where object_id = object_id(@TableName)
) t
order by ColumnId
set @Result = @Result + '
}'
print @Result