🚀 C# RMOS - Linq Genel bilgiler ve basit kullanımları

Ramazan HABER / C# RMOS / Linq Genel bilgiler ve basit kullanımları

1-) C# RMOS - Linq Genel bilgiler ve basit kullanımları

* LİNQ COLUMN ADI DEĞİŞTİRMEK LİNQ COLUMN AS KULLANIMI

select new { YENİCOLUMNADI = GERÇEKCOLUMNADI }

* sorgu query to model

string query = "select * from Pos_Kodlar where Pkod_Sinif='11' and Pkod_Ozelkod<>'4'";

List<PosKodlarModel> posKodlarModels = db.Database.SqlQuery<PosKodlarModel>(query).ToList();

* tek column seçme

string text= subes.Where(x=>x.id==Convert.ToInt32(e.Value.ToString())).Select(x=>x.ad).FirstOrDefault();

*LİNQ İLE GENEL EKLE SİL GÜNCELLE LİSTELE BURASI YETER :)

using DevExpress.XtraGrid;

using DevExpress.XtraGrid.Views.Grid;

using System;

using System.Data;

using System.IO;

using System.Linq;

using System.Windows.Forms;

namespace GenelOtomasyon

{

public partial class Ayarlar : Form

{

public Ayarlar()

{

InitializeComponent();

}

GenelOtomasyonEntities dbgenel = new GenelOtomasyonEntities();

private void Ayarlar_Load(object sender, EventArgs e)

{

kullaniciListele();

txtKullaniciSube.Properties.DataSource = (from x in dbgenel.sube select x).ToList();

txtKullaniciSube.Properties.DisplayMember = "sube_ad";

txtKullaniciSube.Properties.ValueMember = "sube_id";

txtKullaniciSube.Properties.PopulateColumns();

txtKullaniciSube.Properties.Columns["sube_ad"].Caption = "Şube Ad";

txtKullaniciSube.Properties.Columns["sube_id"].Visible = false;

txtKullaniciSube.Properties.Columns["sube_sirket_id"].Visible = false;

}

public void kullaniciListele()

{

var kullanici = (from x in dbgenel.kullanici

join y in dbgenel.sube on x.kullanici_sube_id equals y.sube_id

select new { x.kullanici_ad, x.kullanici_soyad, x.kullanici_sube_id, y.sube_ad, x.kullanici_id, x.kullanici_dogumTarih, x.kullanici_sifre, x.kullanici_kullaniciAd }).ToList();

//var kullanici = dbgenel.Database.SqlQuery<kullanici>("select * from kullanici left join sube on sube_id=kullanici_sube_id").ToList();

gridControl_kullanici.DataSource = kullanici;

gridviewCountYaz(gridView_kullanici, "kullanici_ad");

gridviewSumYaz(gridView_kullanici, "kullanici_sube_id");

}

private void btnKullaniciEkle_Click(object sender, EventArgs e)

{

kullanici kullanici = new kullanici();

kullanici.kullanici_ad = txtKullaniciAd.Text;

kullanici.kullanici_soyad = txtKullaniciSoyad.Text;

kullanici.kullanici_dogumTarih = txtKullaniciDogumTar.DateTime;

kullanici.kullanici_kullaniciAd = txtKullaniciKullaniciAd.Text;

kullanici.kullanici_sifre = txtKullaniciSifre.Text;

kullanici.kullanici_sube_id = Convert.ToInt32(txtKullaniciSube.EditValue);

dbgenel.kullanici.Add(kullanici);

dbgenel.SaveChanges();

kullaniciListele();

}

private void btnKullaniciSil_Click(object sender, EventArgs e)

{

int seciliSatirIndex = gridView_kullanici.FocusedRowHandle;

if (seciliSatirIndex > -1)

{

int k_id = Convert.ToInt32(gridView_kullanici.GetFocusedRowCellValue("kullanici_id").ToString());

//var a = (from x in dbgenel.kullanici where x.kullanici_id == k_id select x).FirstOrDefault();

//dbgenel.kullanici.Remove(a);

//dbgenel.SaveChanges();

int noOfRowDeleted = dbgenel.Database.ExecuteSqlCommand("delete from kullanici where kullanici_id="+ k_id);

kullaniciListele();

}

else

{

MessageBox.Show("Lütfen Satır Seçiniz!");

}

private void btnKullaniciGuncelle_Click(object sender, EventArgs e)

{

int seciliSatirIndex = gridView_kullanici.FocusedRowHandle;

if (seciliSatirIndex > -1)

{

int k_id = Convert.ToInt32(gridView_kullanici.GetFocusedRowCellValue("kullanici_id").ToString());

var a = (from x in dbgenel.kullanici where x.kullanici_id == k_id select x).FirstOrDefault();

a.kullanici_ad = txtKullaniciAd.Text;

a.kullanici_soyad = txtKullaniciSoyad.Text;

a.kullanici_dogumTarih = txtKullaniciDogumTar.DateTime;

a.kullanici_kullaniciAd = txtKullaniciKullaniciAd.Text;

a.kullanici_sifre = txtKullaniciSifre.Text;

a.kullanici_sube_id = Convert.ToInt32(txtKullaniciSube.EditValue);

dbgenel.Entry(a).State = System.Data.Entity.EntityState.Modified;

dbgenel.SaveChanges();

kullaniciListele();

gridView_kullanici.FocusedRowHandle = seciliSatirIndex;

}

else

{

MessageBox.Show("Lütfen Satır Seçiniz!");

}

private void btnKullaniciListele_Click(object sender, EventArgs e)

{

kullaniciListele();

}

private void btnKullaniciYazdir_Click(object sender, EventArgs e)

{

yazdir(gridControl_kullanici);

}

public static void gridviewCountYaz(GridView grid, string fieldName)

{

if (grid.Columns.Count > 0)

{

grid.OptionsView.ShowFooter = true;

grid.Columns[fieldName].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Count;

grid.Columns[fieldName].SummaryItem.FieldName = fieldName;

grid.Columns[fieldName].SummaryItem.DisplayFormat = "{0:n0}";

}

public static void gridviewSumYaz(GridView grid, string fieldName)

{

if (grid.Columns.Count > 0)

{

grid.OptionsView.ShowFooter = true;

grid.Columns[fieldName].SummaryItem.SummaryType = DevExpress.Data.SummaryItemType.Sum;

grid.Columns[fieldName].SummaryItem.FieldName = fieldName;

grid.Columns[fieldName].SummaryItem.DisplayFormat = "Toplam = {0:n0}";

}

public void yazdir(GridControl gridControl)

{

using (SaveFileDialog saveDialog = new SaveFileDialog())

{

if (saveDialog.ShowDialog() != DialogResult.Cancel)

{

string exportFilePath = saveDialog.FileName;

string fileExtenstion = new FileInfo(exportFilePath).Extension;

switch (fileExtenstion)

{

case ".xls":

gridControl.ExportToXls(exportFilePath);

break;

case ".xlsx":

gridControl.ExportToXlsx(exportFilePath);

break;

case ".rtf":

gridControl.ExportToRtf(exportFilePath);

break;

case ".pdf":

gridControl.ExportToPdf(exportFilePath);

break;

case ".html":

gridControl.ExportToHtml(exportFilePath);

break;

case ".mht":

gridControl.ExportToMht(exportFilePath);

break;

default:

break;

}

if (File.Exists(exportFilePath))

{

try

{

//Try to open the file and let windows decide how to open it.

var p = new Process();

p.StartInfo = new ProcessStartInfo(exportFilePath)

{

UseShellExecute = true

};

p.Start();

}

catch

{

String msg = "The file could not be opened." + Environment.NewLine + Environment.NewLine + "Path: " + exportFilePath;

MessageBox.Show(msg, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

else

{

String msg = "The file could not be saved." + Environment.NewLine + Environment.NewLine + "Path: " + exportFilePath;

MessageBox.Show(msg, "Error!", MessageBoxButtons.OK, MessageBoxIcon.Error);

}

private void gridView_kullanici_FocusedRowChanged(object sender, DevExpress.XtraGrid.Views.Base.FocusedRowChangedEventArgs e)

{

int index = gridView_kullanici.FocusedRowHandle;

if (index > -1)

{

txtKullaniciAd.Text = gridView_kullanici.GetFocusedRowCellValue("kullanici_ad").ToString();

var soyad = gridView_kullanici.GetFocusedRowCellValue("kullanici_soyad");

if (soyad == null) soyad = "";

txtKullaniciSoyad.Text = soyad.ToString();

txtKullaniciDogumTar.EditValue = Convert.ToDateTime(gridView_kullanici.GetFocusedRowCellValue("kullanici_dogumTarih").ToString());

txtKullaniciKullaniciAd.Text = gridView_kullanici.GetFocusedRowCellValue("kullanici_kullaniciAd").ToString();

txtKullaniciSifre.Text = gridView_kullanici.GetFocusedRowCellValue("kullanici_sifre").ToString();

txtKullaniciSube.EditValue = Convert.ToInt32(gridView_kullanici.GetFocusedRowCellValue("kullanici_sube_id").ToString());

}

ÖNEMLİ BİR SORGU OLABİLİR

var sorgu = (from x in dbgenel.kullanici where x.kullanici_kullaniciAd == txtKullaniciAd.Text && x.kullanici_sifre == txtKullaniciSifre.Text select x).ToList();

if (sorgu.Any())

{

List<kullanici> k1 = ((List<kullanici>)sorgu);

kullanici k2 = k1.SingleOrDefault();

where kullanımı

foreach (var item in modelAnexList) {

List<rezAcentex> tekrar = modelAnexList.Where(v => v.rez_voucher == item.rez_voucher).ToList(); }

string rezTurkceKullan = ayarlar_ModelList.Where(v => v.ayarlar_key.Equals("rezTurkceKullan")).Select(v=>v.ayarlar_value).FirstOrDefault();

GroupBy kullanımı

var grp = modelAnexList.GroupBy(v => v.rez_voucher).ToList();

DİĞER

var kullanici = (from x in dbgenel.kullanici

join y in dbgenel.sube on x.kullanici_sube_id equals y.sube_id

select new { kullanici = x, sube= y });

JOİN VE LİSTELEME

var kullanici = (from x in dbgenel.kullanici

join y in dbgenel.sube on x.kullanici_sube_id equals y.sube_id

select new { x.kullanici_ad, x.kullanici_soyad, x.kullanici_sube_id, y.sube_ad }).ToList();

gridControl_kullanici.DataSource = kullanici;

kaynak : https://docs.microsoft.com/tr-tr/ef/core/querying/raw-sql

string studentName = ctx.Database.SqlQuery<string>("Select studentname from Student where studentid=1").FirstOrDefault();

var blogs = context.Blogs .FromSqlRaw("SELECT * FROM dbo.Blogs") .ToList();

int noOfRowUpdated = ctx.Database.ExecuteSqlCommand("Update student set studentname ='changed student by command' where studentid=1");

int noOfRowInserted = ctx.Database.ExecuteSqlCommand("insert into student(studentname) values('New Student')");

int noOfRowDeleted = ctx.Database.ExecuteSqlCommand("delete from student where studentid=1");

string studentName = ctx.Database.SqlQuery<string>("Select studentname from Student where studentid=@id", new SqlParameter("@id", 1)).FirstOrDefault();

2-) DATATABLE İLE KULLANIMI

DataTable dt = new DataTable();

dt.Columns.Add("tr");

dt.Columns.Add("en");

dt.Columns.Add("de");

dt.Columns.Add("ru");

DataRow dr = dt.NewRow();

dr["tr"] = "okul-tr";

dr["en"] = "okul-en";

dr["de"] = "okul-de";

dr["ru"] = "okul-ru";

dt.Rows.Add(dr);

dr = dt.NewRow();

dr["tr"] = "okul-tr1";

dr["en"] = "okul-en1";

dr["de"] = "okul-de1";

dr["ru"] = "okul-ru1";

dt.Rows.Add(dr);

DataRow sonuc = dt.AsEnumerable().Where(myRow => myRow.Field<string>("tr") == "okul-tr").FirstOrDefault();

/* yukarıdaki kod açıklaması :

datatable içinde tr sütününda "okul-tr" var is o satırı getir demek.

birden fazla dönerse ilk satırı alır . eğer yoksa null döner

SONRADAN TESPİT EDİLDİKİ BU KOD YAVAŞ ÇALIŞIYOR ONUN YERİNE SELECT KULLAN

AŞAĞIDA YAZIYOR

if (sonuc != null)

{

Console.WriteLine(sonuc["tr"].ToString());

Console.WriteLine(sonuc["en"].ToString());

Console.WriteLine(sonuc["ru"].ToString());

Console.WriteLine(sonuc["de"].ToString());

}

//DİĞER

DataRow[] result = dtClass.dtDilx.Select("Dil_Turkce='" + lbl.Text + "'");

foreach (DataRow row in result)

{

lbl.Text = row["Dil_Karsilik"].ToString();

}

2023 Şubat 11 Cumartesi
768