I think I don't have any issues with my code and I check thousand times if I am making a mistake opening or closing my connections. I can't figure it out please help me :/
If you find the issue please let me know what I need to do. Basically what I need to do is when the selected column changes in Datagrid I want to reach the id of that row and get all the data and put them on my form
Thanks and have a great day
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.OleDb;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace WindowsFormsApp111
{
public partial class Form1 : Form
{
OleDbConnection baglanti = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source= hasta_bilgisi.accdb");
DataTable veritablosu = new DataTable();
public Form1()
{
InitializeComponent();
}
private void button_cikis_Click(object sender, EventArgs e)
{
this.Close();
}
private void Form1_Load(object sender, EventArgs e)
{
veritablosu.Columns.Add("hasta_id");
veritablosu.Columns.Add("isim");
veritablosu.Columns.Add("d_tarihi");
veritablosu.Columns.Add("d_yeri");
veritablosu.Columns.Add("kan_grubu");
veritablosu.Columns.Add("cinsiyet");
veritablosu.Columns.Add("adres");
veritablosu.Columns.Add("tel");
baglanti.Open();
OleDbCommand komut = new OleDbCommand("select * from tbl_Hasta where isim Like 'A%'", baglanti);
OleDbDataReader okuyucu = komut.ExecuteReader();
DataRow satır = veritablosu.NewRow();
while (okuyucu.Read())
{
satır = veritablosu.NewRow();
satır[0] = okuyucu[0].ToString();
satır[1] = okuyucu[1].ToString();
satır[2] = okuyucu[2].ToString();
satır[3] = okuyucu[3].ToString();
satır[4] = okuyucu[4].ToString();
satır[5] = okuyucu[5].ToString();
satır[6] = okuyucu[6].ToString();
satır[7] = okuyucu[7].ToString();
veritablosu.Rows.Add(satır);
}
dataGridView1.DataSource = veritablosu;
baglanti.Close();
}
private void tabControl1_SelectedIndexChanged(object sender, EventArgs e)
{
veritablosu.Clear();
if (tabControl1.SelectedIndex == 0)
{
baglanti.Open();
OleDbCommand komut = new OleDbCommand("select * from tbl_Hasta where isim Like 'A%'", baglanti);
OleDbDataReader okuyucu = komut.ExecuteReader();
DataRow satır = veritablosu.NewRow();
while (okuyucu.Read())
{
satır = veritablosu.NewRow();
satır[0] = okuyucu[0].ToString();
satır[1] = okuyucu[1].ToString();
satır[2] = okuyucu[2].ToString();
satır[3] = okuyucu[3].ToString();
satır[4] = okuyucu[4].ToString();
satır[5] = okuyucu[5].ToString();
satır[6] = okuyucu[6].ToString();
satır[7] = okuyucu[7].ToString();
veritablosu.Rows.Add(satır);
}
dataGridView1.DataSource = veritablosu;
baglanti.Close();
}
else if (tabControl1.SelectedIndex == 1)
{
baglanti.Open();
OleDbCommand komut = new OleDbCommand("select * from tbl_Hasta where isim Like 'B%' or isim Like 'C%' or isim Like 'D%'", baglanti);
OleDbDataReader okuyucu = komut.ExecuteReader();
DataRow satır = veritablosu.NewRow();
while (okuyucu.Read())
{
satır = veritablosu.NewRow();
satır[0] = okuyucu[0].ToString();
satır[1] = okuyucu[1].ToString();
satır[2] = okuyucu[2].ToString();
satır[3] = okuyucu[3].ToString();
satır[4] = okuyucu[4].ToString();
satır[5] = okuyucu[5].ToString();
satır[6] = okuyucu[6].ToString();
satır[7] = okuyucu[7].ToString();
veritablosu.Rows.Add(satır);
}
dataGridView1.DataSource = veritablosu;
baglanti.Close();
}
else if (tabControl1.SelectedIndex == 2)
{
baglanti.Open();
OleDbCommand komut = new OleDbCommand("select * from tbl_Hasta where isim Like 'E%'", baglanti);
OleDbDataReader okuyucu = komut.ExecuteReader();
DataRow satır = veritablosu.NewRow();
while (okuyucu.Read())
{
satır = veritablosu.NewRow();
satır[0] = okuyucu[0].ToString();
satır[1] = okuyucu[1].ToString();
satır[2] = okuyucu[2].ToString();
satır[3] = okuyucu[3].ToString();
satır[4] = okuyucu[4].ToString();
satır[5] = okuyucu[5].ToString();
satır[6] = okuyucu[6].ToString();
satır[7] = okuyucu[7].ToString();
veritablosu.Rows.Add(satır);
}
dataGridView1.DataSource = veritablosu;
baglanti.Close();
}
else if (tabControl1.SelectedIndex == 3)
{
baglanti.Open();
OleDbCommand komut = new OleDbCommand("select * from tbl_Hasta where isim Like 'F%' or isim Like 'G%' or isim Like 'H%'", baglanti);
OleDbDataReader okuyucu = komut.ExecuteReader();
DataRow satır = veritablosu.NewRow();
while (okuyucu.Read())
{
satır = veritablosu.NewRow();
satır[0] = okuyucu[0].ToString();
satır[1] = okuyucu[1].ToString();
satır[2] = okuyucu[2].ToString();
satır[3] = okuyucu[3].ToString();
satır[4] = okuyucu[4].ToString();
satır[5] = okuyucu[5].ToString();
satır[6] = okuyucu[6].ToString();
satır[7] = okuyucu[7].ToString();
veritablosu.Rows.Add(satır);
}
dataGridView1.DataSource = veritablosu;
baglanti.Close();
}
else if (tabControl1.SelectedIndex == 4)
{
baglanti.Open();
OleDbCommand komut = new OleDbCommand("select * from tbl_Hasta where isim Like 'I%' or isim Like 'İ%' ", baglanti);
OleDbDataReader okuyucu = komut.ExecuteReader();
DataRow satır = veritablosu.NewRow();
while (okuyucu.Read())
{
satır = veritablosu.NewRow();
satır[0] = okuyucu[0].ToString();
satır[1] = okuyucu[1].ToString();
satır[2] = okuyucu[2].ToString();
satır[3] = okuyucu[3].ToString();
satır[4] = okuyucu[4].ToString();
satır[5] = okuyucu[5].ToString();
satır[6] = okuyucu[6].ToString();
satır[7] = okuyucu[7].ToString();
veritablosu.Rows.Add(satır);
}
dataGridView1.DataSource = veritablosu;
baglanti.Close();
}
else if (tabControl1.SelectedIndex == 5)
{
baglanti.Open();
OleDbCommand komut = new OleDbCommand("select * from tbl_Hasta where isim Like 'J%' or isim Like 'K%' or isim Like 'L%' or isim Like 'M%' or isim Like 'N%'", baglanti);
OleDbDataReader okuyucu = komut.ExecuteReader();
DataRow satır = veritablosu.NewRow();
while (okuyucu.Read())
{
satır = veritablosu.NewRow();
satır[0] = okuyucu[0].ToString();
satır[1] = okuyucu[1].ToString();
satır[2] = okuyucu[2].ToString();
satır[3] = okuyucu[3].ToString();
satır[4] = okuyucu[4].ToString();
satır[5] = okuyucu[5].ToString();
satır[6] = okuyucu[6].ToString();
satır[7] = okuyucu[7].ToString();
veritablosu.Rows.Add(satır);
}
dataGridView1.DataSource = veritablosu;
baglanti.Close();
}
else if (tabControl1.SelectedIndex == 6)
{
baglanti.Open();
OleDbCommand komut = new OleDbCommand("select * from tbl_Hasta where isim Like 'O%' or isim Like 'Ö%' ", baglanti);
OleDbDataReader okuyucu = komut.ExecuteReader();
DataRow satır = veritablosu.NewRow();
while (okuyucu.Read())
{
satır = veritablosu.NewRow();
satır[0] = okuyucu[0].ToString();
satır[1] = okuyucu[1].ToString();
satır[2] = okuyucu[2].ToString();
satır[3] = okuyucu[3].ToString();
satır[4] = okuyucu[4].ToString();
satır[5] = okuyucu[5].ToString();
satır[6] = okuyucu[6].ToString();
satır[7] = okuyucu[7].ToString();
veritablosu.Rows.Add(satır);
}
dataGridView1.DataSource = veritablosu;
baglanti.Close();
}
else if (tabControl1.SelectedIndex == 7)
{
baglanti.Open();
OleDbCommand komut = new OleDbCommand("select * from tbl_Hasta where isim Like 'P%' or isim Like 'T%' or isim Like 'R%' or isim Like 'S%' ", baglanti);
OleDbDataReader okuyucu = komut.ExecuteReader();
DataRow satır = veritablosu.NewRow();
while (okuyucu.Read())
{
satır = veritablosu.NewRow();
satır[0] = okuyucu[0].ToString();
satır[1] = okuyucu[1].ToString();
satır[2] = okuyucu[2].ToString();
satır[3] = okuyucu[3].ToString();
satır[4] = okuyucu[4].ToString();
satır[5] = okuyucu[5].ToString();
satır[6] = okuyucu[6].ToString();
satır[7] = okuyucu[7].ToString();
veritablosu.Rows.Add(satır);
}
dataGridView1.DataSource = veritablosu;
baglanti.Close();
}
else if (tabControl1.SelectedIndex == 8)
{
baglanti.Open();
OleDbCommand komut = new OleDbCommand("select * from tbl_Hasta where isim Like 'U%' or isim Like 'Ü%' ", baglanti);
OleDbDataReader okuyucu = komut.ExecuteReader();
DataRow satır = veritablosu.NewRow();
while (okuyucu.Read())
{
satır = veritablosu.NewRow();
satır[0] = okuyucu[0].ToString();
satır[1] = okuyucu[1].ToString();
satır[2] = okuyucu[2].ToString();
satır[3] = okuyucu[3].ToString();
satır[4] = okuyucu[4].ToString();
satır[5] = okuyucu[5].ToString();
satır[6] = okuyucu[6].ToString();
satır[7] = okuyucu[7].ToString();
veritablosu.Rows.Add(satır);
}
dataGridView1.DataSource = veritablosu;
baglanti.Close();
}
else if (tabControl1.SelectedIndex == 9)
{
baglanti.Open();
OleDbCommand komut = new OleDbCommand("select * from tbl_Hasta where isim Like 'V%' or isim Like 'Y%' or isim Like 'Z%'", baglanti);
OleDbDataReader okuyucu = komut.ExecuteReader();
DataRow satır = veritablosu.NewRow();
while (okuyucu.Read())
{
satır = veritablosu.NewRow();
satır[0] = okuyucu[0].ToString();
satır[1] = okuyucu[1].ToString();
satır[2] = okuyucu[2].ToString();
satır[3] = okuyucu[3].ToString();
satır[4] = okuyucu[4].ToString();
satır[5] = okuyucu[5].ToString();
satır[6] = okuyucu[6].ToString();
satır[7] = okuyucu[7].ToString();
veritablosu.Rows.Add(satır);
}
dataGridView1.DataSource = veritablosu;
baglanti.Close();
}
}
private void dataGridView1_SelectionChanged(object sender, EventArgs e)
{
if (dataGridView1.CurrentRow != null)
{
int id = Convert.ToInt32(dataGridView1.Rows[dataGridView1.CurrentRow.Index].Cells[0].Value);
baglanti.Open();
OleDbCommand komut = new OleDbCommand("select * from tbl_Hasta where hasta_id=" id, baglanti);
OleDbDataReader okuyucu = komut.ExecuteReader();
while (okuyucu.Read())
{
textBox_isim.Text = okuyucu[1].ToString();
textBox_dogumtarih.Text = okuyucu[2].ToString();
comboBox_dogumyer.Text = okuyucu[3].ToString();
comboBox_kangrp.Text = okuyucu[4].ToString();
if (okuyucu[5].ToString() == "E")
{
radioButton_erkek.Checked = true;
}
else
{
radioButton_kadin.Checked = true;
}
richTextBox_adres.Text = okuyucu[6].ToString();
textBox_tel.Text = okuyucu[7].ToString();
}
dataGridView1.DataSource = veritablosu;
baglanti.Close();
}
}
}
}
CodePudding user response：
Do not hold connections at the class level; it's too easy to forget to close it, or encounter some exception that means it's not closed, or run into another "there is already an open reader associated with..." if you're sharing connections.
Hold the connection string if you must (but there are other things e.g. Settings that are better places for that). Make a new connection with using every time you want one. Open() it, but you can leave the Dispose invoked by using to close it - one less line of code to write. Connections are fast to create and contra to what the naming might have you believe, they aren't opening and closing TCP (or some other slow) connection to the database when you call Open/Close - they're just being leased and returned from a pool that manages and recycles them for you. By holding onto them you risk undoing all the good that pool manager does; just make a new one, and throw it away when you're done
If you're using datatables, make your life easy and use a DataAdapter:
using var da = new OleDbDataAdapter("SELECT SQL here", "conn str here");
var dt = new DataTable();
da.Fill(dt);
That's all you need to fill the table; dataadapter will make the connection, open it, run the reader, pull the data, close the connection..
And then when you get tired of doing that and want some more help from VS to make life even easier (because base DataTable are largely a pig to work with) you can use strongly typed datatables - see my answer here for a glimpse (going into detail on it would be way out of scope of an SO answer) of what that looks like
CodePudding user response：
The problem is with storing connection instead of open/close for each operation.
Microsoft recommends to always close or dispose of a connection when you are finished using it in order to return the connection to the pool. Connections that are not explicitly closed may not get returned to the pool. For example, a connection that has gone out of scope but that has not been explicitly closed will only be returned to the connection pool if the maximum pool size has been reached and the connection is still valid. Microsoft article
Below you can find some example:
public void CallOleDb(string srcString)
{
conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" srcString "; Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
using(var connection = new OleDbConnection(conString))
{
connection.Open();
// do your stuff here...
}
}
Also please do not forget to use using when creating an instance of classes which implements IDisposable interface.