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/

Edited 6 Years Ago by Duki: n/a

/*
    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();

>>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.

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..

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);
        }
Comments
Update query without where clause will update all rows....
The article starter has earned a lot of community kudos, and such articles offer a bounty for quality replies.