Tutorial on connecting c# to SQL server

Question

I want to be able to edit a table in a SQL server database using c#.

Can someone please show me a very simple tutorial on connecting to the DB and editing data in a table.

Thank you so much.

Answer 1

First step is to create a connection. connection needs a connection string. you can create your connection strings with a SqlConnectionStringBuilder.

SqlConnectionStringBuilder connBuilder = new SqlConnectionStringBuilder();
connBuilder.InitialCatalog = "DatabaseName";
connBuilder.DataSource = "ServerName";
connBuilder.IntegratedSecurity = true;

Then use that connection string to create your connection like so:

SqlConnection conn = new SqlConnection(connBuilder.ToString());

//Use adapter to have all commands in one object and much more functionalities
SqlDataAdapter adapter = new SqlDataAdapter("Select ID, Name, Address from  myTable", conn);
adapter.InsertCommand.CommandText = "Insert into myTable (ID, Name, Address) values(1,'TJ', 'Iran')";
adapter.DeleteCommand.CommandText = "Delete From myTable Where (ID = 1)";
adapter.UpdateCommand.CommandText = "Update myTable Set Name = 'Dr TJ' Where (ID = 1)";

//DataSets are like arrays of tables
//fill your data in one of its tables 
DataSet ds = new DataSet();
adapter.Fill(ds, "myTable");  //executes Select command and fill the result into tbl variable

//use binding source to bind your controls to the dataset
BindingSource myTableBindingSource = new BindingSource();
myTableBindingSource.DataSource = ds;

Then, so simple you can use AddNew() method in the binding source to Add new record and then save it with update method of your adapter:

adapter.Update(ds, "myTable");

Use this command to delete a record:

myTableBindingSource.RemoveCurrent();
adapter.Update(ds, "myTable");

The best way is to add a DataSet from Project->Add New Item menu and follow the wizard...