ADO.NET学习摘记3

手机号码归属地练习：

一：导入数据部分

   private void btnImport_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog dlg = new FolderBrowserDialog();
            if (dlg.ShowDialog() != DialogResult.OK)
            {
                return;
            }
           
            string path = dlg.SelectedPath;
            string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "delete from T_Phone";
                    cmd.ExecuteNonQuery();
                }
            }

            string [] files=Directory.GetFiles(path ,"*.txt", SearchOption .AllDirectories );

           
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "insert into T_Phone(StartNo,EndNo,Name) values(@startno,@endno,@name)";
                    foreach (string file in files)
                    {
                        string 运营商名称 = Path.GetFileNameWithoutExtension(file);
                        string[] lines = File.ReadAllLines(file, Encoding.Default);//不用stream reader，因为文件很小，一次全部导入内存也不占太大内存。
                        foreach (string line in lines)
                        {
                            string[] strs = line.Split('-');
                            string 开始号码 = strs[0];
                            string 结束号码 = strs[1];
                            string 市 = strs[2];

                            cmd.Parameters.Clear();
                            cmd.Parameters.Add(new SqlParameter ("startno",开始号码));
                            cmd.Parameters.Add(new SqlParameter ("endno",结束号码));
                            cmd.Parameters.Add(new SqlParameter ("name",运营商名称+市));
                            cmd.ExecuteNonQuery();
                         

                        }

                    }
                   
                }
            }

            MessageBox.Show("数据导入成功！");

        }

二：手机号码归属地测试查询：

private void btnSearch_Click(object sender, EventArgs e)
        {
            string conStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
            using (SqlConnection con = new SqlConnection(conStr))
            {
                con.Open();
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "select Name from T_Phone where  StartNo<@no and EndNo>@no";
                    string no = txtPhone.Text;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add(new SqlParameter("no", no));
                    string result = Convert.ToString(cmd.ExecuteScalar());
                    txtResult.Text = result;

               
                }

            }
        }

 

三、测试修改，上面代码有些不严谨！

        private void btnSearch_Click(object sender, EventArgs e)
        {
            string conStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
            using (SqlConnection con = new SqlConnection(conStr))
            {
                con.Open();
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "select * from T_Phone where  StartNo<=@no and EndNo>=@no";
                    string no = txtPhone.Text;
                    cmd.Parameters.Clear();
                    cmd.Parameters.Add(new SqlParameter("no", no));
                    //string result = Convert.ToString(cmd.ExecuteScalar());//不完善， 当查找不到运营商时，显示空白数据，让用户不知是否执行成功与否
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            string Name = reader.GetString(reader.GetOrdinal("Name"));
                            txtResult.Text = Name;
                        }
                        else
                        {
                            txtResult.Text = "找不到归属地情况";
                        }
                    }
                 

               
                }

            }
        }