C#对数据库各种操作

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.Data.SqlClient;
using System.Configuration;

namespace TellCenter
{
public partial class customer : Form
{
string sqlstr = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
public customer()
{
InitializeComponent();
GetAll();
}

private void GetId()
{
using (SqlConnection sqlcnn = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn.CreateCommand())
{
sqlcnn.Open();
sqlcmm.CommandText = "select id from T_Clientele where ClientName=@name";
sqlcmm.Parameters.AddWithValue("@name", this.txtClientName.Text);
object a = sqlcmm.ExecuteScalar();
string str = Convert.ToString(a);
this.labId.Text = str;
}
}
}//获取客户姓名对应的id号

private void GetAll()
{
using (SqlConnection sqlcnn = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn.CreateCommand())
{
sqlcmm.CommandText = "select Tc.id,Tc.ClientName,Tc.ClientPhone,Tc.Email,Tc.ClientAddress,Tc.Code,Tc.CarNum,Tc.ClientNums,Tc.Cattype,Tc.ShopId,Tc.ShopDateTime,Tr.Suggest,Tr.Memo from T_Clientele Tc left outer join T_Record Tr on Tc.id=Tr.Clientid";
sqlcnn.Open();
DataSet ds = new DataSet();
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmm);
adapter.Fill(ds);
this.dataGridView1.DataSource = ds.Tables[0];
}
}
}//给dataGridview填充数据库中的数据
private void btnlook_Click(object sender, EventArgs e)
{

if (this.checkname.Checked)
{
if (this.txtname.Text.Trim()!="")
{
using (SqlConnection sqlcnn = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn.CreateCommand())
{
sqlcmm.CommandText = "select * from T_Clientele where ClientName=@name";
sqlcmm.Parameters.AddWithValue("@name", this.txtname.Text);
sqlcnn.Open();
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmm);
adapter.Fill(dt);
if (dt.Rows.Count > 1)
{
MessageBox.Show("对不起，您的姓名在本系统中有重复的，请您按照电话查找！");
}
else if (dt.Rows.Count <= 0)
{
MessageBox.Show("系统中没有您要查看的姓名！");
}
else
{
GetInfo(sqlcmm);

using (SqlCommand sqlcmm1 = sqlcnn.CreateCommand())
{
GetId();

sqlcmm1.CommandText = "select Suggest,Memo from T_Record where clientid=@id";
sqlcmm1.Parameters.AddWithValue("@id", Convert.ToInt32(this.labId.Text));
SqlDataReader reader1 = sqlcmm1.ExecuteReader();
if (reader1 != null)
{
while (reader1.Read())
{
this.txtSuggest.Text = reader1["Suggest"].ToString();
this.txtComment.Text = reader1["Memo"].ToString();
}
}
}
}
}
}
}
else
{
MessageBox.Show("请输入姓名！");
}
}
else if(this.checkphone.Checked)
{
using (SqlConnection sqlcnn=new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm=sqlcnn.CreateCommand())
{
sqlcmm.CommandText = "select * from T_Clientele where ClientPhone=@phone";
sqlcmm.Parameters.AddWithValue("@phone",this.txtphone.Text);
sqlcnn.Open();
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(sqlcmm);
adapter.Fill(dt);
if (dt.Rows.Count > 1)
{
MessageBox.Show("对不起，您的电话在本系统中有重复的，请咨询管理员！");
}
else if (dt.Rows.Count <=0)
{
MessageBox.Show("系统中没有您要查找的信息！");
}
else
{
GetInfo(sqlcmm);
}
}
}
}
else if (this.checkname.Checked && this.checkphone.Checked)
{
using (SqlConnection sqlcnn = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn.CreateCommand())
{
sqlcmm.CommandText = "select * from T_Clientele where ClientPhone=@phone";
sqlcmm.Parameters.AddWithValue("@phone", this.txtphone.Text);
sqlcnn.Open();
GetInfo(sqlcmm);
}
}
}
else
{
MessageBox.Show("请选择您按什么查询，姓名？电话？");
}
}//查看

