欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

【C#】ado.net常用代码

程序员文章站 2024-01-12 16:34:28
...

带参数的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 [email protected]";
                    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         }

从数据库读取某个字段:

【C#】ado.net常用代码【C#】ado.net常用代码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 [email protected]_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             }

 

转载于:https://www.cnblogs.com/ngnetboy/archive/2012/07/19/2598617.html