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

Answer 2

Have a read of the MSDN tutorial on Creating Data Applications. You may be able to clarify your question, or find the answers you need.

There is info on editing the data in the app but you have to get connected and load it into your app first.

Answer 3

The only reason to do this in C# is if you want to automate it somehow or create an interface for non-technical users to interact with the database. You can use a GridView control with an SQL datasource to manipulate the data.

@kevin: if he's just learning, I think its probably simpler to have him use SQLCommand object (or SQLDataAdapter).

Answer 4

Assuming you're using Visual Studio as your IDE you could just use LINQ to SQL. It's a pretty simple way to interact with your database and it should be pretty quick to get going.

Using LINQ to SQL is a pretty simple walk through in getting it up and running.