private void GetInfo(SqlCommand sqlcmm)
{
SqlDataReader reader = sqlcmm.ExecuteReader();
if (reader != null)
{
while (reader.Read())
{
this.txtClientName.Text = reader["ClientName"].ToString();
this.txtClientPhone.Text = reader["ClientPhone"].ToString();
this.txtEmail.Text = reader["Email"].ToString();
this.txtClientAddress.Text = reader["ClientAddress"].ToString();
this.txtCode.Text = reader["Code"].ToString();
this.txtCarNum.Text = reader["CarNum"].ToString();
this.txtClientNums.Text = reader["ClientNums"].ToString();
this.txtCattype.Text = reader["Cattype"].ToString();
this.txtShopid.Text = reader["ShopId"].ToString();
this.dateTimePicker1.Text = reader["ShopDateTime"].ToString();
}
}
reader.Close();
}//给客户详细信息赋值

private void DelCus_Click(object sender, EventArgs e)
{
GetId();
using (SqlConnection sqlcnn=new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm=sqlcnn.CreateCommand())
{
sqlcmm.CommandText = "delete from T_Clientele where ClientName=@name";
sqlcnn.Open();
using (SqlCommand sqlcmm1=sqlcnn.CreateCommand())
{
sqlcmm1.CommandText = "delete from T_Record where Clientid=@id";
sqlcmm1.Parameters.AddWithValue("@id",this.labId.Text);
int i= sqlcmm1.ExecuteNonQuery();
if (i<1)
{
MessageBox.Show("删除失败！");
}
}
sqlcmm.Parameters.AddWithValue("@name",this.txtClientName.Text);

int n = sqlcmm.ExecuteNonQuery();
if (n>0)
{
MessageBox.Show("删除成功！");
ToNull();
}
else
{
MessageBox.Show("删除失败！");
}
}
}
}//删除

private void ToNull()
{
this.txtClientName.Text = null;
this.txtClientPhone.Text = null;
this.txtEmail.Text = null;
this.txtClientAddress.Text = null;
this.txtCode.Text = null;
this.txtCarNum.Text = null;
this.txtClientNums.Text = null;
this.txtCattype.Text = null;
this.txtShopid.Text = null;
this.txtSuggest.Text = null;
this.txtComment.Text = null;
}//清空数据

private void Addcus_Click(object sender, EventArgs e)
{
using (SqlConnection sqlcnn = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn.CreateCommand())
{
sqlcnn.Open();
sqlcmm.CommandText = "insert into T_Clientele values(@name,@phone,@Email,@address,@code,@CarNum,@ClientNums,@Cattype,@ShopId,@ShopDateTime)";
GetPams(sqlcmm);
int i = sqlcmm.ExecuteNonQuery();
if (i > 0)
{
MessageBox.Show("添加成功！");
}
else
{
MessageBox.Show("添加失败！");
}
}
using (SqlCommand sqlcmm1 = sqlcnn.CreateCommand())
{
using (SqlCommand sqlcmm2=sqlcnn.CreateCommand())
{
using (SqlCommand sqlcmm3=sqlcnn.CreateCommand())
{
sqlcmm3.CommandText = "select max(id) from T_Clientele";
object maxid= sqlcmm3.ExecuteScalar();
string maxid1 = Convert.ToString(maxid);
this.labId.Text = maxid1;
}
sqlcmm2.CommandText = "select id from T_user where username=@name";
sqlcmm2.Parameters.AddWithValue("@name",Class1.Username);
object a = sqlcmm2.ExecuteScalar();
int id = Convert.ToInt32(a);
sqlcmm1.CommandText = "insert into T_Record(OperPerson,Clientid,Suggest,Memo) values(@person,@clientid,@suggest,@memo)";
sqlcmm1.Parameters.AddWithValue("@person",id);
sqlcmm1.Parameters.AddWithValue("@clientid", Convert.ToInt32(this.labId.Text));
sqlcmm1.Parameters.AddWithValue("@suggest", this.txtSuggest.Text);
sqlcmm1.Parameters.AddWithValue("@memo", this.txtComment.Text);
sqlcmm1.ExecuteNonQuery();
ToNull();
}
}
}
}//添加

