Tech
Forums
Jobs
Books
Events
Live
Interviews
More
Learn
Training
Career
Members
Videos
News
Blogs
Login
Sign Up
Ask Question
1
Reply
technical question about the internal functions
Jahangir Khan
May 14 2016 2:59 AM
256
Reply
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 ?
Upload Source Code
Select only zip and rar file.
Post
Reset
Cancel
Answers (
1
)
Next Recommended Forum
Add event to child node of treeview
Linux : Unity DllImport (“filename”) not working