ADO.Net之手机号码归属地查询

本文实现的主要功能是查询手机号码的归属地。需要特别注意：SqlServer的事务和mysql事务使用有一点不一样的地方是“需要把BeginTransaction返回的SqlTransaction对象赋值给SqlCommand的Transaction属性”

public partial classForm1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

 

        private void Form1_Load(object sender, EventArgse)

        {

 

        }

 

        private void Add_Click(object sender, EventArgse)

        {

            OpenFileDialog dlg = new OpenFileDialog();

            if (dlg.ShowDialog() != System.Windows.Forms.DialogResult.OK)

            {

                return;

            }

            string filename = dlg.FileName ;               //打开一个文件打开对话框

            using(Stream fs = new FileStream(filename,FileMode.Open))  

            using (StreamReader reader = new StreamReader(fs,Encoding.Default))

            {

                reader.ReadLine();         //跳过第一行

                using (SqlConnection conn = SQLHelper.CreateConnection())

                using (SqlCommand cmd = conn.CreateCommand())

                using (SqlTransaction tx = conn.BeginTransaction())

                {

                    try

                    {

                        string sql ="Insertinto [rupeng].[dbo].[T_PhoneAreas](MobilePreFix,Area,MobileType) values(@MobilePreFix,@Area,@MobileType)";

                        cmd.CommandText = sql;

                        cmd.Transaction = tx;

                        string line;

                        while ((line = reader.ReadLine())!= null)

                        {

                           

                            if (string.IsNullOrEmpty(line))

                            {

                                break;

                            }

                            string[] strs = line.Split(',');

                            string phonePreFix =strs[1].Trim('"');

                            string area = strs[2].Trim('"');

                            string mobileType =strs[3].Trim('"');

                            cmd.Parameters.Add(newSqlParameter { ParameterName = "@MobilePreFix", Value = phonePreFix});

                            cmd.Parameters.Add(newSqlParameter { ParameterName = "@Area", Value = area });

                            cmd.Parameters.Add(newSqlParameter { ParameterName = "@MobileType", Value = mobileType });

                            cmd.ExecuteNonQuery();

                            cmd.Parameters.Clear();

                        }

                        cmd.Transaction .Commit();

                    }

                    catch (Exception ex)

                    {

                        cmd.Transaction .Rollback();

                        MessageBox.Show("导入出错" + ex.Message);

                    }

                }

            }

            MessageBox.Show("导入成功");

         

        }

 

        private void checkBtn_Click(object sender,EventArgs e)

        {

            string phoneNum = textBox1.Text;

            string phonePreFix = phoneNum.Substring(0,7);

            DataTable table = SQLHelper.ExecuteQuery("selectArea,MobileType from [rupeng].[dbo].[T_PhoneAreas] whereMobilePreFix=@MobilePreFix",

                new SqlParameter { ParameterName= "@MobilePreFix", Value = phonePreFix });

            if (table.Rows.Count <= 0)

            {

                MessageBox.Show("没有此字段");

            }

            else

            {

                DataRow row = table.Rows[0];

                string area = (string)row["Area"];

                string mobileType = (string)row["MobileType"];

                MessageBox.Show(area + mobileType);

            }

        }

       

    }