【C#】ado.net常用代码
带参数的SqlDataAdapter ：
using (SqlConnection con = new SqlConnection("server=.; database=data; integrated security=SSPI;"))
{
using (SqlCommand com = con.CreateCommand())
{
com.CommandText = "select Age from T_User where UserName=@name";
com.Parameters.Add(new SqlParameter("name", textBox1.Text));
SqlDataAdapter da = new SqlDataAdapter(com);
DataSet ds = new DataSet();
da.Fill(ds);
··············
··············
}
}
从多个txt文件中导入数据致数据库：
1 using (SqlConnection con = new SqlConnection("server=.; database=data; integrated security=SSPI;")) 2 { 3 using (SqlCommand com = con.CreateCommand()) 4 { 5 con.Open(); 6 com.CommandText = "insert into T_phone(StarNum,EndNum,Name) values(@starnum,@endnum,@name)"; 7 if (folderBrowserDialog2.ShowDialog() == DialogResult.OK) 8 { 9 string[] files = Directory.GetFiles(folderBrowserDialog2.SelectedPath, "*.txt", SearchOption.AllDirectories);//遍历文件夹中的文件 10 foreach (string file in files)//遍历所选中的文件 11 { 12 string name = Path.GetFileNameWithoutExtension(file); //获取文件的文件名（不包括后缀） 13 14 string[] lines = File.ReadAllLines(file,Encoding.Default);//file和streamreader的区别在于file是把数据全部加载内存，而streamreader则是一行一行的读取，当数据量大的时候用streamreader数据量小的时候用file； 15 foreach (string line in lines) 16 { 17 string[] strs = line.Split('-'); 18 string starnum = strs[0]; 19 string endnum = strs[1]; 20 string shengshi = strs[2]; 21 com.Parameters.Clear(); 22 com.Parameters.Add(new SqlParameter("starnum", starnum)); 23 com.Parameters.Add(new SqlParameter("endnum", endnum)); 24 com.Parameters.Add(new SqlParameter("name", name + shengshi)); 25 com.ExecuteNonQuery(); 26 } 27 28 } 29 MessageBox.Show("导入成功"); 30 } 31 }
从带个txt文件中导入数据到数据库：
1 if (openFileDialog1.ShowDialog() == DialogResult.OK) 2 { 3 string filename = openFileDialog1.FileName; 4 using (FileStream file = File.OpenRead(openFileDialog1.FileName)) 5 { 6 using (StreamReader reader = new StreamReader(file,Encoding.Default)) 7 { 8 string line = null; 9 while ((line = reader.ReadLine()) != null) 10 { 11 string[] strs = line.Split('&'); 12 string name = strs[0]; 13 int age = Convert.ToInt32(strs[1]); 14 15 using (SqlConnection con = new SqlConnection("server=.; database=data; integrated security=SSPI;")) 16 { 17 using (SqlCommand com = con.CreateCommand()) 18 { 19 con.Open(); 20 com.CommandText = "insert into T_User(UserName,Age) values(@username,@age)"; 21 com.Parameters.Clear(); 22 com.Parameters.Add(new SqlParameter("username", name)); 23 com.Parameters.Add(new SqlParameter("age", age)); 24 com.ExecuteNonQuery(); 25 } 26 } 27 28 } 29 MessageBox.Show("导入成功！"); 30 } 31 }
从数据库导出数据：
1 if (saveFileDialog1.ShowDialog() == DialogResult.OK) 2 { 3 using (StreamWriter sw = new StreamWriter(saveFileDialog1.FileName)) 4 { 5 using (SqlConnection con = new SqlConnection("server=.; database=data; integrated security=SSPI;")) 6 { 7 using (SqlCommand com = con.CreateCommand()) 8 { 9 con.Open(); 10 com.CommandText = "select * from T_User"; 11 using (SqlDataReader read = com.ExecuteReader()) 12 { 13 StringBuilder sb = new StringBuilder(); 14 while (read.Read()) 15 { 16 Int64 id = read.GetInt64(read.GetOrdinal("id")); 17 string name = read.GetString(read.GetOrdinal("UserName")); 18 int age = read.GetInt32(read.GetOrdinal("Age")); 19 sb.Append(id); 20 sb.Append(name); 21 sb.Append(age); 22 } 23 sw.WriteLine(sb); 24 } 25 MessageBox.Show("导出成功！"); 26 } 27 } 28 } 29 30 }
弱类型DataSet数据的修改：
1 DataSet ds = new DataSet(); 2 using (SqlConnection con = new SqlConnection("server=.; database=data; integrated security=SSPI;")) 3 { 4 using (SqlCommand com = con.CreateCommand()) 5 { 6 7 con.Open(); 8 com.CommandText = "select * from T_User"; 9 SqlDataAdapter da = new SqlDataAdapter(com); 10 da.Fill(ds); 11 DataTable dt = ds.Tables[0]; 12 DataRow dr = dt.Rows[0]; 13 dr["UserName"] = "你是哈哈"; 14 15 SqlCommandBuilder scb = new SqlCommandBuilder(da); 16 da.Update(ds); 17 18 this.lianjie(); 19 } 20 }
lianjie()函数：
1 public void lianjie() 2 { 3 DataSet ds = new DataSet(); 4 using (SqlConnection con = new SqlConnection("server=.; database=data; integrated security=SSPI;")) 5 { 6 using (SqlCommand com = con.CreateCommand()) 7 { 8 9 con.Open(); 10 com.CommandText = "select * from T_User"; 11 SqlDataAdapter da = new SqlDataAdapter(com); 12 da.Fill(ds); 13 dataGridView1.DataSource = ds.Tables[0]; 14 } 15 } 16 }
从数据库读取某个字段：
View Code
1 using (SqlConnection con = cons.getconn()) 2 { 3 using (SqlCommand com = con.CreateCommand()) 4 { 5 con.Open(); 6 com.CommandText = "select C_OnePwd from Client where C_ID=@C_ID and C_State=1"; 7 com.Parameters.Add(new SqlParameter("C_ID", model.cid)); 8 using (SqlDataReader reader = com.ExecuteReader()) 9 { 10 if (reader.Read()) 11 { 12 string password = reader.GetString(reader.GetOrdinal("C_OnePwd")); 13 if (password == model.conepwd) 14 { 15 return true; 16 } 17 else 18 { 19 return false; 20 } 21 } 22 23 else 24 { 25 return false; 26 } 27 } 28 } 29 }
