二、SqlCommand操作数据库
1.ExecuteNonQuery:执行非查询的T-SQL语句
using (SqlCommand cmd = conn.CreateCommand()) { //设置T-SQL语句 cmd.CommandText = "insert into userInfo(id,userName,userAge,DelFlag)values(11,'赵非',18,0)"; //执行T-SQL语句,并返回受影响的行数 int num = cmd.ExecuteNonQuery(); Console.WriteLine(num); }
2.ExecuteScalar:执行查询,并返回查询所返回的结果集中第一行的第一列
using (SqlCommand cmd = conn.CreateCommand()) { //查询Employee表中性别为男的所有信息 cmd.CommandText = "select * from Employee where gender='男' "; //执行查询,并返回这些信息中的第一行的第一列的数据 label1.Text = cmd.ExecuteScalar().ToString(); }
3.ExecuteReader:执行T-SQL语句,并返回一个SqlDataReader读取器
using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "select * from employee"; //执行T-SQL语句,并生成一个SqlDataReader读取器 SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { //reader[1]:读取表中第2列的数据 listView1.Items.Add(reader[1].ToString()); } reader.Close(); }
4.数据导入
//提示用户打开文件 using (OpenFileDialog opfd = new OpenFileDialog()) { if (opfd.ShowDialog() == DialogResult.OK) { this.textBox1.Text = opfd.FileName; //调用数据导入的方法 ImportData(opfd.FileName); MessageBox.Show("数据导入成功"); } }
//数据导入的方法 private void ImportData(string fileName) { //定义一个空字符串 string tempStr = string.Empty; //用指定字符编码,为指定文件名初始化StreamReader类的一个新实例 using (StreamReader sr = new StreamReader(fileName, Encoding.UTF8)) { string connStr = "server= .;uid = sa;pwd = 123;database = testDb"; using (SqlConnection conn = new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { //sr.ReadLine():从当前流中读取下一行字符,并将数据作为字符串返回 while (!string.IsNullOrEmpty(tempStr = sr.ReadLine())) { string[] strs = tempStr.Split(','); //注意:字符串数据要加单引号 cmd.CommandText = string.Format("insert into testTable3(id,name,num)values({0},'{1}',{2})", strs[0], strs[1], strs[2]); cmd.ExecuteNonQuery(); } } } } }
5.数据导出
//声明一个空字符串,用来存储文件路径 string filePath = string.Empty; //提示用户选择文件的保存位置 using (SaveFileDialog ofd = new SaveFileDialog()) { if (ofd.ShowDialog() != DialogResult.OK) { return; } //选定的文件名的字符串 filePath = ofd.FileName; } using (conn = new SqlConnection(connStr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = "select * from TblArea"; SqlDataReader sdr = cmd.ExecuteReader(); //写入流 using (StreamWriter sw = new StreamWriter(filePath)) { while (sdr.Read()) { string tempStr = sdr["AreaId"].ToString() + "\t" + sdr["AreaName"] + "\t" + sdr["AreaPId"] + "\r\n"; //将字符写入sw流中 sw.Write(tempStr); } MessageBox.Show("导出成功!"); } } }