c#数据库编程常用代码示例.doc

C#连接sql2000示范代码/定义连接字符串 string strConnection = user id=sa;password=sd;initial catalog=Tele_Sale;Server=6;Connect Timeout=30;/用连接字符串创建一个数据库连接对象SqlConnection objConnection = new SqlConnection(strConnection);/使用数据库连接对象连接数据库objConnection.Open();/基于数据库连接对象创建一个sql命令SqlCommand SqlCmd = new SqlCommand(select * from MissInfo where MissID=234, objConnection);/创建一个数据阅读器并从sql命令中读出数据SqlDataReader DataReader = SqlCmd.ExecuteReader();/使用数据阅读器中的字段内容DataReader.Read();MessageBox.Show( DataReader0.ToString() );MessageBox.Show( DataReader1.ToString() );MessageBox.Show( DataReader2.ToString() ); /关闭数据库连接objConnection.Close(); C#执行SQL命令（无返回值）示例代码（常用的Sql命令 insertdeleteupdate都是无返回值的，本例以update为例）/定义连接字符串 string strConnection = Server=6;initial catalog=Tele_Sale;user id=sa;password=sd;Connect Timeout=30;/用连接字符串创建一个数据库连接对象SqlConnection objConnection = new SqlConnection(strConnection);/使用数据库连接对象连接数据库objConnection.Open();/基于数据库连接对象创建一个sql命令SqlCommand SqlCmd = new SqlCommand(update MissInfo set MissName=技术主管 where MissID=234, objConnection);/执行不返回结果的sql命令SqlCmd.ExecuteNonQuery();/关闭数据库连接objConnection.Close();C#执行返回结果的SQL语句（select语句），并将结果显示到DataGridView说明：本例中的dgv 是DataGridView控件,本你一并示范了在查询时把用户输入的工号拼接进了sql字符串。 private void button1_Click(object sender, EventArgs e) if (txt_User.Text.Trim() = ) MessageBox.Show(请输入查询工号); txt_User.Focus(); return; /定义连接字符串 /string strConnection = Server=6;initial catalog=Tele_Sale;user id=sa;password=sd;Connect Timeout=30; string strConnection = Server=6;database=Tele_Sale;uid=sa;pwd=sd; /用连接字符串创建一个数据库连接对象 SqlConnection Conn = new SqlConnection(strConnection); /打开数据库连接 Conn.Open(); /数据适配器( SqlDataadapter 的作用是实现 DataSet 和 DB 之间的桥梁 ) SqlDataAdapter MyAdapter = new SqlDataAdapter(select * from MissInfo where MissID=+txt_User.Text.Trim()+, Conn); /SqlDataAdapter MyAdapter = new SqlDataAdapter(select * from MissInfo , Conn); /创建数据记录集( 可以把DataSet当成内存中的数据库，DataSet是不依赖于数据库的独立数据集合。) DataSet MyDataSet = new DataSet(); /将数据适配器中的数据填充到数据记录集 MyAdapter.Fill(MyDataSet, MyTable); /指定数据表格控件的数据源记录集 dgv.DataSource = MyDataSet; /指定数据表格控件的数据源表名 dgv.DataMember = MyTable; /关闭数据库连接 Conn.Close(); C#将Excel作数据源(需在项目上“添加引用” “Microsoft Office Excel”)private void btn_Import_Click(object sender, EventArgs e) string strConn; / IMEX=1 可把混合型作为文本型读取，避免null值 strConn = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Test.xls;Extended Properties=Excel 8.0;HDR=False;IMEX=1; OleDbConnection OleConn = new OleDbConnection(strConn); OleConn.Open(); String sql = SELECT * FROM Sheet1$; OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn); DataSet ds = new DataSet(); OleDaExcel.Fill(ds, MyTable);/MyTable是自定义的别名 dgv.DataSource = ds; dgv.DataMember = MyTable; OleConn.Close();实战练习一：C# 登录模块示例代码登录窗体详细设置（在InitializeComponent();处击右键，选择“转到定义”获取的代码)：private void InitializeComponent() this.label1 = new System.Windows.Forms.Label(); this.label2 = new System.Windows.Forms.Label(); this.txt_User = new System.Windows.Forms.TextBox(); this.txt_Pwd = new System.Windows.Forms.TextBox(); this.btn_Login = new System.Windows.Forms.Button(); this.btn_Exit = new System.Windows.Forms.Button(); this.SuspendLayout(); / / label1 / this.label1.AutoSize = true; this.label1.Location = new System.Drawing.Point(35, 36); this.label1.Name = label1; this.label1.Size = new System.Drawing.Size(41, 12); this.label1.TabIndex = 0; this.label1.Text = 工号：; / / label2 / this.label2.AutoSize = true; this.label2.Location = new System.Drawing.Point(35, 90); this.label2.Name = label2; this.label2.Size = new System.Drawing.Size(41, 12); this.label2.TabIndex = 1; this.label2.Text = 密码：; / / txt_User / this.txt_User.Location = new System.Drawing.Point(91, 33); this.txt_User.Name = txt_User; this.txt_User.Size = new System.Drawing.Size(100, 21); this.txt_User.TabIndex = 2; / / txt_Pwd / this.txt_Pwd.Location = new System.Drawing.Point(91, 87); this.txt_Pwd.Name = txt_Pwd; this.txt_Pwd.PasswordChar = *; this.txt_Pwd.Size = new System.Drawing.Size(100, 21); this.txt_Pwd.TabIndex = 3; / / btn_Login / this.btn_Login.Location = new System.Drawing.Point(37, 133); this.btn_Login.Name = btn_Login; this.btn_Login.Size = new System.Drawing.Size(61, 23); this.btn_Login.TabIndex = 4; this.btn_Login.Text = 登录; this.btn_Login.UseVisualStyleBackColor = true; this.btn_Login.Click += new System.EventHandler(this.btn_Login_Click); / / btn_Exit / this.btn_Exit.Location = new System.Drawing.Point(137, 133); this.btn_Exit.Name = btn_Exit; this.btn_Exit.Size = new System.Drawing.Size(54, 23); this.btn_Exit.TabIndex = 5; this.btn_Exit.Text = 退出; this.btn_Exit.UseVisualStyleBackColor = true; this.btn_Exit.Click += new System.EventHandler(this.btn_Exit_Click); / / Form1 / this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F); this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font; this.ClientSize = new System.Drawing.Size(237, 186); this.Controls.Add(this.btn_Exit); this.Controls.Add(this.btn_Login); this.Controls.Add(this.txt_Pwd); this.Controls.Add(this.txt_User); this.Controls.Add(this.label2); this.Controls.Add(this.label1); this.Name = Form1; this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen; this.Text = 系统登录; this.Load += new System.EventHandler(this.Form1_Load); this.ResumeLayout(false); this.PerformLayout(); “登录”按钮源码：private void btn_Login_Click(object sender, EventArgs e) /检查工号是否为空 if (txt_User.Text = ) MessageBox.Show(请先输入工号, 系统提示); txt_User.Focus(); return; else if (txt_Pwd.Text = ) MessageBox.Show(请输入密码, 系统提示); txt_Pwd.Focus(); return; /定义连接字符串 string strConnection = Server=6;initial catalog=Tele_Sale;user id=sa;password=sd;Connect Timeout=30; /用连接字符串创建一个数据库连接对象 SqlConnection objConnection = new SqlConnection(strConnection); /使用数据库连接对象连接数据库 objConnection.Open(); /基于数据库连接对象创建一个sql命令 SqlCommand SqlCmd = new SqlCommand(select * from MissInfo where MissID=+txt_User.Text+, objConnection); /创建一个数据阅读器并从sql命令中读出数据 SqlDataReader DataReader = SqlCmd.ExecuteReader(); /使用数据阅读器中的字段内容 if (DataReader.Read() /读取密码 if (DataReader2.ToString() != txt_Pwd.Text) MessageBox.Show(对不起，您输入的工号或密码错误！, 系统提示); else /MessageBox.Show(欢迎使用营销管理系统!, 系统提示); /打开主程序界面 this.Hide(); Form2 f2 = new Form2(txt_User.Text); f2.ShowDialog(); this.Close(); else MessageBox.Show(该工号不存在, 系统提示); /关闭数据库连接及数据阅读器 objConnection.Close(); DataReader.Close(); “退出”按钮源码： private void btn_Exit_Click(object sender, EventArgs e) this.Close(); 实战练习二：“密码修改”模块示例代码：“密码修改”窗体详细设置（在InitializeComponent();处击右键，选择“转到定义”获取的代码)：private void InitializeComponent() this.label1 = new System.Windows.Forms.Label(); this.label2 = new System.Windows.Forms.Label(); this.label3 = new System.Windows.Forms.Label(); this.txt_OldPwd = new System.Windows.Forms.TextBox(); this.txt_NewPwd = new System.Windows.Forms.TextBox(); this.txt_ConfirmPwd = new System.Windows.Forms.TextBox(); this.btn_OK = new System.Windows.Forms.Button(); this.SuspendLayout(); / / label1 / this.label1.AutoSize = true; this.label1.Location = new System.Drawing.Point(17, 15); this.label1.Name = label1; this.label1.Size = new System.Drawing.Size(53, 12); this.label1.TabIndex = 0; this.label1.Text = 旧密码：; / / label2 / this.label2.AutoSize = true; this.label2.Location = new System.Drawing.Point(17, 51); this.label2.Name = label2; this.label2.Size = new System.Drawing.Size(53, 12); this.label2.TabIndex = 1; this.label2.Text = 新密码：; / / label3 / this.label3.AutoSize = true; this.label3.Location = new System.Drawing.Point(17, 87); this.label3.Name = label3; this.label3.Size = new System.Drawing.Size(65, 12); this.label3.TabIndex = 2; this.label3.Text = 密码确认：; / / txt_OldPwd / this.txt_OldPwd.Location = new System.Drawing.Point(99, 12); this.txt_OldPwd.Name = txt_OldPwd; this.txt_OldPwd.PasswordChar = *; this.txt_OldPwd.Size = new System.Drawing.Size(100, 21); this.txt_OldPwd.TabIndex = 3; / / txt_NewPwd / this.txt_NewPwd.Location = new System.Drawing.Point(99, 48); this.txt_NewPwd.Name = txt_NewPwd; this.txt_NewPwd.PasswordChar = *; this.txt_NewPwd.Size = new System.Drawing.Size(100, 21); this.txt_NewPwd.TabIndex = 4; / / txt_ConfirmPwd / this.txt_ConfirmPwd.Location = new System.Drawing.Point(99, 84); this.txt_ConfirmPwd.Name = txt_ConfirmPwd; this.txt_ConfirmPwd.PasswordChar = *; this.txt_ConfirmPwd.Size = new System.Drawing.Size(100, 21); this.txt_ConfirmPwd.TabIndex = 5; / / btn_OK / this.btn_OK.Location = new System.Drawing.Point(124, 121); this.btn_OK.Name = btn_OK; this.btn_OK.Size = new System.Drawing.Size(75, 23); this.btn_OK.TabIndex = 6; this.btn_OK.Text = 确定; this.btn_OK.UseVisualStyleBackColor = true; this.btn_OK.Click += new System.EventHandler(this.btn_OK_Click); / / Form2 / this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F); this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font; this.ClientSize = new System.Drawing.Size(222, 153); this.Controls.Add(this.btn_OK); this.Controls.Add(this.txt_ConfirmPwd); this.Controls.Add(this.txt_NewPwd); this.Controls.Add(this.txt_OldPwd); this.Controls.Add(this.label3); this.Controls.Add(this.label2); this.Controls.Add(this.label1); this.Name = Form2; this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen; this.Text = 密码修改; this.Load += new System.EventHandler(this.Form2_Load); this.ResumeLayout(false); this.PerformLayout(); “确定”按钮源码：private void btn_OK_Click(object sender, EventArgs e) if (txt_OldPwd.Text = ) MessageBox.Show(请输入旧密码,系统提示); txt_OldPwd.Focus(); else if (txt_NewPwd.Text = ) MessageBox.Show(请输入新密码, 系统提示); txt_NewPwd.Focus(); else if (txt_ConfirmPwd.Text = ) MessageBox.Show(请输入确认密码, 系统提示); txt_ConfirmPwd.Focus(); else if (txt_ConfirmPwd.Text != txt_NewPwd.Text) MessageBox.Show(确认密码必须和新密码相同!, 系统提示); txt_ConfirmPwd.Focus(); else /定义连接字符串 string strConnection = Server=6;initial catalog=Tele_Sale;user id=sa;password=sd;Connect Timeout=30; /用连接字符串创建一个数据库连接对象 SqlConnection objConnection = new SqlConnection(strConnection); /使用数据库连接对象连接数据库 objConnection.Open(); /先判断旧密码是否正确 SqlCommand SqlCmd_Select = new SqlCommand(select PassWord from MissInfo where MissID=+MissID+, objConnection); string TempPass = SqlCmd_Select.ExecuteScalar().ToString(); / MessageBox.Show(TempPass, ); if (TempPass != txt_OldPwd.Text) MessageBox.Show(对不起，您输入的旧密码不正确!, 系统提示); txt_OldPwd.Focus(); else /基于数据库连接对象创建一个sql命令 SqlCommand SqlCmd = new SqlCommand(update MissInfo set MissName=技术主管 where MissID=234, objConnection); /执行不返回结果的sql命令 SqlCmd.ExecuteNonQuery(); MessageBox.Show(密码修改成功!, 系统提示); /关闭数据库连接 objConnection.Close(); 实战练习三：执行返回结果的SQL语句。“查询”按钮源码：private void button1_Click(object sender, EventArgs e) if (txt_User.Text.Trim() = ) MessageBox.Show(请输入查询工号); txt_User.Focus(); return; /定义连接字符串 /string strConnection = Server=6;initial catalog=Tele_Sale;user id=sa;password=sd;Connect Timeout=30; string strConnection = Server=6;database=Tele_Sale;uid=sa;pwd=sd; /用连接字符串创建一个数据库连接对象 SqlConnection Conn = new SqlConnection(strConnection); /打开数据库连接 Conn.Open(); /数据适配器( SqlDataadapter 的作用是实现 DataSet 和 DB 之间的桥梁 ) SqlDataAdapter MyAdapter = new SqlDataAdapter(select * from MissInfo where MissID=+txt_User.Text.Trim()+, Conn); /SqlDataAdapter MyAdapter = new SqlDataAdapter(select * from MissInfo , Conn); /创建数据记录集( 可以把DataSet当成内存中的数据库，DataSet是不依赖于数据库的独立数据集合。) DataSet MyDataSet = new DataSet(); /将数据适配器中的数据填充到数据记录集 MyAdapter.Fill(MyDataSet, MyTable); /指定数据表格控件的数据源记录集 dgv.DataSource = MyDataSet; /指定数据表格控件的数据源表名 dgv.DataMember = MyTable; /关闭数据库连接 Conn.Close(); 实战练习四：EXCEL操作（从EXCEL导入数据 / 将DataGridView的数据导出到EXCEL）Test.xls文件的内容如下：C#程序界面：“从EXCEL导入”按钮源码： private void btn_Import_Click(object sender, EventArgs e) string strConn; / IMEX=1 可把混合型作为文本型读取，避免null值 strConn = Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Test.xls;Extended Properties=Excel 8.0;HDR=False;IMEX=1; OleDbConnection OleConn = new OleDbConnection(strConn); OleConn.Open(); String sql = SELECT * FROM Sheet1$; / 可更改 Sheet 名称 OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn); DataSet