|
using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
namespace MyDB
{
/// <summary>
/// Form1 的摘要说明。
/// </summary>
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Panel panel1;
private System.Windows.Forms.Splitter splitter1;
private System.Windows.Forms.DataGrid dataGrid1;
private System.Windows.Forms.Button button1;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.ComboBox comboBox1;
private System.Windows.Forms.Label label2;
private System.Windows.Forms.TextBox textBox1;
private System.Data.OleDb.OleDbCommand oleDbSelectCommand1;
private System.Data.OleDb.OleDbCommand oleDbInsertCommand1;
private System.Data.OleDb.OleDbCommand oleDbUpdateCommand1;
private System.Data.OleDb.OleDbCommand oleDbDeleteCommand1;
private System.Data.OleDb.OleDbConnection oleDbConnection1;
private System.Data.OleDb.OleDbDataAdapter oleDbDataAdapter1;
private System.Data.DataSet dataSet1;
private System.Windows.Forms.Label label3;
private System.Windows.Forms.Label label4;
/// <summary>
/// 必需的设计器变量。
/// </summary>
private System.ComponentModel.Container components = null;
public Form1()
{
//
// Windows 窗体设计器支持所必需的
//
InitializeComponent();
//
// TODO: 在 InitializeComponent 调用后添加任何构造函数代码
//
}
/// <summary>
/// 清理所有正在使用的资源。
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose();
}
}
base.Dispose( disposing );
}
#region Windows 窗体设计器生成的代码
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(Form1));
this.panel1 = new System.Windows.Forms.Panel();
this.label4 = new System.Windows.Forms.Label();
this.label3 = new System.Windows.Forms.Label();
this.textBox1 = new System.Windows.Forms.TextBox();
this.label2 = new System.Windows.Forms.Label();
this.comboBox1 = new System.Windows.Forms.ComboBox();
this.label1 = new System.Windows.Forms.Label();
this.button1 = new System.Windows.Forms.Button();
this.splitter1 = new System.Windows.Forms.Splitter();
this.dataGrid1 = new System.Windows.Forms.DataGrid();
this.oleDbSelectCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbConnection1 = new System.Data.OleDb.OleDbConnection();
this.oleDbInsertCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbUpdateCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbDeleteCommand1 = new System.Data.OleDb.OleDbCommand();
this.oleDbDataAdapter1 = new System.Data.OleDb.OleDbDataAdapter();
this.dataSet1 = new System.Data.DataSet();
this.panel1.SuspendLayout();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).BeginInit();
((System.ComponentModel.ISupportInitialize)(this.dataSet1)).BeginInit();
this.SuspendLayout();
//
// panel1
//
this.panel1.Controls.Add(this.label4);
this.panel1.Controls.Add(this.label3);
this.panel1.Controls.Add(this.textBox1);
this.panel1.Controls.Add(this.label2);
this.panel1.Controls.Add(this.comboBox1);
this.panel1.Controls.Add(this.label1);
this.panel1.Controls.Add(this.button1);
this.panel1.Dock = System.Windows.Forms.DockStyle.Top;
this.panel1.Location = new System.Drawing.Point(0,0);
this.panel1.Name = "panel1";
this.panel1.Size = new System.Drawing.Size(408,80);
this.panel1.TabIndex = 0;
this.panel1.Paint += new System.Windows.Forms.PaintEventHandler(this.panel1_Paint);
//
// label4
//
this.label4.Location = new System.Drawing.Point(8,64);
this.label4.Name = "label4";
this.label4.Size = new System.Drawing.Size(392,16);
this.label4.TabIndex = 6;
this.label4.Text = "label4";
//
// label3
//
this.label3.Location = new System.Drawing.Point(8,40);
this.label3.Name = "label3";
this.label3.Size = new System.Drawing.Size(392,16);
this.label3.TabIndex = 5;
this.label3.Text = "label3";
//
// textBox1
//
this.textBox1.Location = new System.Drawing.Point(256,8);
this.textBox1.Name = "textBox1";
this.textBox1.Size = new System.Drawing.Size(64,21);
this.textBox1.TabIndex = 4;
//
// label2
//
this.label2.Location = new System.Drawing.Point(184,16);
this.label2.Name = "label2";
this.label2.Size = new System.Drawing.Size(80,16);
this.label2.TabIndex = 3;
this.label2.Text = "设置查询值：";
//
// comboBox1
//
this.comboBox1.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
this.comboBox1.Items.AddRange(new object[] {
"所有记录",
"订单ID",
"客户ID",
"雇员ID",
"订购日期",
"到货日期",
"发货日期",
"运货商",
"运货费",
"货主名称",
"货主地址",
"货主城市",
"货主地区",
"货主邮政编码",
"货主国家"});
this.comboBox1.Location = new System.Drawing.Point(96,8);
this.comboBox1.Name = "comboBox1";
this.comboBox1.Size = new System.Drawing.Size(88,20);
this.comboBox1.TabIndex = 2;
//
// label1
//
this.label1.Location = new System.Drawing.Point(8,16);
this.label1.Name = "label1";
this.label1.Size = new System.Drawing.Size(96,16);
this.label1.TabIndex = 1;
this.label1.Text = "选择查询条件：";
//
// button1
//
this.button1.Location = new System.Drawing.Point(328,8);
this.button1.Name = "button1";
this.button1.Size = new System.Drawing.Size(75,23);
this.button1.TabIndex = 0;
this.button1.Text = "开始查询";
this.button1.Click += new System.EventHandler(this.button1_Click);
//
// splitter1
//
this.splitter1.Dock = System.Windows.Forms.DockStyle.Top;
this.splitter1.Location = new System.Drawing.Point(0,80);
this.splitter1.Name = "splitter1";
this.splitter1.Size = new System.Drawing.Size(408,3);
this.splitter1.TabIndex = 1;
this.splitter1.TabStop = false;
//
// dataGrid1
//
this.dataGrid1.CaptionVisible = false;
this.dataGrid1.DataMember = "";
this.dataGrid1.Dock = System.Windows.Forms.DockStyle.Fill;
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(0,83);
this.dataGrid1.Name = "dataGrid1";
this.dataGrid1.Size = new System.Drawing.Size(408,211);
this.dataGrid1.TabIndex = 2;
//
// oleDbSelectCommand1
//
this.oleDbSelectCommand1.CommandText = "SELECT * FROM 订单";
this.oleDbSelectCommand1.Connection = this.oleDbConnection1;
//
// oleDbConnection1
//
this.oleDbConnection1.ConnectionString = resources.GetString("oleDbConnection1.ConnectionString");
//
// oleDbInsertCommand1
//
this.oleDbInsertCommand1.CommandText = "INSERT INTO 订单(到货日期,订购日期,发货日期,雇员ID,货主城市,货主地区,货主地址,货主国家,货主名称,货主邮政编码,客户ID" +
",运货费,运货商) VALUES (?,?,?)";
this.oleDbInsertCommand1.Connection = this.oleDbConnection1;
this.oleDbInsertCommand1.Parameters.AddRange(new System.Data.OleDb.OleDbParameter[] {
new System.Data.OleDb.OleDbParameter("到货日期",System.Data.OleDb.OleDbType.DBDate,"到货日期"),
new System.Data.OleDb.OleDbParameter("订购日期","订购日期"),
new System.Data.OleDb.OleDbParameter("发货日期","发货日期"),
new System.Data.OleDb.OleDbParameter("雇员ID",System.Data.OleDb.OleDbType.Integer,"雇员ID"),
new System.Data.OleDb.OleDbParameter("货主城市",System.Data.OleDb.OleDbType.VarWChar,15,"货主城市"),
new System.Data.OleDb.OleDbParameter("货主地区","货主地区"),
new System.Data.OleDb.OleDbParameter("货主地址",60,"货主地址"),
new System.Data.OleDb.OleDbParameter("货主国家","货主国家"),
new System.Data.OleDb.OleDbParameter("货主名称",40,"货主名称"),
new System.Data.OleDb.OleDbParameter("货主邮政编码",10,"货主邮政编码"),
new System.Data.OleDb.OleDbParameter("客户ID",5,"客户ID"),
new System.Data.OleDb.OleDbParameter("运货费",System.Data.OleDb.OleDbType.Currency,"运货费"),
new System.Data.OleDb.OleDbParameter("运货商","运货商")});
//
// oleDbUpdateCommand1
//
this.oleDbUpdateCommand1.CommandText = resources.GetString("oleDbUpdateCommand1.CommandText");
this.oleDbUpdateCommand1.Connection = this.oleDbConnection1;
this.oleDbUpdateCommand1.Parameters.AddRange(new System.Data.OleDb.OleDbParameter[] {
new System.Data.OleDb.OleDbParameter("到货日期","运货商"),
new System.Data.OleDb.OleDbParameter("Original_订单ID",System.Data.ParameterDirection.Input,false,((byte)(0)),"订单ID",System.Data.DataRowVersion.Original,null),
new System.Data.OleDb.OleDbParameter("Original_到货日期","到货日期",
new System.Data.OleDb.OleDbParameter("Original_到货日期1",
new System.Data.OleDb.OleDbParameter("Original_发货日期","发货日期",
new System.Data.OleDb.OleDbParameter("Original_发货日期1",
new System.Data.OleDb.OleDbParameter("Original_客户ID","客户ID",
new System.Data.OleDb.OleDbParameter("Original_客户ID1",
new System.Data.OleDb.OleDbParameter("Original_订购日期","订购日期",
new System.Data.OleDb.OleDbParameter("Original_订购日期1",
new System.Data.OleDb.OleDbParameter("Original_货主名称","货主名称",
new System.Data.OleDb.OleDbParameter("Original_货主名称1",
new System.Data.OleDb.OleDbParameter("Original_货主国家","货主国家",
new System.Data.OleDb.OleDbParameter("Original_货主国家1",
new System.Data.OleDb.OleDbParameter("Original_货主地区","货主地区",
new System.Data.OleDb.OleDbParameter("Original_货主地区1",
new System.Data.OleDb.OleDbParameter("Original_货主地址","货主地址",
new System.Data.OleDb.OleDbParameter("Original_货主地址1",
new System.Data.OleDb.OleDbParameter("Original_货主城市","货主城市",
new System.Data.OleDb.OleDbParameter("Original_货主城市1",
new System.Data.OleDb.OleDbParameter("Original_货主邮政编码","货主邮政编码",
new System.Data.OleDb.OleDbParameter("Original_货主邮政编码1",
new System.Data.OleDb.OleDbParameter("Original_运货商","运货商",
new System.Data.OleDb.OleDbParameter("Original_运货商1",
new System.Data.OleDb.OleDbParameter("Original_运货费","运货费",
new System.Data.OleDb.OleDbParameter("Original_运货费1",
new System.Data.OleDb.OleDbParameter("Original_雇员ID","雇员ID",
new System.Data.OleDb.OleDbParameter("Original_雇员ID1",null)});
//
// oleDbDeleteCommand1
//
this.oleDbDeleteCommand1.CommandText = resources.GetString("oleDbDeleteCommand1.CommandText");
this.oleDbDeleteCommand1.Connection = this.oleDbConnection1;
this.oleDbDeleteCommand1.Parameters.AddRange(new System.Data.OleDb.OleDbParameter[] {
new System.Data.OleDb.OleDbParameter("Original_订单ID",null)});
//
// oleDbDataAdapter1
//
this.oleDbDataAdapter1.DeleteCommand = this.oleDbDeleteCommand1;
this.oleDbDataAdapter1.InsertCommand = this.oleDbInsertCommand1;
this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;
this.oleDbDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table","订单",new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("到货日期",
new System.Data.Common.DataColumnMapping("订单ID","订单ID"),
new System.Data.Common.DataColumnMapping("订购日期",
new System.Data.Common.DataColumnMapping("发货日期",
new System.Data.Common.DataColumnMapping("雇员ID",
new System.Data.Common.DataColumnMapping("货主城市",
new System.Data.Common.DataColumnMapping("货主地区",
new System.Data.Common.DataColumnMapping("货主地址",
new System.Data.Common.DataColumnMapping("货主国家",
new System.Data.Common.DataColumnMapping("货主名称",
new System.Data.Common.DataColumnMapping("货主邮政编码",
new System.Data.Common.DataColumnMapping("客户ID",
new System.Data.Common.DataColumnMapping("运货费",
new System.Data.Common.DataColumnMapping("运货商","运货商")})});
this.oleDbDataAdapter1.UpdateCommand = this.oleDbUpdateCommand1;
//
// dataSet1
//
this.dataSet1.DataSetName = "NewDataSet";
this.dataSet1.Locale = new System.Globalization.CultureInfo("zh-CN");
//
// Form1
//
this.AutoScaleBaseSize = new System.Drawing.Size(6,14);
this.ClientSize = new System.Drawing.Size(408,294);
this.Controls.Add(this.dataGrid1);
this.Controls.Add(this.splitter1);
this.Controls.Add(this.panel1);
this.Name = "Form1";
this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
this.Text = "演示获取聚合函数的返回值";
this.Closed += new System.EventHandler(this.Form1_Closed);
this.Load += new System.EventHandler(this.Form1_Load);
this.panel1.ResumeLayout(false);
this.panel1.PerformLayout();
((System.ComponentModel.ISupportInitialize)(this.dataGrid1)).EndInit();
((System.ComponentModel.ISupportInitialize)(this.dataSet1)).EndInit();
this.ResumeLayout(false);
}
#endregion
/// <summary>
/// 应用程序的主入口点。
/// </summary>
[STAThread]
static void Main()
{
Application.Run(new Form1());
}
private void Form1_Load(object sender,System.EventArgs e)
{//显示所有记录
this.oleDbDataAdapter1.Fill(this.dataSet1);
this.dataGrid1.DataSource=this.dataSet1;
if(!this.dataGrid1.IsExpanded(0))
this.dataGrid1.Expand(0);
this.oleDbConnection1.Open();
System.Data.OleDb.OleDbCommand MyCommandAmt=new System.Data.OleDb.OleDbCommand("SELECT Count(*) FROM 订单",this.oleDbConnection1);
this.label3.Text="一共查询到："+MyCommandAmt.ExecuteScalar().ToString()+"条记录符合要求";
System.Data.OleDb.OleDbCommand MyCommandSum=new System.Data.OleDb.OleDbCommand("SELECT SUM(运货费) FROM 订单",this.oleDbConnection1);
this.label4.Text="订单运费总额为："+MyCommandSum.ExecuteScalar().ToString();
System.Data.OleDb.OleDbCommand MyCommandAvg=new System.Data.OleDb.OleDbCommand("SELECT AVG(运货费) FROM 订单",this.oleDbConnection1);
this.label4.Text+=",订单平均运费为："+MyCommandAvg.ExecuteScalar().ToString();
this.oleDbConnection1.Close();
}
private void button1_Click(object sender,System.EventArgs e)
{//查询记录
try
{
this.dataSet1.Clear();
string StrSQL=" FROM 订单 WHERE ";
StrSQL+=this.comboBox1.Text+" LIKE '%";
StrSQL+=this.textBox1.Text+"%'";
if(this.comboBox1.Text=="所有记录")
StrSQL=" FROM 订单";
this.oleDbDataAdapter1.SelectCommand.CommandText="Select * "+StrSQL;
this.oleDbDataAdapter1.SelectCommand.Connection=this.oleDbConnection1;
this.oleDbConnection1.Open();
this.oleDbDataAdapter1.SelectCommand.ExecuteNonQuery();
this.oleDbDataAdapter1.Fill(this.dataSet1);
this.dataGrid1.DataSource=this.dataSet1;
System.Data.OleDb.OleDbCommand MyCommandAmt=new System.Data.OleDb.OleDbCommand("SELECT COUNT(*) "+StrSQL,this.oleDbConnection1);
this.label3.Text="一共查询到："+MyCommandAmt.ExecuteScalar().ToString()+"条记录符合要求";
System.Data.OleDb.OleDbCommand MyCommandSum=new System.Data.OleDb.OleDbCommand("SELECT SUM(运货费) "+StrSQL,this.oleDbConnection1);
this.label4.Text="订单运费总额为："+MyCommandSum.ExecuteScalar().ToString();
System.Data.OleDb.OleDbCommand MyCommandAvg=new System.Data.OleDb.OleDbCommand("SELECT AVG(运货费) "+StrSQL,订单平均运费为："+MyCommandAvg.ExecuteScalar().ToString();
this.oleDbConnection1.Close();
}
catch(Exception Err)
{
MessageBox.Show("查询数据集记录操作失败："+Err.Message,"信息提示",
MessageBoxButtons.OK,MessageBoxIcon.Information);
if(this.oleDbConnection1.State==ConnectionState.Open)
{
this.oleDbConnection1.Close();
}
}
}
private void Form1_Closed(object sender,System.EventArgs e)
{//关闭程序
if(this.oleDbConnection1.State==ConnectionState.Open)
{
this.oleDbConnection1.Close();
}
}
private void panel1_Paint(object sender,PaintEventArgs e)
{
}}} （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