private void GetPams(SqlCommand sqlcmm)
{
sqlcmm.Parameters.AddWithValue("@name", this.txtClientName.Text);
sqlcmm.Parameters.AddWithValue("@phone", this.txtClientPhone.Text);
sqlcmm.Parameters.AddWithValue("@Email", this.txtEmail.Text);
sqlcmm.Parameters.AddWithValue("@address", this.txtClientAddress.Text);
sqlcmm.Parameters.AddWithValue("@code", this.txtCode.Text);
sqlcmm.Parameters.AddWithValue("@CarNum", this.txtCarNum.Text);
sqlcmm.Parameters.AddWithValue("@ClientNums", this.txtClientNums.Text);
sqlcmm.Parameters.AddWithValue("@Cattype", this.txtCattype.Text);
sqlcmm.Parameters.AddWithValue("@ShopId", this.txtShopid.Text);
sqlcmm.Parameters.AddWithValue("@ShopDateTime", this.dateTimePicker1.Text);
}//填充占位符

private void btnUpdate_Click(object sender, EventArgs e)
{
using (SqlConnection sqlcnn=new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm=sqlcnn.CreateCommand())
{
sqlcmm.CommandText = "update T_Clientele set ClientPhone=@phone,Email=@Email,ClientAddress=@address,Code=@code,CarNum=@CarNum,ClientNums=@ClientNums,Cattype=@Cattype,ShopId=@ShopId,ShopDateTime=@ShopDateTime where ClientName=@name";
sqlcnn.Open();
GetPams(sqlcmm);
sqlcmm.ExecuteNonQuery();
//int i = sqlcmm.ExecuteNonQuery();
//if (i>0)
//{
// MessageBox.Show("更改成功！");
//}
//else
//{
// MessageBox.Show("更改失败！");
//}
}
using (SqlCommand sqlcmm1=sqlcnn.CreateCommand())
{
GetId();
sqlcmm1.CommandText = "update T_Record set Suggest=@suggest,Memo=@memo where Clientid=@id";
sqlcmm1.Parameters.AddWithValue("@id",Convert.ToInt32(this.labId.Text));
sqlcmm1.Parameters.AddWithValue("@suggest",this.txtSuggest.Text);
sqlcmm1.Parameters.AddWithValue("@memo",this.txtComment.Text);
int s= sqlcmm1.ExecuteNonQuery();
if (s > 0)
{
MessageBox.Show("更改成功！");
}
else
{
MessageBox.Show("更改失败！");
}
}
}
}//更改
private void dataGridView1_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
{
if (this.dataGridView1.CurrentRow.Cells[0].Selected)
{
tabPage1.Show();
}

}//双击显示另一选项卡
private void btn_add_Click(object sender, EventArgs e)
{
using (SqlConnection sqlcnn = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn.CreateCommand())
{
sqlcmm.CommandText = "insert into T_Clientele(ClientName,ClientPhone,Email,ClientAddress,Code,CarNum,ClientNums,Cattype,ShopId,ShopDateTime) values(@name,@phone,@Email,@address,@code,@CarNum,@ClientNums,@Cattype,@ShopId,@ShopDateTime)";
sqlcnn.Open();
getCellValue(sqlcmm);
int i = sqlcmm.ExecuteNonQuery();
if (i > 0)
{
MessageBox.Show("添加成功！");
}
else
{
MessageBox.Show("添加失败！");
}
}
using (SqlCommand sqlcmm1 = sqlcnn.CreateCommand())
{
using (SqlCommand sqlcmm2 = sqlcnn.CreateCommand())
{
using (SqlCommand sqlcmm3 = sqlcnn.CreateCommand())
{
sqlcmm3.CommandText = "select max(id) from T_Clientele";
object maxid = sqlcmm3.ExecuteScalar();
string maxid1 = Convert.ToString(maxid);
this.labId.Text = maxid1;
}
sqlcmm2.CommandText = "select id from T_user where username=@name";
sqlcmm2.Parameters.AddWithValue("@name", Class1.Username);
object a = sqlcmm2.ExecuteScalar();
int id = Convert.ToInt32(a);
sqlcmm1.CommandText = "insert into T_Record(OperPerson,Clientid,Suggest,Memo) values(@person,@clientid,@suggest,@memo)";
sqlcmm1.Parameters.AddWithValue("@person", id);
sqlcmm1.Parameters.AddWithValue("@clientid", Convert.ToInt32(this.labId.Text));
sqlcmm1.Parameters.AddWithValue("@suggest", this.dataGridView1.CurrentRow.Cells[11].Value.ToString());
sqlcmm1.Parameters.AddWithValue("@memo", this.dataGridView1.CurrentRow.Cells[12].Value.ToString());
sqlcmm1.ExecuteNonQuery();
}
}
}
}//添加（dataGridview）

