手机号码归属地TXT文档数据写入DB

1，读取TXT写入DB步骤：打开连接——SqlCommand(Conn,Str)——逐行读取TXT文本 构造SqlParameter 的参数——执行ExecuteNonQuery()—— 清除参数

2，乱码问题：Txt文档的编码要和StreamReader的编码一致：

using (StreamReader streamReader = new StreamReader(file, Encoding.Default))//解决乱码问题

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;
using System.Configuration;

namespace 手机号码归属地查询
{
    public partial class 手机号码 : Form
    {
        public 手机号码()
        {
            InitializeComponent();
        }

        private void BtnSelFolder_Click(object sender, EventArgs e)
        {
            //连接字符串
            string strConn = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;

            //打开文件对话框
            FolderBrowserDialog fbDlg = new FolderBrowserDialog();
            string strFolderPath = "";
            if (fbDlg.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            else
            {
                strFolderPath = fbDlg.SelectedPath;
            }
            if (strFolderPath != "")
            {
                //打开连接
                using (SqlConnection Conn = new SqlConnection(strConn))
                {
                    Conn.Open();
                    //清除已经存在的数据
                    string strClear = "Delete  from T_Numbers ";
                    SqlCommand sqlCmdClear = new SqlCommand(strClear, Conn);
                    sqlCmdClear.ExecuteNonQuery();
                    //得到目录下所有的txt文件路径
                    string[] files = Directory.GetFiles(strFolderPath, "*.txt", SearchOption.AllDirectories);
                    foreach (string file in files)
                    {
                        //读取每一个txt文件名
                        string txtName = Path.GetFileNameWithoutExtension(file);
                        //读取txt内容
                        using (StreamReader streamReader = new StreamReader(file, Encoding.Default))//解决乱码问题
                        {
                            string strIns = "insert into  T_Numbers (StartNum, EndNum, CityName,TelCoName) values(@startNum,@endNum,@cityName,@telCoName)";
                            using (SqlCommand sqlCmd = new SqlCommand(strIns, Conn))
                            {
                                string line = null;
                                string strStartNum = "";
                                string strEndNum = "";
                                string strCityName = "";
                                string strTelCoName = "";
                                while ((line = streamReader.ReadLine()) != null)
                                {
                                    string[] str = line.Split('-');
                                    strStartNum = str[0].ToString();
                                    strEndNum = str[1].ToString();
                                    strCityName = str[2].ToString();
                                    strTelCoName = txtName;

                                    SqlParameter[] sqlPara = new SqlParameter[]{
                                        new SqlParameter("startNum",strStartNum),
                                        new SqlParameter("endNum",strEndNum),
                                        new SqlParameter("telCoName",strTelCoName),
                                        new SqlParameter("cityName",strCityName)
                                    };

                                    sqlCmd.Parameters.AddRange(sqlPara);  //添加参数

                                    sqlCmd.ExecuteNonQuery();  //执行

                                    sqlCmd.Parameters.Clear();  //清除参数
                                }

                            }
                        }

                    }
                    MessageBox.Show("手机号码归属地导入OK");
                }
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string strCon = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
            string strSel = "SELECT CityName,TelCoName FROM  T_Numbers where StartNum<= @TelNum and @TelNum<=EndNum";
            using (SqlConnection con = new SqlConnection(strCon))
            {
                con.Open();
                using (SqlCommand sqlCmd = new SqlCommand(strSel, con))
                {
                    SqlParameter sqlPara = new SqlParameter("TelNum", textBox1.Text.ToString().Trim());
                    sqlCmd.Parameters.Add(sqlPara);
                    using (SqlDataReader sdr = sqlCmd.ExecuteReader())
                    {
                        if (sdr.Read())
                        {
                            string PhoneCity = sdr.GetString(0);
                            string TelCoName = sdr.GetString(1);
                            MessageBox.Show(PhoneCity + " " + TelCoName);
                        }
                        else
                        {
                            MessageBox.Show("无手机号码信息");
                        }

                    }
                }
            }
        }
    }
}

 

 

 

转载于:https://www.cnblogs.com/siri/archive/2012/12/18/2822574.html