1 Setting up Visual Studio

Setting up a Visual Studio Forms app in C#.

Here is the code you will need:
//------------------------------------------------------------------------------------------
using Microsoft.Data.SqlClient;
using System.Data;

class Database
{
// TODO - this needs to be changed to your connection string
private string connectionStr = "this needs to change";
// this allows create and insert sql commands to be executed


// creates a table in a database. Returns true if the table was created. False if the table
// already exists or there was an error
public bool CreateTable(string sql)
{
bool retVal = true;
try
{
ExecuteNonQuery(sql);
}
catch
{
retVal = false;
}

return retVal;
}

// Inserts data into a table. Returns true if it worked, false if not
public bool Insert(string sql)
{
bool retVal = true;
try
{
ExecuteNonQuery(sql);
}
catch
{
retVal = false;
}

return retVal;
}

// Select data from a table, returned as a DataTable
public DataTable Select(string sql)
{
DataTable dataTable = new DataTable();

using (SqlConnection connection = new SqlConnection(connectionStr))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = sql;

// create data adapter
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
{
// this will query your database and return the result to your datatable

dataAdapter.Fill(dataTable);
connection.Close();
}
}

}
return dataTable;
}

// if Id is used as the primary key, returns the integer value of the next available value
public int GetNextId(string tableName)
{
int retVal = 1;
using (SqlConnection connection = new SqlConnection(connectionStr))
{
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT MAX(Id) FROM " + tableName;
using (SqlDataReader reader = command.ExecuteReader())
{
try
{
if (reader.Read())
{
retVal = reader.GetInt32(0) + 1;
}
reader.Close();
}
catch
{
reader.Close();
retVal = 1;
}
}
connection.Close();
}
return retVal;
}


public void DeleteTable(string tableName)
{
ExecuteNonQuery("DROP TABLE " + tableName);
}

private void ExecuteNonQuery(string sql)
{
using (SqlConnection connection = new SqlConnection(connectionStr))
{

connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText = sql;
command.ExecuteNonQuery();
}
}

}