private void getCellValue(SqlCommand sqlcmm)
{
sqlcmm.Parameters.AddWithValue("@id", this.dataGridView1.CurrentRow.Cells[0].Value.ToString());
sqlcmm.Parameters.AddWithValue("@name", this.dataGridView1.CurrentRow.Cells[1].Value.ToString());
sqlcmm.Parameters.AddWithValue("@phone", this.dataGridView1.CurrentRow.Cells[2].Value.ToString());
sqlcmm.Parameters.AddWithValue("@Email", this.dataGridView1.CurrentRow.Cells[3].Value.ToString());
sqlcmm.Parameters.AddWithValue("@address", this.dataGridView1.CurrentRow.Cells[4].Value.ToString());
sqlcmm.Parameters.AddWithValue("@code", this.dataGridView1.CurrentRow.Cells[5].Value.ToString());
sqlcmm.Parameters.AddWithValue("@CarNum", this.dataGridView1.CurrentRow.Cells[6].Value.ToString());
sqlcmm.Parameters.AddWithValue("@ClientNums", this.dataGridView1.CurrentRow.Cells[7].Value.ToString());
sqlcmm.Parameters.AddWithValue("@Cattype", this.dataGridView1.CurrentRow.Cells[8].Value.ToString());
sqlcmm.Parameters.AddWithValue("@ShopId", this.dataGridView1.CurrentRow.Cells[9].Value.ToString());
sqlcmm.Parameters.AddWithValue("@ShopDateTime", this.dataGridView1.CurrentRow.Cells[10].Value.ToString());

}//获取dataGridview中选中行的单元格的值

private void btn_update_Click(object sender, EventArgs e)
{
using (SqlConnection sqlcnn = new SqlConnection(sqlstr))
{
using (SqlCommand sqlcmm = sqlcnn.CreateCommand())
{
string strcmm = "update T_Clientele set ClientName=@name,ClientPhone=@phone,Email=@Email,ClientAddress=@address,Code=@code,CarNum=@CarNum,ClientNums=@ClientNums,Cattype=@Cattype,ShopId=@ShopId,ShopDateTime=@ShopDateTime where id=@id";
sqlcmm.CommandText = strcmm;
sqlcnn.Open();
getCellValue(sqlcmm);
int i = sqlcmm.ExecuteNonQuery();
//if (i>0)
//{
// MessageBox.Show("修改成功！");
//}
//else
if (i == 0)
{
MessageBox.Show("修改失败！");
}
}
using (SqlCommand sqlcmm1 = sqlcnn.CreateCommand())
{
sqlcmm1.CommandText = "update T_Record set Suggest=@suggest,Memo=@memo where Clientid=@id";
sqlcmm1.Parameters.AddWithValue("@id",this.dataGridView1.CurrentRow.Cells[0].Value.ToString());
sqlcmm1.Parameters.AddWithValue("@suggest", this.dataGridView1.CurrentRow.Cells[11].Value.ToString());
sqlcmm1.Parameters.AddWithValue("@memo", this.dataGridView1.CurrentRow.Cells[12].Value.ToString());
int s = sqlcmm1.ExecuteNonQuery();
if (s > 0)
{
MessageBox.Show("更改成功！");
}
else
{
MessageBox.Show("更改失败！");
}
}

}
}//更改（dataGridview）

private void refresh_Click(object sender, EventArgs e)
{
GetAll();
}//刷新
}
}