C# Create a SQL Db connection in code with update, insert, and delete capability
1. MAKE SURE YOU ADD THE FOLLOWING CLASSES
using System.Data;
using System.Data.SqlClient;
2. DECLARE THE FOLLOWING AT THE FORM LEVEL
private DataSet ds_Employees = new DataSet();
private SqlDataAdapter da_Employees = new SqlDataAdapter();
private string str_Employees_SQL = "SELECT * FROM Employees";
private string str_Employees_Connection = "initial catalog=Employees;workstation id=DBA2158;packet size=4096;integrated security=SSPI;data source=DBSERVER2;persist security info=True";
private SqlConnection con_Employees = new SqlConnection();
// The line below allows you to perform insert, updates, and deletes. It builds the commands for you
// without it, you cannot do updates on the dataset
private SqlCommandBuilder cb_Employees = new SqlCommandBuilder();
3. ADD THE FOLLOWING WHERE YOU WANT TO EXECUTE THE SQL (e.g. OnLoad)
// Create a connection and open it
con_Employees = new SqlConnection(str_Employees_Connection);
con_Employees.Open();
// Create a dataAdapter and dataset, then fill the dataAdapter
da_Employees = new SqlDataAdapter(str_Employees_SQL, con_Employees);
da_Employees.Fill(ds_Employees, "Employees");
// You need the line below to build the update, delete, insert commands for the dataAdapter
cb_Employees = new SqlCommandBuilder(da_Employees);
// Fill dataset
da_Employees.Fill(ds_Employees);
4. IF YOU HAVE A DATAGRID, YOU CAN REFRESH IT
// Update the datagrid with Source, Caption and a refresh
grd_Employees.DataSource = ds_Employees.Tables["Employees"];
grd_Employees.CaptionText = "Employees";
grd_Employees.Refresh();