以前在做信息管理类系统的时候经常遇到查询问题,都是每次查询直接到数据库里查询,
这样往往增大了数据的访问量,增大了数据库的压力,而且编程时会忽略这里的安全处理
问题,比如在这里的SQL注入.
这里介绍使用 DataTable自身的查询功能,一次查询后,就在数据库外查询.
核心代码:
- DataTable dt = new DataTable();
- //这里给dt表格绑定数据,这里我就自己构建dt,实际编程时,一班都是在窗体或页面记载是用查询数据库先初始化dt
- dt.Columns.Add(new DataColumn("ID"));
- dt.Columns.Add(new DataColumn("name"));
- DataRow dr0 = dt.NewRow();
- dr0[0] = "1";
- dr0[1] = "张三";
- DataRow dr1 = dt.NewRow();
- dr1[0] = "2";
- dr1[1] = "李四";
- DataRow dr2 = dt.NewRow();
- dr2[0] = "3";
- dr2[1] = "李五";
- dt.Rows.Add(dr0);
- dt.Rows.Add(dr1);
- dt.Rows.Add(dr2);
- //表格数据绑定结束
- string expression = "name like '%"+textBox1.Text.Trim()+"%' and ID=2";
- DataView dv = dt.DefaultView;
- dv.Sort = "ID desc";
- dv.RowFilter=expression;
- //此后就可以把dv的数据绑定给窗体或页面控件了
- //例如 dataGridView1.DataSource=dv;
实例: (windows应用程序)
- using System;
- using System.Collections.Generic;
- using System.ComponentModel;
- using System.Data;
- using System.Drawing;
- using System.Text;
- using System.Windows.Forms;
- namespace WindowsApplication3
- {
- public partial class Form1 : Form
- {
- DataTable dt = new DataTable();
- public Form1()
- {
- InitializeComponent();
- }
- private void button1_Click(object sender, EventArgs e)
- {
- string expression = "name like '%"+textBox1.Text.Trim()+"%' and ID=2";
- DataView dv = dt.DefaultView;
- dv.Sort = "ID desc";
- dv.RowFilter=expression;
- dataGridView1.DataSource=dv;
- }
- private void Form1_Load(object sender, EventArgs e)
- {
- dt.Columns.Add(new DataColumn("ID"));
- dt.Columns.Add(new DataColumn("name"));
- DataRow dr0 = dt.NewRow();
- dr0[0] = "1";
- dr0[1] = "张三";
- DataRow dr1 = dt.NewRow();
- dr1[0] = "2";
- dr1[1] = "李四";
- DataRow dr2 = dt.NewRow();
- dr2[0] = "3";
- dr2[1] = "李五";
- dt.Rows.Add(dr0);
- dt.Rows.Add(dr1);
- dt.Rows.Add(dr2);
- dataGridView1.DataSource = dt.DefaultView;
- }
- }
- }
窗体设计代码
- namespace WindowsApplication3
- {
- partial class Form1
- {
- /// <summary>
- /// 必需的设计器变量。
- /// </summary>
- private System.ComponentModel.IContainer components = null;
- /// <summary>
- /// 清理所有正在使用的资源。
- /// </summary>
- /// <param name="disposing">如果应释放托管资源,为 true;否则为 false。</param>
- protected override void Dispose(bool disposing)
- {
- if (disposing && (components != null))
- {
- components.Dispose();
- }
- base.Dispose(disposing);
- }
- #region Windows 窗体设计器生成的代码
- /// <summary>
- /// 设计器支持所需的方法 - 不要
- /// 使用代码编辑器修改此方法的内容。
- /// </summary>
- private void InitializeComponent()
- {
- this.button1 = new System.Windows.Forms.Button();
- this.textBox1 = new System.Windows.Forms.TextBox();
- this.dataGridView1 = new System.Windows.Forms.DataGridView();
- ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).BeginInit();
- this.SuspendLayout();
- //
- // button1
- //
- this.button1.Location = new System.Drawing.Point(312, 44);
- this.button1.Name = "button1";
- this.button1.Size = new System.Drawing.Size(75, 23);
- this.button1.TabIndex = 1;
- this.button1.Text = "button1";
- this.button1.UseVisualStyleBackColor = true;
- this.button1.Click += new System.EventHandler(this.button1_Click);
- //
- // textBox1
- //
- this.textBox1.Location = new System.Drawing.Point(159, 46);
- this.textBox1.Name = "textBox1";
- this.textBox1.Size = new System.Drawing.Size(100, 21);
- this.textBox1.TabIndex = 2;
- //
- // dataGridView1
- //
- this.dataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
- this.dataGridView1.Location = new System.Drawing.Point(181, 87);
- this.dataGridView1.Name = "dataGridView1";
- this.dataGridView1.RowTemplate.Height = 23;
- this.dataGridView1.Size = new System.Drawing.Size(240, 150);
- this.dataGridView1.TabIndex = 3;
- //
- // Form1
- //
- this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
- this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
- this.ClientSize = new System.Drawing.Size(683, 266);
- this.Controls.Add(this.dataGridView1);
- this.Controls.Add(this.textBox1);
- this.Controls.Add(this.button1);
- this.Name = "Form1";
- this.Text = "Form1";
- this.Load += new System.EventHandler(this.Form1_Load);
- ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).EndInit();
- this.ResumeLayout(false);
- this.PerformLayout();
- }
- #endregion
- private System.Windows.Forms.Button button1;
- private System.Windows.Forms.TextBox textBox1;
- private System.Windows.Forms.DataGridView dataGridView1;
- }
- }
注意点:
DataTable dt = new DataTable();
是定义的窗体类Form1的变量,而不是某个事件,函数的变量.