C＃从具有更新和删除功能的数据库中填充GridView
我一直在寻找一种从sql本地数据库填充GridView的方法，而不需要创建重复项。
I have been hunting for a way to populate a GridView from a sql local db with edit and delete functionality without creating duplicates.
到目前为止，我已经有了作为创建SQL查询，将结果存储为数据表并将其推送到GridView中。我似乎无法弄清楚如何管理编辑和删除功能，并希望有人能够帮助。
So far I have got as far as creating a SQL query, storing the results as a datatable and pumping that in to the GridView. I cannot seem to figure out how to go about managing edit and delete functionality and hoping someone here can help.
非常感谢。
以下是我填充GridView的方式：
Here's how I am populating the GridView:
//Load data in to parameters gridview
using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["PowerShellManager.Properties.Settings.PSManagerConnectionString"].ConnectionString))
{
con.Open();
SqlCommand comm3 = new SqlCommand("SELECT * FROM Parameters WHERE ScriptId = (SELECT Scripts.ScriptId FROM Scripts WHERE Scripts.ScriptName = @ScriptName) AND ProfileId = (SELECT Profiles.ProfileId FROM Profiles WHERE Profiles.ProfileName = @currentlySelectedProfile)", con);
comm3.Parameters.AddWithValue("ScriptName", listView_Scripts.SelectedItems[0].Text);
comm3.Parameters.AddWithValue("currentlySelectedProfile", currentlySelectedProfile);
DataTable t3 = new DataTable();
t3.Load(comm3.ExecuteReader());
//variablesDataGridView.DataSource = null;
parametersDataGridView.DataSource = t3;
//variablesBindingSource.Filter = new
con.Close();
}
我创建了一个简单应用程序只为你而编辑整个答案！这将是一个很长的答案。您可以将新的Person保存到数据库或从数据库中删除Person，以及从 GridView 中。这里是一些图片示例的代码：
I've created a simple application just for you and edited whole answer! This will be a very long answer. You can save new Person to database or delete a Person from database, aswell as from GridView. Here is code with some picture examples:
个人类：
public class Person
{
#region Members
private int _ID = -1;
private string _FirstName = string.Empty;
private string _LastName = string.Empty;
private DateTime? _BirthDate = null;
private string _PhoneNumber = string.Empty;
private string _Email = string.Empty;
private bool _Changed = false;
#endregion
public Person()
{
_Changed = false;
}
#region Methods
public void Save()
{
if (!_Changed)
return;
using (SqlConnection con = new SqlConnection(@"server=.\sqlexpress;database=People;integrated Security=True;"))
{
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "Person_Save";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ID", _ID);
cmd.Parameters.AddWithValue("@FirstName", _FirstName);
cmd.Parameters.AddWithValue("@LastName", _LastName);
cmd.Parameters.AddWithValue("@BirthDate", _BirthDate);
cmd.Parameters.AddWithValue("@PhoneNumber", _PhoneNumber);
cmd.Parameters.AddWithValue("@Email", _Email);
try
{
con.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
_ID = dr.GetInt32(0);
_Changed = false;
}
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message, "Error message");
}
}
}
public void Delete()
{
using (SqlConnection con = new SqlConnection(@"server=.\sqlexpress;database=People;integrated Security=True;"))
{
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "Person_Delete";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ID", _ID);
try
{
con.Open();
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message, "Error message");
}
}
}
#endregion
#region Properties
public int ID
{
get { return _ID; }
set
{
if (_ID != value)
{
_ID = value;
_Changed = true;
}
}
}
public string FirstName
{
get { return _FirstName; }
set
{
if (_FirstName != value)
{
_FirstName = value;
_Changed = true;
}
}
}
public string LastName
{
get { return _LastName; }
set
{
if (_LastName != value)
{
_LastName = value;
_Changed = true;
}
}
}
public DateTime? BirthDate
{
get { return _BirthDate; }
set
{
if (_BirthDate != value)
{
_BirthDate = value;
_Changed = true;
}
}
}
public string PhoneNumber
{
get { return _PhoneNumber; }
set
{
if (_PhoneNumber != value)
{
_PhoneNumber = value;
_Changed = true;
}
}
}
public string Email
{
get { return _Email; }
set
{
if (_Email != value)
{
_Email = value;
_Changed = true;
}
}
}
public bool Changed
{
get { return _Changed; }
set { _Changed = value; }
}
#endregion
}
PeopleForm类：
public partial class PeopleForm : Form
{
public PeopleForm()
{
InitializeComponent();
FillDataSource();
}
public void FillDataSource()
{
List<Person> list = new List<Person>();
using (SqlConnection con = new SqlConnection(@"server=.\sqlexpress;database=People;integrated Security=True;"))
{
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "People_Read";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
try
{
con.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
list.Add(new Person()
{
ID = (int)dr["ID"]
,FirstName = dr["FirstName"] as string
,LastName = dr["LastName"] as string
,BirthDate = dr["BirthDate"] as DateTime?
,PhoneNumber = dr["PhoneNumber"] as string
,Email = dr["Email"] as string
});
}
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message, "Error message");
}
}
personBindingSource.DataSource = list;
}
private void btnSave_Click(object sender, EventArgs e)
{
foreach (Person person in personBindingSource)
{
person.Save();
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
((Person)grdPeople.CurrentRow.DataBoundItem).Delete();
personBindingSource.RemoveAt(grdPeople.CurrentRow.Index);
}
}
存储过程
CREATE PROCEDURE [dbo].[Person_Save]
@ID int
,@FirstName nvarchar(30)
,@LastName nvarchar(30)
,@BirthDate date
,@PhoneNumber nvarchar(30)
,@Email nvarchar(50)
AS
BEGIN
IF(@ID = -1)
BEGIN
INSERT INTO People
VALUES (@FirstName,
@LastName,
@BirthDate,
@PhoneNumber,
@Email)
SET @ID = SCOPE_IDENTITY();
END
ELSE
BEGIN
UPDATE People
SET FirstName = @FirstName,
LastName = @LastName,
BirthDate = @BirthDate,
PhoneNumber = @PhoneNumber,
Email = @Email
WHERE ID = @ID
END
SELECT @ID;
END
CREATE PROCEDURE [dbo].[Person_Delete]
@ID int
AS
BEGIN
DELETE
FROM People
WHERE ID = @ID
END
CREATE PROCEDURE [dbo].[People_Read]
AS
BEGIN
SELECT ID
,FirstName
,LastName
,BirthDate
,PhoneNumber
,Email
FROM People
ORDER BY LastName, FirstName
END
您将不得不将您的Person类绑定到您的 DataGridView 作为绑定来源，如下所示：
You will have to bind your Person class to your DataGridView as binding source like this:
最后你得到如下工作的应用程序：
In the end you get working application that looks like this:
请注意，我已经更改了颜色，没有什么特别的。我添加了几个人为例。您必须在出生日期列中处理错误的输入错误，但有一些探索乐趣。我想我给你足够的开始你的申请。
Notice that I've changed colors just a bit, nothing special. I've added a few people as an example. You'll have to handle error for the wrong input in your birth date column, but have some fun exploring. I think I gave you more than enough to start your application.
玩得开心！