protected void ShowAll() { using (SqlConnection connection = new SqlConnection(GetConnection.GetConnectionString())) { SqlCommand cmd = new SqlCommand("Select * from Persons", connection); connection.Open(); GridView1.DataSource = cmd.ExecuteReader(); GridView1.DataBind(); } }
protected void Delete() { string sqlString = "DELETE FROM Persons WHERE Name = @Name"; using (SqlConnection connection = new SqlConnection(GetConnection.GetConnectionString())) { SqlCommand cmd = GetCommandWithParameters(sqlString, connection); connection.Open(); int rowsAffected = cmd.ExecuteNonQuery(); lblLog.Text = $"Er zijn {rowsAffected} regels gedeletet"; } ShowAll(); }
protected void Insert() { string sqlString = "INSERT INTO Persons (Name, Age, Address, City) VALUES (@Name, @Age, @Address, @City)"; using (SqlConnection connection = new SqlConnection(GetConnection.GetConnectionString())) { SqlCommand cmd = GetCommandWithParameters(sqlString, connection); connection.Open(); int rowsAffected = cmd.ExecuteNonQuery(); lblLog.Text = $"Er zijn {rowsAffected} regels toegevoegd"; } ShowAll(); }
protected void Update() { string sqlString = GetSqlVariables(); if (sqlString == "") { lblLog.Text = $"Er zijn geen velden aangepast."; ShowAll(); return; } sqlString = sqlString.Substring(0, sqlString.Length - 2); sqlString = "UPDATE Persons SET " + sqlString + $" WHERE ID = @ID"; using (SqlConnection connection = new SqlConnection(GetConnection.GetConnectionString())) { SqlCommand cmd = GetCommandWithParameters(sqlString, connection); connection.Open(); int rowsAffected = cmd.ExecuteNonQuery(); lblLog.Text = rowsAffected == 0?"Deze ID bestaat niet.":$"Er zijn {rowsAffected} rijen aangepast."; } ShowAll(); }
protected void Find() { string sqlString = "SELECT * FROM Persons WHERE Name LIKE @LikeName AND Age = @Age AND Address LIKE @LikeAddress AND City LIKE @LikeCity"; string sqlStringNoAge = "SELECT * FROM Persons WHERE Name LIKE @LikeName AND Address LIKE @LikeAddress AND City LIKE @LikeCity"; using (SqlConnection connection = new SqlConnection(GetConnection.GetConnectionString())) { SqlCommand cmd; if (txtAge.Text != "") { cmd = GetCommandWithParameters(sqlString, connection); } else { cmd = GetCommandWithParameters(sqlStringNoAge, connection); } connection.Open(); GridView1.DataSource = cmd.ExecuteReader(); GridView1.DataBind(); lblLog.Text = $"Er zijn {GridView1.Rows.Count} records gevonden."; } }