SQL查询相关技术-6.SQL操作(自选设置条件)

来源：互联网发布：手机淘宝有什么活动编辑：程序博客网时间：2024/06/07 10:00

SQL操作

1. 计算年龄

代码注释：stringP_Str_SqlStr = string.Format(//创建SQL查询字符串

@"SELECT 学生姓名,出生年月,

DATEDIFF(YEAR,出生年月,GETDATE()) AS学生年龄 FROMtb_Student");

记住DATEDIFF(YEAR,出生年月,GETDATE())这个函数

SELECT DATEDIFF(day,'2008-12-29','2008-12-30') AS DiffDate

YEAR是计算的单位可以是day

using System;

usingSystem.Collections.Generic;

usingSystem.ComponentModel;

usingSystem.Data;

usingSystem.Drawing;

usingSystem.Linq;

usingSystem.Text;

usingSystem.Windows.Forms;

usingSystem.Data.SqlClient;

namespaceUseDateDiff

{

public partial class Frm_Main : Form

{

public Frm_Main()

{

InitializeComponent();

}

private void Frm_Main_Load(objectsender, EventArgs e)

{

dgv_Message.DataSource =GetMessage();//设置数据源

dgv_Message.Columns[1].Width =200;//设置列宽度

}

private void btn_Select_Click(objectsender, EventArgs e)

{

dgv_Message.DataSource =GetStudent();//设置数据源

dgv_Message.Columns[1].Width =100;//设置列宽度

dgv_Message.Columns[2].Width =170;//设置列宽度

}

/// <summary>

/// 查询数据库信息

/// </summary>

/// <returns>方法返回DataTable对象</returns>

private DataTable GetStudent()

{

string P_Str_ConnectionStr =string.Format(//创建数据库连接字符串

@"DataSource=.\SQLEXPRESS;AttachDbFilename=C:\DataBase\db_TomeTwo.mdf;IntegratedSecurity=True;Connect Timeout=30;User Instance=True");

string P_Str_SqlStr =string.Format(//创建SQL查询字符串

@"SELECT 学生姓名,出生年月,

DATEDIFF(YEAR,出生年月,GETDATE()) AS学生年龄 FROM tb_Student");

SqlDataAdapter P_SqlDataAdapter =new SqlDataAdapter(//创建数据适配器

P_Str_SqlStr,P_Str_ConnectionStr);

DataTable P_dt = new DataTable();//创建数据表

P_SqlDataAdapter.Fill(P_dt);//填充数据表

return P_dt;//返回数据表

}

/// <summary>

/// 查询数据库信息

/// </summary>

/// <returns>方法返回DataTable对象</returns>

private DataTable GetMessage()

{

string P_Str_ConnectionStr =string.Format(//创建数据库连接字符串

@"DataSource=.\SQLEXPRESS;AttachDbFilename=C:\DataBase\db_TomeTwo.mdf;IntegratedSecurity=True;Connect Timeout=30;User Instance=True");

string P_Str_SqlStr =string.Format(//创建SQL查询字符串

"select 学生姓名,出生年月 from tb_Student");

SqlDataAdapter P_SqlDataAdapter =new SqlDataAdapter(//创建数据适配器

P_Str_SqlStr,P_Str_ConnectionStr);

DataTable P_dt = new DataTable();//创建数据表

P_SqlDataAdapter.Fill(P_dt);//填充数据表

return P_dt;//返回数据表

}

2. 运用运算符指定条件查询

using System;

usingSystem.Collections.Generic;

usingSystem.ComponentModel;

usingSystem.Data;

usingSystem.Drawing;

using System.Linq;

usingSystem.Text;

usingSystem.Windows.Forms;

usingSystem.Data.SqlClient;

namespaceFindOperator

{

public partial class Frm_Main : Form

{

public Frm_Main()

{

InitializeComponent();

}

private void Frm_Main_Load(objectsender, EventArgs e)

{

cbox_Field.DataSource =GetField("tb_student");//设置数据源

cbox_Field.DisplayMember ="Name";//设置显示的属性

dgv_Message.DataSource =GetMessage();//设置数据源

}

private void btn_Select_Click(objectsender, EventArgs e)

{

dgv_Message.DataSource =GetStudent(SQLGenerator(//设置数据源

"tb_Student",cbox_Field.Text, cbox_Operator.Text,

txt_Condition.Text));

}

/// <summary>

/// 查询数据表中字段信息

/// </summary>

/// <returns>方法返回DataTable对象</returns>

private List<StudentField>GetField(string TableName)

{

string P_Str_ConnectionStr =string.Format(//创建数据库连接字符串

@"server=WIN-GI7E47AND9R\LS;database=db_TomeTwo;uid=sa;pwd=");

string P_Str_SqlStr = string.Format(//创建SQL查询字符串

"select c.name fromsyscolumns c,sysobjects a where a.name='{0}' and a.id=c.id",

TableName);

SqlDataAdapter P_SqlDataAdapter =new SqlDataAdapter(//创建数据适配器

P_Str_SqlStr, P_Str_ConnectionStr);

DataTable P_dt = new DataTable();//创建数据表

P_SqlDataAdapter.Fill(P_dt);//填充数据表

List<StudentField>P_List_StudentField = new List<StudentField>();

for (int i = 0; i <P_dt.Rows.Count; i++)

{

P_List_StudentField.Add(newStudentField()//向集合添加数据

{ Name =P_dt.Rows[i][0].ToString() });

}

return P_List_StudentField;//返回数据表

}

/// <summary>

/// 动态生成SQL语句

/// </summary>

/// <paramname="Table">表名</param>

/// <paramname="Field">字段名</param>

/// <paramname="Operator">操作符</param>

/// <paramname="Condition">条件</param>

/// <returns>方法返回SQL语句</returns>

private string SQLGenerator(stringTable,string Field,string Operator,string Condition)

{

string P_Str = "SELECT * FROM"+Table;//生成SQL语句

switch (Operator)

{

case "%Like%":

P_Str += string.Format("WHERE {0} Like '%{1}%'",Field,Condition);//添加字符串

break;

case "Like%":

P_Str +=string.Format(" WHERE {0} Like '{1}%'", Field, Condition);//添加字符串

break;

case "%Like":

P_Str +=string.Format(" WHERE {0} Like '%{1}'", Field, Condition);//添加字符串

break;

default:

if(Condition==string.Empty)

{

P_Str +=string.Format(" WHERE {0} IS null OR {0}=''", Field);//添加字符串

break;

}

P_Str +=string.Format(" WHERE {0} {1} '{2}'",//添加字符串

Field, Operator,Condition);

break;

}

return P_Str;//方法返回字符串对象

}

/// <summary>

/// 根据指定SQL语句查询数据库信息

/// </summary>

/// <returns>方法返回DataTable对象</returns>

private DataTable GetStudent(stringSQL)

{

string P_Str_ConnectionStr =string.Format(//创建数据库连接字符串

@"server=WIN-GI7E47AND9R\LS;database=db_TomeTwo;uid=sa;pwd=");

SqlDataAdapter P_SqlDataAdapter =new SqlDataAdapter(//创建数据适配器

SQL, P_Str_ConnectionStr);

DataTable P_dt = new DataTable();//创建数据表

P_SqlDataAdapter.Fill(P_dt);//填充数据表

return P_dt;//返回数据表

}

/// <summary>

/// 查询数据库信息

/// </summary>

/// <returns>方法返回DataTable对象</returns>

private DataTable GetMessage()

{

string P_Str_ConnectionStr =string.Format(//创建数据库连接字符串

@"server=WIN-GI7E47AND9R\LS;database=db_TomeTwo;uid=sa;pwd=");

string P_Str_SqlStr = string.Format(//创建SQL查询字符串

"SELECT * FROMtb_Student");

SqlDataAdapter P_SqlDataAdapter =new SqlDataAdapter(//创建数据适配器

P_Str_SqlStr,P_Str_ConnectionStr);

DataTable P_dt = new DataTable();//创建数据表

P_SqlDataAdapter.Fill(P_dt);//填充数据表

return P_dt;//返回数据表

}

class StudentField

{

public string Name { get; set; }//定义属性

public override string ToString()//重写ToString方法

{

return Name.ToString();

}

0 0