C# Database Connection Notes.md

C# Database Connection Notes.md

Create Local DB & Setup DataGridView

Get DataGridView Data into New Form

Example SQL Set Data

string connString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Joswar\source\repos\C898_Capstone\InventoryDB.mdf;Integrated Security=True";
string queryString = $"INSERT INTO Inventory ([Id], [Name], [Product Number], [Description], [Quantity], [Expiration Date], [Record Modified]) VALUES (7, '{nameInput.Text}', {Convert.ToInt32(productNumberInput.Text)}, '{descriptionInput.Text}', {Convert.ToInt32(quantityInput.Text)}, '{expirationDateInput.Text}', '{DateTime.Now}')";
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlCommand cmd = new SqlCommand(queryString, conn);
cmd.ExecuteNonQuery();
conn.Close();

Example SQL Get Data

var recordIDValue = inventoryDataGridView.CurrentRow.Cells[0].Value;
string connString = @"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Joswar\source\repos\C898_Capstone\InventoryDB.mdf;Integrated Security=True";
string queryString = $"SELECT [Id], [Name], [Product Number], [Description], [Quantity], [Expiration Date], [Record Modified] FROM Inventory WHERE Id = {recordIDValue}";
SqlConnection conn = new SqlConnection(connString);
conn.Open();
SqlCommand cmd = new SqlCommand(queryString, conn);            
SqlDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{                
    while (reader.Read())
    {                    
        int id = reader.GetInt32(0);
        string name = reader.GetString(1);
        int productNumber = reader.GetInt32(2);
        string description = reader.GetString(3);
        int quantity = reader.GetInt32(4);
        string expirationDate = reader.GetString(5);
        DateTime recordModified = reader.GetDateTime(6);
        // below only example of what can be done with data
        MessageBox.Show($"{id}, {name}, {productNumber}, {description}, {quantity}, {expirationDate}, {recordModified}", "Data");                
    }
}
conn.Close();

Example SQL Populate DataGridView

    public void refreshInventoryDGV()
    {
        string connString = Data.connString;
        string queryString = "SELECT * FROM Inventory";
        SqlConnection conn = new SqlConnection(connString);            
        SqlCommand cmd = new SqlCommand(queryString, conn);
        SqlDataAdapter dAdapter = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        dAdapter.Fill(ds);
        inventoryDataGridView.DataSource = ds.Tables[0];
    }