文章存在优化问题,及删除问题,请高手指点~~
/*
* Created by SharpDevelop.
* User:
* Date: 2009-9-22
* Time: 14:51
*
* To change this template use Tools | Options | Coding | Edit Standard Headers.
*/
using System;
using System.Collections.Generic;
using System.Drawing;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
namespace DataGridViewTest
{
/// <summary>
/// Description of MainForm.
/// </summary>
///
public partial class MainForm
{
[STAThread]
public static void Main(string[] args)
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new MainForm());
}
public MainForm()
{
//
// The InitializeComponent() call is required for Windows Forms designer support.
//
InitializeComponent();
//
// TODO: Add constructor code after the InitializeComponent() call.
//
}
private SqlConnection con;
private SqlDataAdapter adapter;
private DataSet dataset;
private string product_id;
private SqlCommandBuilder buider;
private SqlCommand cmd;
//连接数据库自定义函数
private void OpenConnection()
{
try
{
con=new SqlConnection();
con.ConnectionString = @"server = (local);database=lianxie;user id = sa;pwd=p9pip";
con.Open();
}
catch(SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
//关闭数据库自定义函数
private void CloseConnection()
{
if(this.con!=null && con.State!=System.Data.ConnectionState.Closed)
{
this.con.Close();
}
}
private int MaxID()
{
int count = 0;
//首先连接数据库、然后再写SQL语句、定义一个Command对象
//用Command对象执行语句、关闭数据库
OpenConnection();
string SqlStr = "select max(id) from market ";
SqlCommand cmd = new SqlCommand(SqlStr,con);
count=Convert.ToInt32(cmd.ExecuteScalar().ToString())+1;
CloseConnection();
return count;
}
//根据指定的ID删除,自定义删除函数
private int deleteProduct(string id)
{
int count = 0;
//首先连接数据库、然后再写SQL语句、定义一个Command对象
//用Command对象执行语句、关闭数据库
OpenConnection();
string SqlStr = "delete from market where id=" + id;
cmd = new SqlCommand(SqlStr,con);
count = cmd.ExecuteNonQuery();
//count = Convert.ToInt32(cmd.ExecuteScalar().ToString());
CloseConnection();
return count;
}
//添加函数
private void addProduct()
{
//首先连接数据库、然后再写SQL语句、定义一个Command对象
//用Command对象执行语句、关闭数据库
OpenConnection();
string SqlStr = "insert into market(product_name,barcode,amount,price) values ('"
+this.text_name.Text.Trim()
+"','"+ this.text_bar.Text.Trim()+"','"+this.text_amount.Text.Trim()
+"','"+this.text_price.Text.Trim()+"')";
SqlCommand cmd = new SqlCommand(SqlStr,con);
cmd.ExecuteNonQuery();
// count = Convert.ToInt32(cmd.ExecuteScalar().ToString());
CloseConnection();
}
//绑定
public void bind()
{
OpenConnection();
try
{
string strSql = "SELECT id,product_name,barcode,amount,price FROM MARKET ";
adapter = new SqlDataAdapter(strSql, con);
dataset = new DataSet();
adapter.Fill(dataset,"market_test");//填出数据集记录到dataadapter 适配器里
if (dataset.Tables[0].Rows.Count > 0)
{
//dataGridView1 里添加数据
dataGridView1.DataSource = dataset;
dataGridView1.DataMember = "market_test";
//标题头设置
// dataGridView1.Columns[0].HeaderText = "商品名字";
// dataGridView1.Columns[1].HeaderText = "货架";
// dataGridView1.Columns[2].HeaderText = "总数";
// dataGridView1.Columns[3].HeaderText = "价格";
}
else
MessageBox.Show("没有您要查询的信息,请修改查询条件");
}
catch(SqlException ex)
{
MessageBox.Show(ex.Message);
}
}
//只可以输入数字函数
private void OnlyWriteNumber(object sender, KeyPressEventArgs e)
{
TextBox txt = (TextBox)sender;
if (e.KeyChar == 8)
{
return;
}
if (e.KeyChar == 46 && txt.Text.IndexOf('.') == -1)
{
return;
}
if (e.KeyChar > 57 || e.KeyChar < 48)
{
e.Handled = true;
this.Focus();
}
}
void MainFormLoad(object sender, System.EventArgs e)
{
// bind();
}
//是否保存按钮
void But_saveClick(object sender, System.EventArgs e)
{
DialogResult answer = MessageBox.Show("是否确定保存?",
"保存",MessageBoxButtons.YesNo,MessageBoxIcon.Question);
if(answer == DialogResult.Yes)
{
buider = new SqlCommandBuilder(this.adapter);
int count = this.adapter.Update(this.dataset,"market_test");//影响的行数
MessageBox.Show("影响的行数:"+ count.ToString());
}
}
//显示数据
void But_showClick(object sender, System.EventArgs e)
{
bind();
this.but_addNew.Enabled=true;
this.but_delete.Enabled=true;
this.but_save.Enabled=true;
}
//删除单行
void But_deleteClick(object sender, System.EventArgs e)
{
DialogResult answer = MessageBox.Show("是否确定要删除吗?",
"删除操作",MessageBoxButtons.YesNo);
if(answer == DialogResult.Yes)
{
int count = this.deleteProduct(product_id);
MessageBox.Show("删除的行数:"+ count.ToString());
}
}
void DataGridView1Click(object sender, System.EventArgs e)
{
//第一种方法:通过指定的ID删除,点击"删除单行"
/*
MessageBox.Show(this.dataGridView1.SelectedCells[0].ColumnIndex.ToString() ); //返回选择列的最后一列的序号
int rowindex = this.dataGridView1.SelectedCells[0].RowIndex; //返回行的序号,[0]是选择行的最后一行
MessageBox.Show(this.dataGridView1.Rows[rowindex].Cells[1].Value.ToString());//获得选种行的第一列的值
MessageBox.Show(this.dataGridView1.SelectedRows.Count.ToString());//返回选择的行数,必须整行选中
MessageBox.Show(this.dataGridView1.SelectedCells.Count.ToString());//返回选择的单元格的个数
if(this.dataGridView1.SelectedCells.Count>=0)
{
int rowindex = this.dataGridView1.SelectedCells[0].RowIndex;
product_id = this.dataGridView1.Rows[rowindex].Cells[1].Value.ToString();
}
*/
}
void DataGridView1CellClick(object sender, System.Windows.Forms.DataGridViewCellEventArgs e)
{
//第二种方法:通过单元格CellClick删除事件,点击"删除单行"
product_id = this.dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();//取出对应的ID
#region
/*
//第三种方法:通过单元格CellClick删除事件,点击"删除本行"
if(e.ColumnIndex==0 && e.RowIndex>=0)
{
product_id = this.dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();//取出对应的ID
MessageBox.Show(product_id.ToString());
DialogResult answer = MessageBox.Show("是否确定要删除吗?",
"删除操作",MessageBoxButtons.YesNo);
if(answer == DialogResult.Yes)
{
int count = this.deleteProduct(product_id);
MessageBox.Show("删除的行数:"+ count.ToString());
}
}
*/
#endregion
}
//添加记录
void But_addClick(object sender, System.EventArgs e)
{
if(this.text_name.Text.Trim()!="" && this.text_bar.Text.Trim()!=""
&& this.text_amount.Text.Trim()!="" && this.text_price.Text.Trim()!="")
{
DialogResult answer = MessageBox.Show("是否确定要添加?",
"添加",MessageBoxButtons.YesNo,MessageBoxIcon.Question);
if(answer == DialogResult.Yes)
{
addProduct();
}
}
else
MessageBox.Show("填写数据不完整,不可以为空1");
}
void Text_nameKeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
{
//允许输入的字符
//「BackSpace」「Delete」后退键正常删除操作
if ((Keys)(e.KeyChar) == Keys.Back || (Keys)(e.KeyChar) == Keys.Delete)
{
return;
}
//「Ctrl+C」(3)「Ctrl+X」(24)特殊组合键正常
//「Ctrl+Z」(26) 撤消组合键正常
if ((e.KeyChar == 3) || (e.KeyChar == 24) || (e.KeyChar == 26))
{
return;
}
}
//只可以输入数字
void Text_barKeyPress(object sender, System.Windows.Forms.KeyPressEventArgs e)
{
#region
/* 方法一:只可以输入数字
string AstrictChar = "0123456789,";
if (AstrictChar.IndexOf(e.KeyChar.ToString()) == -1)
{
e.Handled = true;
return;
}
*/
#endregion
// 方法二: 只允许输入数字
this.OnlyWriteNumber( sender, e);
}
void But_addNewClick(object sender, System.EventArgs e)
{
DataRow datarow = dataset.Tables[0].NewRow();
#region
/*
//方法一:给绑定的DataGridView新增一行,再添加数据。
for (int i = 0; i < dataset.Tables[0].Rows.Count; i++)
{
datarow[i]=dataset.Tables[0].Rows[0][0].ToString();
}
*/
#endregion
// -----------具体值添加---------
//方法二:先把数据添加到数据源里面,再重新绑定。
datarow["id"]= this.MaxID();//因为字段ID不可以为空
// datarow["product_name"]="dd";
// datarow["barcode"]="22";
// datarow["amount"]="33";
// datarow["price"]="33";
dataset.Tables[0].Rows.Add(datarow);
dataGridView1.DataSource = dataset.Tables[0];
}
//删除多行
void But_deleteSumClick(object sender, System.EventArgs e)
{
try
{
int j = dataGridView1.SelectedRows.Count;
OpenConnection();
if (j>0)
{
DialogResult answer= MessageBox.Show("您确认要删除这" + Convert.ToString(j) + "项吗?",
"系统提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
if (answer == DialogResult.Yes)//给出提示
{
#region
/*
//方法1:
for (int i = 0; i<j; i++)
{
dataGridView1.Rows.Remove(dataGridView1.SelectedRows[0]);
}//以上删除只是从前台删除,后台没有删除
//删除后台数据不知道怎么写
// ??????????????????这里的代码不知道怎么写了 请大家帮忙写出来好吗?
*/
#endregion
#region
/*
//this.dataGridView1.DataSource = this.dataset.Tables[0];
//SqlCommandBuilder cmdbuilder = new SqlCommandBuilder(adapter);
//adapter.Fill(dataset);
//adapter.UpdateCommand = cmdbuilder.GetUpdateCommand();
//adapter.Update(dataset,dataset.Tables[0].TableName);
*/
#endregion
#region
//方法2:正解
for (int i = 0; i < j; i++)
{
string ID = this.dataGridView1.Rows[this.dataGridView1.SelectedRows[i].Index].Cells[1].Value.ToString();
string strDelete = "Delete from market where ID=" + ID ;
cmd = new SqlCommand(strDelete,con);
cmd.ExecuteNonQuery();
}
#endregion
#region
/*
//方法3:定义数组,用循环赋值
String[] array = new String[j];
for (int i = 0; i < j; i++)
{
String ID = this.dataGridView1.Rows[this.dataGridView1.SelectedRows[i].Index].Cells[1].Value.ToString();
string strDelete = "Delete from market where ID=" + ID;
array[i] = strDelete;
}
//遍历数组
foreach (String str in array)
{
// this.Update(str);
cmd = new SqlCommand(str,con);
cmd.ExecuteNonQuery();
}
*/
#endregion
MessageBox.Show("删除成功", "提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
CloseConnection();
//这里写刷新的方法
}
catch (SqlException Sex)
{
MessageBox.Show(Sex.Message);
}
}
}
}