ADO.Net_案例2（手机号码归属地查询）

文本文件名格式：

 江苏移动.txt 、江苏联通.txt

内容格式：

13951200000-13951239999-常州
13951240000-13951249999-宿迁
13951250000-13951259999-连云港
13951260000-13951269999-淮安
13951270000-13951289999-镇江
13951290000-13951299999-宿迁

 

首先将数据导入到数据库：

private void btnImport_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog folder = new FolderBrowserDialog(); //选择文件夹对话框
            if (folder.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            string folderPath = folder.SelectedPath;

            //先删除数据表中的数据

            using (SqlConnection conn = new SqlConnection(
@"Data Source =.\SQLEXPRESS; AttachDBFilename=|DataDirectory|\MyDB1.mdf; Integrated Security=True;User Instance=True"))
           {

                conn.Open();

                using(SqlCommand cmd = conn.CreateCommand())

                {

                     cmd.CommandText = "delete from T_PhoneNo";

                     cmd.ExecuteNonQuery();

               }

           }

           //数据库中插入数据
            using (SqlConnection conn = new SqlConnection(
@"Data Source =.\SQLEXPRESS; AttachDBFilename=|DataDirectory|\MyDB1.mdf; Integrated Security=True;User Instance=True"))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "insert into T_PhoneNo(FStartNo,FEndNo,FOperName) values(@FStartNo,@FEndNo,@FOperName)";

                    string[] files = Directory.GetFiles(folderPath); //取得文件夹下所有文件
                    foreach (string file in files)
                    {
                        if (file.EndsWith(".txt"))
                        {
                            string[] strings = file.Split('.');
                            string fileName = strings[0].Substring(strings[0].LastIndexOf("\\")+1);
                            using (FileStream fStream = File.OpenRead(file))
                            {
                                using (StreamReader sr = new StreamReader(fStream, Encoding.Default))
                                {
                                    string line = null;
                                    while ((line = sr.ReadLine()) != null)
                                    {
                                        string[] strs = line.Split('-');
                                        string startNo = strs[0];
                                        string endNo = strs[1];
                                        string cityName = strs[2];
                                        string operatorName = fileName + '[' + cityName + ']';

                                        //清空参数集合
                                        cmd.Parameters.Clear();
                                        cmd.Parameters.AddWithValue("FStartNo", startNo);
                                        cmd.Parameters.AddWithValue("FEndNo", endNo);
                                        cmd.Parameters.AddWithValue("FOperName", operatorName);
                                        //执行插入命令
                                        cmd.ExecuteNonQuery();
                                    }
                                }
                            }
                        }
                    }
                }
            }
            MessageBox.Show("导入成功！");
        }

 

界面上输入号码，点击查询：

private void btnQuery_Click(object sender, EventArgs e)
        {
            //取得用户输入的号码
            string txtPhoneNo = txtNo.Text.Trim();
            using (SqlConnection conn = new SqlConnection(
@"Data Source =.\SQLEXPRESS; AttachDBFilename=|DataDirectory|\MyDB1.mdf; Integrated Security=True;User Instance=True"))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = "select FOperName from T_PhoneNo where FStartNo<=@FPhoneNo and FEndNo>=@FPhoneNo";
                    cmd.Parameters.AddWithValue("FPhoneNo", txtPhoneNo);
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            string operatorName = reader.GetString(reader.GetOrdinal("FOperName"));
                            lbArea.Text = operatorName;
                        }
                        else
                        {
                            lbArea.Text = "找不到您要查的号码！请检查您输入的号码是否正确！";
                        }
                    }
                }
            }
        }

简单的手机号码归属地查询功能实现完毕。

 

在实现导入过程时：

另一种简单方法：

string[] files = Directory.GetFiles(folderPath,"*.txt",SearchOption.AllDirectories); //获取文件夹下所有的.txt文件

foreach(string file in files)

{

      string operName = Path.GetFileNameWithoutExtension(file); //获取不带扩展名的文件名

      string[] lines = File.ReadAllLines(file,Encoding.Default);//不用StreamReader，因为文件很小，一次性加载也不占多少内存

      foreach(string line in lines)

      {

             //处理文件中的每一行数据

      }

}