C# 数据库查询总结

数据库：SQL Server

数据库名：Blog

表名：Test

注：数据库的连接可以使用“dbl”文件测试，具体使用百度即可。

1.将数据库的数据全部查询到datagridview

语句：select * from Test

 1   private void button1_Click(object sender, EventArgs e)
 2  {
 3       string connstr = "Data Source=LONG-PC\\LONG;Initial Catalog=Blog;User ID=long;Password=longtao";
 4       string cmdstr="select * from Test";
 5             
 6       SqlConnection conn = new SqlConnection(connstr);
 7       conn.Open();
 8 
 9       SqlCommand cmd = new SqlCommand();
10       cmd.CommandText = cmdstr;
11       cmd.Connection = conn;
12       SqlDataAdapter adap = new SqlDataAdapter(cmd);
13       DataSet ds = new DataSet();
14       adap.Fill(ds);
15       dataGridView1.DataSource = ds.Tables[0].DefaultView;
16  }

2.查询数据库的部分数据并显示到datagridview

语句： string cmdstr = "select 姓名,年龄 from Test";

 1  private void button2_Click(object sender, EventArgs e)
 2  {
 3      string connstr = "Data Source=LONG-PC\\LONG;Initial Catalog=Blog;User ID=long;Password=longtao";
 4      string cmdstr = "select 姓名,年龄 from Test";
 5 
 6      SqlConnection conn = new SqlConnection(connstr);
 7      conn.Open();
 8 
 9      SqlCommand cmd = new SqlCommand();
10      cmd.CommandText = cmdstr;
11      cmd.Connection = conn;
12      SqlDataAdapter adap = new SqlDataAdapter(cmd);
13      DataSet ds = new DataSet();
14      adap.Fill(ds);
15      dataGridView1.DataSource = ds.Tables[0].DefaultView;
16  }

3.使用列别名显示数据

语句： string cmdstr = "select 姓名 as name,年龄 as age from Test";

注：语句中的“as”关键词，可以使用空格代替

 1   private void button3_Click(object sender, EventArgs e)
 2   {
 3       string connstr = "Data Source=LONG-PC\\LONG;Initial Catalog=Blog;User ID=long;Password=longtao";
 4       string cmdstr = "select 姓名 as name,年龄 as age from Test";
 5 
 6       SqlConnection conn = new SqlConnection(connstr);
 7       conn.Open();
 8 
 9       SqlCommand cmd = new SqlCommand();
10       cmd.CommandText = cmdstr;
11       cmd.Connection = conn;
12       SqlDataAdapter adap = new SqlDataAdapter(cmd);
13       DataSet ds = new DataSet();
14       adap.Fill(ds);
15       dataGridView1.DataSource = ds.Tables[0].DefaultView;
16  }

4.使用列计算

语句：string cmdstr = "select 姓名,年龄,语文,数学,英语,语文+数学+英语 as 总分 from Test";

 1   private void button4_Click(object sender, EventArgs e)
 2   {
 3      string connstr = "Data Source=LONG-PC\\LONG;Initial Catalog=Blog;User ID=long;Password=longtao";
 4      string cmdstr = "select 姓名,年龄,语文,数学,英语,语文+数学+英语 as 总分 from Test";
 5 
 6      SqlConnection conn = new SqlConnection(connstr);
 7      conn.Open();
 8 
 9      SqlCommand cmd = new SqlCommand();
10      cmd.CommandText = cmdstr;
11      cmd.Connection = conn;
12      SqlDataAdapter adap = new SqlDataAdapter(cmd);
13      DataSet ds = new DataSet();
14      adap.Fill(ds);
15      dataGridView1.DataSource = ds.Tables[0].DefaultView;
16   }

5.限定查询数值范围大小

语句：string cmdstr = "select 姓名,年龄,语文,数学,英语,语文+数学+英语 as 总分 from Test where (语文+数学+英语)>215";

 1   private void button5_Click(object sender, EventArgs e)
 2   {
 3       string connstr = "Data Source=LONG-PC\\LONG;Initial Catalog=Blog;User ID=long;Password=longtao";
 4       string cmdstr = "select 姓名,年龄,语文,数学,英语,语文+数学+英语 as 总分 from Test where (语文+数学+英语)>215";
 5 
 6       SqlConnection conn = new SqlConnection(connstr);
 7       conn.Open();
 8 
 9       SqlCommand cmd = new SqlCommand();
10       cmd.CommandText = cmdstr;
11       cmd.Connection = conn;
12       SqlDataAdapter adap = new SqlDataAdapter(cmd);
13       DataSet ds = new DataSet();
14       adap.Fill(ds);
15       dataGridView1.DataSource = ds.Tables[0].DefaultView;
16   }

C# 数据库查询总结

标签：