This blog is moved to
http://amalhashim.wordpress.com

Tuesday, April 6, 2010

C# – Read, Insert, Update, Delete From SQL Database

Code snippets for reading, inserting, updating and deleting from SQL database.

static void Read()
{
try
{
string connectionString =
"server=.;" +
"initial catalog=employee;" +
"user id=sa;" +
"password=sa123";
using (SqlConnection conn =
new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd =
new SqlCommand("SELECT * FROM EmployeeDetails", conn))
{
SqlDataReader reader = cmd.ExecuteReader();

if (reader.HasRows)
{
while (reader.Read())
{
Console.WriteLine("Id = ", reader["Id"]);
Console.WriteLine("Name = ", reader["Name"]);
Console.WriteLine("Address = ", reader["Address"]);
}
}

reader.Close();
}
}
}
catch (SqlException ex)
{
//Log exception
//Display Error message
}
}

static void Insert()
{
try
{
string connectionString =
"server=.;" +
"initial catalog=employee;" +
"user id=sa;" +
"password=sa123";
using (SqlConnection conn =
new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd =
new SqlCommand("INSERT INTO EmployeeDetails VALUES(" +
"@Id, @Name, @Address)", conn))
{
cmd.Parameters.AddWithValue("@Id", 1);
cmd.Parameters.AddWithValue("@Name", "Amal Hashim");
cmd.Parameters.AddWithValue("@Address", "Bangalore");

int rows = cmd.ExecuteNonQuery();

//rows number of record got inserted
}
}
}
catch (SqlException ex)
{
//Log exception
//Display Error message
}
}

static void Update()
{
try
{
string connectionString =
"server=.;" +
"initial catalog=employee;" +
"user id=sa;" +
"password=sa123";
using (SqlConnection conn =
new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd =
new SqlCommand("UPDATE EmployeeDetails SET Name=@NewName, Address=@NewAddress" +
" WHERE Id=@Id", conn))
{
cmd.Parameters.AddWithValue("@Id", 1);
cmd.Parameters.AddWithValue("@Name", "Munna Hussain");
cmd.Parameters.AddWithValue("@Address", "Kerala");

int rows = cmd.ExecuteNonQuery();

//rows number of record got updated
}
}
}
catch (SqlException ex)
{
//Log exception
//Display Error message
}
}

static void Delete()
{
try
{
string connectionString =
"server=.;" +
"initial catalog=employee;" +
"user id=sa;" +
"password=sa123";
using (SqlConnection conn =
new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd =
new SqlCommand("DELETE FROM EmployeeDetails " +
"WHERE Id=@Id", conn))
{
cmd.Parameters.AddWithValue("@Id", 1);

int rows = cmd.ExecuteNonQuery();

//rows number of record got deleted
}
}
}
catch (SqlException ex)
{
//Log exception
//Display Error message
}
}

13 comments:

chusta said...

thanx a bunch...

A.m.a.L Hashim said...

@chusta, nice to hear this helped.

sundar said...

hai Hasim,
Good morning, i am new of C# I catch your code.that is very nice but how to use in webform

A.m.a.L Hashim said...

@sundar, there are several ways to use this code.

1. Create a class library project and use it in the web form
2. Add a class in App_Code folder of your web application project and paste the code there.

SS said...

Thanks a bunch, used it to encode passcode in the db using the update code. very useful!.

merimo said...

Verry nice begginer example. Can you plese make example how to insert data from SQL joined tables into database?

Problem is that user see data from two different tables and change something, now I need to update that from dataset to database.
Hope you understand me :)

merimo said...

Very nice example. Can you please make example how to insert data from SQL joined tables into database?

Problem is that user see data from two different tables and change something, now I need to update that from dataset to database.
Hope you understand me :)

baljeet singh Ranu said...

thanks a lot...got the same code looking for ages...

Sudhir DBAKings said...

Nice post very helpful

dbakings

rahul said...

nice code

PRAVEEN said...

Awsome tutorial. I got a small issue with delete code my code doesn't give any errors when executed nor exceptions in run time.But data wont get deleted from data base as expected.hope u could guide me(insert code works perfectly)

Eben Snyman said...

Thanks a million, this solved a big update bug in my clients software.

sam said...

Thank you - very helpful, just what I needed an example in code without the person trying to justify their methods and confusing the issue. Top job.