C#操作sql server数据库实例源代码
1.实现的界面
2.要导出的命名空间
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.sqlClient;
using System.Configuration;
3.app.config配置
4.窗口load事件的实现
此功能和刷新按钮的功能是一样的.
private void frmclientinfo_Load(object sender, EventArgs e)
{
try
{
string sql;
string strcon = ConfigurationManager.ConnectionStrings["MingXinERP.Properties.Settings.mingxindataConnectionString1"].ToString();
sqlConnection con = new SqlConnection(strcon);
con.Open();
sql = "select top 12 * from (select top 12 * from 客户信息 order by 编号 desc) a order by 编号 asc";//显示最后的12条记录
SqlDataAdapter da = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
da.Fill(ds, "abc");
dataGridView1.DataSource = ds.Tables["abc"].DefaultView;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
5. dataGridView_CellClick的实现
是单击dataGridView单元格所触发的事件.
private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
{
txtid.Text = Convert.ToString(dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value);
txtname.Text = Convert.ToString(dataGridView1[1, dataGridView1.CurrentCell.RowIndex].Value);
cbotype.Text = Convert.ToString(dataGridView1[2, dataGridView1.CurrentCell.RowIndex].Value);
cbofrom.Text = Convert.ToString(dataGridView1[3, dataGridView1.CurrentCell.RowIndex].Value);
cbokind.Text = Convert.ToString(dataGridView1[4, dataGridView1.CurrentCell.RowIndex].Value);
cboarea.Text = Convert.ToString(dataGridView1[5, dataGridView1.CurrentCell.RowIndex].Value);
txtcellphone.Text=Convert.ToString(dataGridView1[6, dataGridView1.CurrentCell.RowIndex].Value);
txtfax.Text = Convert.ToString(dataGridView1[7, dataGridView1.CurrentCell.RowIndex].Value);
txtpost.Text = Convert.ToString(dataGridView1[8, dataGridView1.CurrentCell.RowIndex].Value);
txtaddress.Text =Convert.ToString(dataGridView1[9, dataGridView1.CurrentCell.RowIndex].Value);
txtqq.Text = Convert.ToString(dataGridView1[10, dataGridView1.CurrentCell.RowIndex].Value); txtemail.Text = Convert.ToString(dataGridView1[11, dataGridView1.CurrentCell.RowIndex].Value);
txtremark.Text=Convert.ToString(dataGridView1[13, dataGridView1.CurrentCell.RowIndex].Value);
}
6.添加按钮事件代码
private void toolStripButton2_Click(object sender, EventArgs e)
{
#region 添加
#region 1.初始化
if (txtname.Text == "")
{
MessageBox.Show("请输入客户名称");
return;
}
if (txtcellphone .Text == "")
{
MessageBox.Show("请输入联系电话");
return;
}
#endregion
try
{
string sql;
string count;
string strcon = ConfigurationManager.ConnectionStrings["MingXinERP.Properties.Settings.mingxindataConnectionString1"].ToString();
sqlConnection con = new SqlConnection(strcon);
con.Open();
#region 3.检测是否存在
try
{
sql = "select count(*) from 客户信息 where 客户名称='" + txtname.Text + "' and 联系电话='" + txtcellphone .Text + "'";
SqlCommand cmd6 = new SqlCommand(sql, con);
string count6 = cmd6.ExecuteScalar().ToString();
if (count6 == "1")
{
MessageBox.Show("已存在该条记录了");
return;
}
}
catch
{
}
#endregion
#region 2.编号
try
{
sql = "select max(编号) from 客户信息";
SqlCommand cmd2 = new SqlCommand(sql, con);
count = cmd2.ExecuteScalar().ToString();
}
catch
{
count = "0";
}
#endregion
count = Convert.ToString(Convert.ToInt32(count) + 1);
sql = "insert into 客户信息(编号,客户名称,客户级别,客户来源,所属行业,所在区域,联系电话,传真号码,邮政编码,联系地址,qq号码,email地址,记录添加时间,备注) values('" + count + "','" + txtname.Text +
"','" +cbotype .Text + "','" + cbofrom .Text +"','"+cbokind .Text +"','"+cboarea .Text +"','"+txtcellphone .Text +"','"+txtfax .Text +"','"+txtpost .Text +"','"+txtaddress .Text +
"','"+txtqq .Text +"','"+txtemail .Text + "','" + DateTime.Now.ToString() + "','" + txtremark.Text + "')";
sqlCommand cmd = new SqlCommand(sql, con);
string count5 = cmd.ExecuteNonQuery().ToString();
if (count5 == "1")
{
MessageBox.Show("记录添加成功");
}
else
{
MessageBox.Show("记录添加失败");
}
}
catch
{
#region 2.
为空时异常添加
try
{
String strcon=ConfigurationManager.ConnectionStrings["MingXinERP.Properties.Settings.mingxindataConnectionString1"].ToString();
SqlConnection con = new SqlConnection(strcon);
string sql;
con.Open();
sql = "insert into 客户信息(编号,客户名称,客户级别,客户来源,所属行业,所在区域,联系电话,传真号码,邮政编码,联系地址,qq号码,email地址,记录添加时间,备注) values('" +"1"+ "','" + txtname.Text + "','" + cbotype.Text + "','" + cbofrom.Text + "','" + cbokind.Text + "','" + cboarea.Text + "','" + txtcellphone.Text + "','" + txtfax.Text + "','" + txtpost.Text + "','" + txtaddress.Text +"','" + txtqq.Text + "','" + txtemail.Text + "','" + DateTime.Now.ToString() + "','" + txtremark.Text + "')";
SqlCommand cmd = new SqlCommand(sql, con);
string count5 = cmd.ExecuteNonQuery().ToString();
if (count5 == "1")
{
MessageBox.Show("记录添加成功");
}
else
{
MessageBox.Show("记录添加失败");
}
}
catch (Exception ex)
{
MessageBox.Show("记录添加失败,原因如下:" + ex.Message);
}
#endregion
}
frmclientinfo_Load(sender, e);
#endregion
}
7.修改按钮的事件代码
private void toolStripButton3_Click(object sender, EventArgs e)
{
#region 修改
#region 1.初始化
if (txtid.Text == "")
{
MessageBox.Show("要求有编号");
return;
}
if (txtname.Text == "")
{
MessageBox.Show("请输入客户名称");
return;
}
if (txtcellphone .Text == "")
{
MessageBox.Show("请输入联系电话");
return;
}
#endregion
try
{
string sql;
string strcon= ConfigurationManager.ConnectionStrings["MingXinERP.Properties.Settings.mingxindataConnectionString1"].ToString();
SqlConnection con = new SqlConnection(strcon);
con.Open();
sql = "update 客户信息 set 客户名称='" + txtname.Text + "',客户级别='" + cbotype .Text + "',客户来源='" + cbofrom .Text +"',所属行业='"+cbokind .Text +"',所在区域='"+cboarea .Text +"',联系电话='"+txtcellphone .Text +"',传真号码='"+txtfax .Text +"',邮政编码='"+txtpost .Text +"',联系地址='"+txtaddress .Text +"',qq号码='"+txtqq .Text +"',email地址='"+txtemail .Text +"',备注='" + txtremark.Text +
"' where 编号='" + txtid.Text + "'";
SqlCommand cmd = new SqlCommand(sql, con);
string count5 = cmd.ExecuteNonQuery().ToString();