学步园

--建表
if object_id('gtest') is not null 
drop table gtest
go
create table gtest(
id int identity(1,1) not null constraint [pk_gtest] primary key,
name nvarchar(20) not null constraint [uk_gtest_name] unique,
price float not null
)
go
set nocount on;
declare @count int
select @count=1
while @count<50
begin
	insert into gtest values('产品'+cast(@count as nvarchar(2)),10*@count);
	select @count=@count+1;
end
go

--前台拖个DataGridView(datagridview1)进去，不做任何其他操作

--后台，数据库操作类此略

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Diagnostics;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Text.RegularExpressions;
namespace cswinformtest
{
    public partial class Form1 : Form
    {
        bool firstLoad = true;
        public Form1()
        {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            DataBind();
        }
        #region datagridview command
        /// <summary>
        /// bind datagridview
        /// </summary>
        private void DataBind()
        {
            this.dataGridView1.DataSource = SQLHelper.ExecuteDataTable(SQLHelper.ConStr, CommandType.Text, "select * from gtest");
            this.dataGridView1.Columns[0].ReadOnly = true;
        }
        /// <summary>
        /// for insert,update
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dataGridView1_RowLeave(object sender, DataGridViewCellEventArgs e)
        {
            if (!firstLoad)
            {
                DataGridView dgv = sender as DataGridView;
                //update cell immediately
                if (dgv.EditingControl != null)
                {
                    if (dgv.CurrentCell.ColumnIndex == 2)
                    {
                        dgv.CurrentCell.Value = dgv.EditingControl.Text.Replace(",", "");
                    }
                    else
                    {
                        dgv.CurrentCell.Value = dgv.EditingControl.Text;
                    }
                }
                string id = dgv.Rows[e.RowIndex].Cells[0].Value.ToString();
                string name = Replace(dgv.Rows[e.RowIndex].Cells[1].Value.ToString());
                string price = dgv.Rows[e.RowIndex].Cells[2].Value.ToString().Replace(",","");
                //check empty
                if (!CheckEmpty(name, price))
                {
                    return;
                }
                //check unique
                if (!CheckUnique(id,name))
                {
                    MessageBox.Show(string.Format("Product:{0} exists already",name),
                         "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    return;
                }
                //insert
                if (id == "")
                {
                    dgv.Rows[e.RowIndex].Cells[0].Value = SQLHelper.ExecuteScalar(SQLHelper.ConStr, CommandType.Text,
                        string.Format("insert into gtest values('{0}',{1});select SCOPE_IDENTITY();", name, price)).ToString();
                }
                //update
                else
                {
                    SQLHelper.ExecuteNonQuery(SQLHelper.ConStr, CommandType.Text,
                        string.Format("update gtest set name='{0}',price='{1}' where id={2}", name, price, id));
                }
            }
            else
            {
                firstLoad = false;
            }
        }
        /// <summary>
        /// for delete
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void dataGridView1_UserDeletingRow(object sender, DataGridViewRowCancelEventArgs e)
        {
            if (MessageBox.Show("Are your sure to delete this record?", "Warning", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning) == DialogResult.OK)
            {
                SQLHelper.ExecuteNonQuery(SQLHelper.ConStr, CommandType.Text, string.Format("delete gtest where id={0}", e.Row.Cells[0].Value));
                return;
            }
            e.Cancel = true;
        }
        #endregion
        #region common method
        /// <summary>
        /// chek empty;
        /// </summary>
        /// <param name="name"></param>
        /// <param name="price"></param>
        /// <returns></returns>
        private bool CheckEmpty(string name,string price)
        {
            if (Regex.IsMatch(name, @"^\s*$"))
            {
                MessageBox.Show("Name not allowed null", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }
            if (Regex.IsMatch(price, @"^\s*$"))
            {
                MessageBox.Show("Price not allowed null","Warning",  MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }
            return true;
        }
        /// <summary>
        /// should be dealt in database
        /// </summary>
        /// <returns></returns>
        private bool CheckUnique(string id,string name)
        {
            if (Convert.ToInt32(SQLHelper.ExecuteScalar(SQLHelper.ConStr, CommandType.Text,
                string.Format("select count(1) from gtest where id<>{0} and name='{1}'", id == "" ? "0" : id, name)
                )) > 0)
            {
                return false;
            }
            return true;
        }
        /// <summary>
        /// replace '
        /// </summary>
        /// <param name="str"></param>
        /// <returns></returns>
        private string Replace(string str)
        {
            return str.Replace("'", "''");
        }
        #endregion
    }
}