CRUD with C#/SQL in .NET Core

Refer to the repo: HERE

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!");

Comments

[Aleksey Podoba]

I'm think this is deprecated approach.
Entity Framework has already become part of .NET ecosystem.

[Matthew Cullen]

Yes you are right, I should have noted that. I am in a pre apprenticeship with WozU for Infosys and the program seems pretty dated and not so great. These examples were meant to help some classmates solve a couple labs.