technical question about the internal functions

Hello,

Here is a demo of a program doing what it should do "Add", "Update","Delete" data.

using System;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data;
namespace dss
{
public partial class Form1 : Form
{
SqlConnection con = new SqlConnection("Data Source=JAKHAN-PC\\sqlexpress;Initial Catalog=JG_Test;Integrated Security=True");
string connString = "Data Source=JAKHAN-PC\\sqlexpress;Initial Catalog=JG_Test;Integrated Security=True";
public Form1()
{
InitializeComponent();
}
private void btnSearch_Click(object sender, EventArgs e)
{
SqlDataReader reader;
SqlCommand cmd = new SqlCommand();
try
{
string sql = "SELECT * FROM Members WHERE MemberId = '"+ tbID.Text +"' ";
cmd.Connection = con;
cmd.CommandText = sql;
con.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
tbID.Text = reader["MemberId"].ToString();
tbName.Text = reader["Name"].ToString();
tbMobile.Text = reader["Mobile"].ToString();
tbEmail.Text = reader["Email"].ToString();
tbAddress.Text = reader["Address"].ToString();
}
con.Close();
sql= "SELECT * FROM Payments WHERE MemberId = '" + tbID.Text + "' ";
cmd.Connection = con;
cmd.CommandText = sql;
con.Open();
reader = cmd.ExecuteReader();
while (reader.Read())
{
tbID.Text = reader["MemberId"].ToString();
tbYear.Text = reader["Year"].ToString();
tbAmount.Text = reader["Amount"].ToString();
}
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
}
private void btnAdd_Click(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(connString))
{
con.Open();
string Sql = "INSERT INTO Members ( MemberId, Name, Address, Mobile, Email ) VALUES " + " (@Id, @name, @address, @mobile, @email)";
using (SqlCommand cmd = new SqlCommand(Sql, con))
{
cmd.CommandText = Sql;
cmd.Parameters.AddWithValue("@Id", tbID.Text);
cmd.Parameters.AddWithValue("@name", tbName.Text);
cmd.Parameters.AddWithValue("@address", tbAddress.Text);
cmd.Parameters.AddWithValue("@mobile", tbMobile.Text);
cmd.Parameters.AddWithValue("@email", tbEmail.Text);
cmd.ExecuteNonQuery();
Sql = "INSERT INTO Payments (MemberId, [Year], [Amount] ) VALUES " + "(@id, @year, @amount)";
cmd.Parameters.Clear();
cmd.CommandText = Sql;
cmd.Parameters.AddWithValue("@Id", tbID.Text);
cmd.Parameters.AddWithValue("@year", tbYear.Text);
cmd.Parameters.AddWithValue("@amount", tbAmount.Text);
cmd.ExecuteNonQuery();
MessageBox.Show("Data Added");
tbID.Clear();tbName.Clear();tbAddress.Clear();tbMobile.Clear();tbEmail.Clear();tbYear.Clear();tbAmount.Clear();
con.Close();
}
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
try
{
SqlCommand cmd = new SqlCommand();
string Sql = "UPDATE Members SET MemberId = '" + tbID.Text + "', Name = '" + tbName.Text + "', Address = '" + tbAddress.Text + "', Mobile = '" + tbMobile.Text + "', Email = '" + tbEmail.Text + "' WHERE MemberId = '" + tbID.Text + "' ";
cmd.CommandText = Sql;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Sql = "UPDATE Payments SET MemberId = '" + tbID.Text + "', Year = '" + tbYear.Text + "', Amount = '" + tbAmount.Text + "' WHERE MemberId = '" + tbID.Text + "' ";
cmd.CommandText = Sql;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Data Updated");
tbID.Clear(); tbName.Clear(); tbAddress.Clear(); tbMobile.Clear(); tbEmail.Clear(); tbYear.Clear(); tbAmount.Clear();
con.Close();
}
catch (Exception error)
{
MessageBox.Show(error.ToString());
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
try
{
SqlCommand cmd = new SqlCommand();
string Sql = "DELETE FROM Members WHERE MemberId = '" + tbID.Text + "' ";
cmd.CommandText = Sql;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Sql = "DELETE FROM Payments WHERE MemberId = '" + tbID.Text + "' ";
cmd.CommandText = Sql;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
tbID.Clear(); tbName.Clear(); tbAddress.Clear(); tbMobile.Clear(); tbEmail.Clear(); tbYear.Clear(); tbAmount.Clear();
MessageBox.Show("Data Deleted");
con.Close();
}
catch (Exception error)
{
MessageBox.Show(error.ToString());
}
}
private void btnReset_Click(object sender, EventArgs e)
{
tbID.Clear(); tbName.Clear(); tbAddress.Clear(); tbMobile.Clear(); tbEmail.Clear(); tbYear.Clear(); tbAmount.Clear();
}
private void btnExit_Click(object sender, EventArgs e)
{
Application.Exit();
}
}
}

My question is "WHY" I have to declare the connection twice in line 11 & 12, ( remeber it's a learning and uderstanding process ) is it wrong to have it declared twice or is it correct ? or there is a more simple short and smarter way to do it ?

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Bhuvanesh Mohankumar10y ago
Accepted answer
I believe that this is repeatative of code in your application, if in case you use two different database then it would be fine, I can see in both
Con & Connection string you use same database and connection.
Better try this, you will get answer for your question,
comment the "connString"and then replace the line number 60 , as below.
Before:
using (SqlConnection con = new SqlConnection(connString))
After:
using (SqlConnection con = new SqlConnection(con))
Now check your application and let me know,
Mark as answer, if helpful.
+1