CRUD with C#/SQL in .NET Core
Tags: csharp, sql, dotnet, database
Published: 2021-02-09 21:57:16

Refer to the repo: [HERE](https://github.com/3Mustard/basic-crud-csharp-sql)

## SETUP:
#### ~DataBase
The database for this example is the following
* TABLE NAME: CrudAppUsers
* Id int primaryKey
* Username varchar(50)
* Password varChar(50)

#### ~Data Connection String
To get a data connecting string and access your data base do the following:
* In Visual Studio go to View -> Server Explorer.
* In Server Explorer window, Under Data Connections Select your Database. Right Click your Database -> Click Properties.
* In Properties window you will see your Connection String

### CREATE
```
SqlConnection cn = new SqlConnection(@"PUT DATA CONNECTION STRING HERE");
cn.Open();
string Username, Password, Id;
Console.WriteLine("Enter The id and the new username/password of the new user: ");
Id = Console.ReadLine();
Username = Console.ReadLine();
Password = Console.ReadLine();

SqlCommand cmd = new SqlCommand("INSERT INTO CrudAppUsers VALUES (@Id, @Username, @Password)", cn);
cmd.Parameters.AddWithValue("@Id", int.Parse(Id));
cmd.Parameters.AddWithValue("@Username", Username);
cmd.Parameters.AddWithValue("@Password", Password);
cmd.ExecuteNonQuery();
cn.Close();
Console.WriteLine("Success!");
```

### READ :
#### ~Select All
```
SqlConnection cn = new SqlConnection(@"PUT DATA CONNECTION STRING HERE");
cn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM CrudAppUsers", cn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);

foreach (DataRow row in dt.Rows)
{
  string Id = row["Id"].ToString();
  string Username = row["Username"].ToString();
  string Password = row["Password"].ToString();
  Console.WriteLine($"ID: {Id}, Username: {Username}, Password: {Password}");
  Console.WriteLine("-------------------------------------------------------");
 }

cn.Close();
Console.WriteLine("Success!");
```
#### ~Select by ID
```
SqlConnection cn = new SqlConnection(@"PUT DATA CONNECTION STRING HERE");
cn.Open();
string Id;
Console.WriteLine("Enter The id of the user you wish to select: ");
Id = Console.ReadLine();
SqlCommand cmd = new SqlCommand("SELECT * FROM CrudAppUsers WHERE Id=@Id", cn);
cmd.Parameters.AddWithValue("Id", int.Parse(Id));
SqlDataAdapter da = new SqlDataAdapter(cmd); // pass select statement to the data adapter
DataTable dt = new DataTable();
da.Fill(dt); // use the data adapter to fill the empty data table
string Username = dt.Rows[0]["Username"].ToString();
string Password = dt.Rows[0]["Password"].ToString();
Console.WriteLine($"ID: {Id}, Username: {Username}, Password: {Password}");
cn.Close();
Console.WriteLine("Success!");
```

### UPDATE 
```
SqlConnection cn = new SqlConnection(@"PUT DATA CONNECTION STRING HERE");
cn.Open();
string Username, Password, Id;
Console.WriteLine("Enter The id of the user to update and the new username/password: ");
Id = Console.ReadLine();
Username = Console.ReadLine();
Password = Console.ReadLine();
SqlCommand cmd = new SqlCommand("UPDATE CrudAppUsers SET Username=@Username, Password=@Password WHERE Id = @Id", cn);
cmd.Parameters.AddWithValue("@Id", int.Parse(Id));
cmd.Parameters.AddWithValue("@Username", Username);
cmd.Parameters.AddWithValue("@Password", Password);
cmd.ExecuteNonQuery();
cn.Close();
Console.WriteLine("Success!");
```

### DELETE
```
SqlConnection cn = new SqlConnection(@"PUT DATA CONNECTION STRING HERE");
cn.Open();
string Id;
Console.WriteLine("Enter The id of the user you wish to delete: ");
Id = Console.ReadLine();
SqlCommand cmd = new SqlCommand("DELETE CrudAppUsers WHERE Id=@Id", cn);
cmd.Parameters.AddWithValue("@Id", int.Parse(Id));
cmd.ExecuteNonQuery();
cn.Close();
Console.WriteLine("Success!");
```