SQL Databases using C# - Connecting and Updating

Duki 0 818 Views

I've spent the last couple of days researching how to connect to a database server and modify the data in that server. I was able to find a lot of information on how to Read from a database, but I found very little material to assist with Writing to a database.

This is an example code snippet of connecting to an SQL database on your network and modifying records within that database. My example only creates one connection, but it's easy enough to see how you can create multiple Connection Strings (cs) and then create multiple SQL Connections (conn) to different databases.

Some useful links:
http://www.csharp-station.com/tutorials/adodotnet/Lesson02.aspx
http://www.csharp-station.com/tutorials/adodotnet/Lesson03.aspx
http://www.sqlcommands.net/sql+update/

/*
    SQL Connection and Modification with real examples
*/

/*********/
using System.Data.SqlClient;
/*********/

//SQL connection string
/*****
Data Source = YourServerName\\DataBaseInstance
Initial Catalog = TheDatabaseName
Integrated Security = SSPI (uses current logon credentials to authenticate with Database Server)
******/
string cs = "Data Source=REG-PENTREE1\\SQLEXPRESS;Initial Catalog=reg_checkout;Integrated Security=SSPI";

//Create a connection
SqlConnection conn = new SqlConnection(cs);

//Open the SQL connection
conn.Open();

//Create SQL command to be sent
SqlCommand cmd = new SqlCommand("UPDATE Laptops SET isCheckedOut=1 WHERE name='pen-laptop1'", conn);

//Execute command
cmd.ExecuteNonQuery();
Member Avatar for kvprajapati
kvprajapati 1,826 Posting Genius

>>Parametrized query

/*
    SQL Connection and Modification with real examples
*/

/*********/
using System.Data.SqlClient;
/*********/

//SQL connection string
/*****
Data Source = YourServerName\\DataBaseInstance
Initial Catalog = TheDatabaseName
Integrated Security = SSPI (uses current logon credentials to authenticate with Database Server)
******/
string cs = "Data Source=REG-PENTREE1\\SQLEXPRESS;Initial Catalog=reg_checkout;Integrated Security=SSPI";

//Create a connection
SqlConnection conn = new SqlConnection(cs);


//Create SQL command to be sent
SqlCommand cmd = new SqlCommand("UPDATE Laptops SET isCheckedOut=@ischeckedout WHERE name=@name", conn);


//Define Parameters
cmd.Parameters.AddWithValue("@ischeckedout",1);
cmd.Parameters.AddWithValue("@name","pen-laptop1");


//Open the SQL connection
conn.Open();

//Execute command
cmd.ExecuteNonQuery();

//Close the connection
conn.Close();

Text From MSDN Page - If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose.

Member Avatar for dotnetfreak
dotnetfreak 0 Newbie Poster

sql database connectivity is of two types connected and disconnected...in connected explicit opening and closing of connection is done but the exact thing is done implicit by the .net ...use parameterisded queries mentioned bcoz it will avoid unnecessary database transactions by the end user using your application and also it will prevent type of attack known as sql injection attack..

Member Avatar for Duki
Duki 552 Nearly a Posting Virtuoso

Great - I love this. Here's some of my updated coded; I've added SQL_cmd builder functions throughout my app.

private void SQL_Update(string name, DateTime checkinDate, DateTime checkoutDate, string user, bool isCheckedOut)
        {
            cmd = new SqlCommand("UPDATE Laptops SET Name=@name, isCheckedOut = @ischeckedout, Checkout_Date=@checkout, Checkin_Date=@checkin, Checked_Out_By=@checkedoutby", conn);

            cmd.Parameters.AddWithValue("@name", name);
            cmd.Parameters.AddWithValue("@checkin", checkinDate);
            cmd.Parameters.AddWithValue("@checkout", checkoutDate);
            cmd.Parameters.AddWithValue("@checkedoutby", user);
            cmd.Parameters.AddWithValue("@ischeckedout", isCheckedOut);
        }
kvprajapati commented: Update query without where clause will update all rows.... +10
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Edit Preview