这是我第一次使用C#和SQL。我已经设法使数据网格视图工作，无论是插入、更新还是删除。然而，数据网格视图中反映的所有更改都不会在SQL表中更新(当我打开数据库并单击"显示表数据"时，插入、更新和删除更改不会反映在那里。(
请帮忙！我搜索到的所有视频都只显示了他们的数据网格视图更改，但没有显示他们的SQL表是否真的更新了。
我的代码：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using System.Runtime.InteropServices;
namespace CRUDProj
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
displaydata();
button3.Visible = false;
DataGridViewCheckBoxColumn chk = new DataGridViewCheckBoxColumn();
chk.HeaderText = "Select";
chk.ValueType = typeof(bool);
chk.Name = "chkbox";
infoDataGridView.Columns.Insert(0, chk);
}
private void Form1_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'cRUDDBDataSet.Info' table. You can move, or remove it, as needed.
this.infoTableAdapter.Fill(this.cRUDDBDataSet.Info);
}
private void infoBindingNavigatorSaveItem_Click(object sender, EventArgs e)
{
this.Validate();
this.infoBindingSource.EndEdit();
this.tableAdapterManager.UpdateAll(this.cRUDDBDataSet);
}
private void displaydata()
{
string mainconn = ConfigurationManager.ConnectionStrings["CRUDProj.Properties.Settings.CRUDDBConnectionString"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(mainconn);
string sqlquery = "select * from [dbo].[Info]";
sqlconn.Open();
SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
DataTable dt = new DataTable();
SqlDataAdapter sdr = new SqlDataAdapter(sqlcomm);
sdr.Fill(dt);
infoDataGridView.DataSource = dt;
sqlconn.Close();
}
private void button1_Click(object sender, EventArgs e)
{
string mainconn = ConfigurationManager.ConnectionStrings["CRUDProj.Properties.Settings.CRUDDBConnectionString"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(mainconn);
string sqlquery = "insert into [dbo].[Info] values (@Id, @FullName, @NRIC, @Phone, @Temperature, @LocationLevel, @Date)";
sqlconn.Open();
SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
sqlcomm.Parameters.AddWithValue("@Id", iDTextBox.Text);
sqlcomm.Parameters.AddWithValue("@FullName", fullNameTextBox.Text);
sqlcomm.Parameters.AddWithValue("@NRIC", nRICTextBox.Text);
sqlcomm.Parameters.AddWithValue("@Phone", phoneTextBox.Text);
sqlcomm.Parameters.AddWithValue("@Temperature", temperatureTextBox.Text);
sqlcomm.Parameters.AddWithValue("@LocationLevel", locationLevelTextBox.Text);
sqlcomm.Parameters.AddWithValue("@Date", dateTextBox.Text);
sqlcomm.ExecuteNonQuery();
MessageBox.Show("Record Successfully Inserted");
displaydata();
sqlconn.Close();
}
public string message = string.Empty;
private void button2_Click(object sender, EventArgs e)
{
foreach(DataGridViewRow row in infoDataGridView.Rows)
{
bool issellected = Convert.ToBoolean(row.Cells["chkbox"].Value);
if (issellected)
{
message = Environment.NewLine;
message = row.Cells[1].Value.ToString();
}
}
label1.Text = message;
label1.Visible = true;
button3.Visible = true;
button1.Enabled = false;
button2.Enabled = false;
button4.Enabled = false;
button5.Enabled = false;
}
private void button3_Click(object sender, EventArgs e)
{
string mainconn = ConfigurationManager.ConnectionStrings["CRUDProj.Properties.Settings.CRUDDBConnectionString"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(mainconn);
string sqlquery = "update [dbo].[Info] set Id=@Id, FullName=@FullName, NRIC=@NRIC, Phone=@Phone, Temperature=@Temperature, LocationLevel=@LocationLevel, Date=@Date where Id=@Id";
sqlconn.Open();
SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
sqlcomm.Parameters.AddWithValue("@Id" ,label1.Text);
sqlcomm.Parameters.AddWithValue("@FullName", fullNameTextBox.Text);
sqlcomm.Parameters.AddWithValue("@NRIC", nRICTextBox.Text);
sqlcomm.Parameters.AddWithValue("@Phone", phoneTextBox.Text);
sqlcomm.Parameters.AddWithValue("@Temperature", temperatureTextBox.Text);
sqlcomm.Parameters.AddWithValue("@LocationLevel", locationLevelTextBox.Text);
sqlcomm.Parameters.AddWithValue("@Date", dateTextBox.Text);
sqlcomm.ExecuteNonQuery();
infoTableAdapter.Update(cRUDDBDataSet.Info);
sqlconn.Close();
MessageBox.Show("Record Updated Successfully! ");
displaydata();
button3.Visible = false;
button1.Enabled = true;
button2.Enabled = true;
button4.Enabled = true;
button5.Enabled = true;
DataRowView drv = infoDataGridView.CurrentRow.DataBoundItem as DataRowView;
DataRow[] rowsToUpdate = new DataRow[] { drv.Row };
SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM dbo.Info", sqlconn);
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
adapter.Update(rowsToUpdate);
this.infoTableAdapter.Update(this.cRUDDBDataSet.Info);
}
private void textBox1_TextChanged(object sender, EventArgs e)
{
}
private void button5_Click(object sender, EventArgs e)
{
string mainconn = ConfigurationManager.ConnectionStrings["CRUDProj.Properties.Settings.CRUDDBConnectionString"].ConnectionString;
SqlConnection sqlconn = new SqlConnection(mainconn);
List<String> empselect = new List<String>();
DataGridViewRow row = new DataGridViewRow();
for (int i = 0; i<= infoDataGridView.Rows.Count-1; i++)
{
row = infoDataGridView.Rows[i];
if (Convert.ToBoolean(row.Cells [0].Value)==true)
{
string id = row.Cells[1].Value.ToString();
empselect.Add(id);
}
sqlconn.Open();
foreach (string s in empselect)
{
SqlCommand sqlcomm = new SqlCommand("delete from [dbo].[Info] where Id = ' " + s + " ' ", sqlconn);
sqlcomm.ExecuteNonQuery();
}
sqlconn.Close();
}
MessageBox.Show("Record Deleted Successfully! ");
displaydata();
}
private void iDTextBox1_TextChanged(object sender, EventArgs e)
{
}
private void button4_Click(object sender, EventArgs e)
{
iDTextBox.Clear();
fullNameTextBox.Clear();
nRICTextBox.Clear();
phoneTextBox.Clear();
temperatureTextBox.Clear();
locationLevelTextBox.Clear();
dateTextBox.Clear();
}
}
}
将DGV绑定到数据库这将帮助您轻松执行crud操作。
这是DGV上执行一些操作的示例代码，可能会对您有所帮助。但请确保将DGV绑定到数据库表
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
_id = Convert.ToInt16(dataGridView1.Rows[e.RowIndex].Cells[0].Value);
string _name = dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();
string _lname = dataGridView1.Rows[e.RowIndex].Cells[2].Value.ToString();
string _sex = dataGridView1.Rows[e.RowIndex].Cells[3].Value.ToString();
string _age = dataGridView1.Rows[e.RowIndex].Cells[4].Value.ToString();
if (e.ColumnIndex == dataGridView1.Columns["delete"].Index && e.RowIndex >= 0)
{
if(groupBox1.Visible==true)
{
groupBox1.Visible = false;
}
_id = Convert.ToInt16(dataGridView1.Rows[e.RowIndex].Cells[0].Value);
DialogResult result = MessageBox.Show("Do You Want to delete?", "Delete", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (result.Equals(DialogResult.OK) && service.deleterecord(_id, _name, _lname, _age, _sex))
{
MessageBox.Show("deleted record");
this.Close();
}
else
{
MessageBox.Show("not deleted");
}
}
if (e.ColumnIndex == dataGridView1.Columns["edit"].Index && e.RowIndex >= 0)
{
groupBox1.Visible = true;
firstname.Text = _name;
lastname.Text = _lname;
gender.Text = _sex;
age.Text = _age;
}
}
private void update_Click(object sender, EventArgs e)
{
string _fname = firstname.Text;
string _lname = lastname.Text;
string _age = age.Text;
string _sex = gender.Text;
try
{
if (string.IsNullOrWhiteSpace(_fname) || string.IsNullOrWhiteSpace(_lname) || string.IsNullOrWhiteSpace(_age) || string.IsNullOrWhiteSpace(_sex) || gender.Equals(null))
{
MessageBox.Show("Please enter the empty fields");
}
else
{
if (Updatedata("Update Query"))
{
MessageBox.Show("Record Updated");
//this.recordTableAdapter.Fill(this.task2DataSet.record);
groupBox1.Visible = false;
this.Size = new Size(595, 258);
this.Close();
}
else
{
MessageBox.Show("Failed to update");
}
}
}
catch (FormatException ex)
{
MessageBox.Show(ex.Message);
}
catch (ArgumentNullException ex)
{
MessageBox.Show(ex.Message);
}
}