【C#】ado.net常用代码

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› C++›C++教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

带参数的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             }