黑马程序员+ADO.Net基础(下)

12  优化导入数据
        /// <summary>

        /// 优化导入操作

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void button3_Click(object sender, EventArgs e)

        {

            OpenFileDialog ofd = new OpenFileDialog();

            if (ofd.ShowDialog()==DialogResult.OK)

            {//创建一次连接

                using (SqlConnection conn=new SqlConnection(conStr))

                {

                    conn.Open();

                    using (SqlCommand cmd=conn.CreateCommand())

                    {

                        cmd.CommandText = "insert into T_Person values(@Name,@Age)";

                        using (FileStream stream = File.OpenRead(ofd.FileName))

                        {

                            using (StreamReader reader=new StreamReader(stream))

                            {

                                string lines = null;

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

                                {

                                    string[] line = lines.Split('|');

                                    string name = line[0];

                                    int age = Convert.ToInt32(line[1]);

                                    cmd.Parameters.Clear();//重要！

                                    cmd.Parameters.Add(new SqlParameter("@Name",name));

                                    cmd.Parameters.Add(new SqlParameter("@Age", age));

                                    cmd.ExecuteNonQuery();

                                }

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

                            }

                        }

                    }

                }

            }

        }

13  省市联级（DisplayMember  ComoBox加载时要显示的字段; DropDownStyle  ComoBox 只能下拉菜单;）      
       string conStr = @"Data Source=ZHANGLIANG\SQLEXPRESS;Initial Catalog=MyDB;Integrated Security=True";

        /// <summary>

        /// 加载省

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void Form1_Load(object sender, EventArgs e)

        {

            using (SqlConnection conn=new SqlConnection(conStr))

            {

                conn.Open();

                using (SqlCommand cmd=conn.CreateCommand())

                {

                    cmd.CommandText = "select * from promary ";

                    using (SqlDataReader reader=cmd.ExecuteReader())

                    {

                        while (reader.Read())

                        {

                            Promary promary = new Promary();  

                            promary.ProID = reader.GetInt32(reader.GetOrdinal("proID"));

                            promary.ProName = reader.GetString(reader.GetOrdinal("proName"));

                            cmbpromary.Items.Add(promary); 

                        }

                    }

                }

            }

        }

        /// <summary>

        /// 加载市

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void cmbpromary_SelectedIndexChanged(object sender, EventArgs e)

        {

            City city=new City();

            Promary promary = (Promary)cmbpromary.SelectedItem;

            int i=promary.ProID;

            using (SqlConnection conn=new SqlConnection(conStr))

            {

                conn.Open();

                using (SqlCommand cmd=conn.CreateCommand())

                {

                    cmd.CommandText = string.Format("select * from city where proID='{0}'",promary.ProID);

                    using (SqlDataReader reader=cmd.ExecuteReader())

                    {

                        cmbcity.Items.Clear();

                        while (reader.Read())

                        {

                            city.CityID = reader.GetInt32(reader.GetOrdinal("cityID"));

                            city.CityName = reader.GetString(reader.GetOrdinal("cityName"));

                            cmbcity.Items.Add(city);

                        }

                    }

                }

               

            }

        }

    }

    class City {

        public int CityID { get; set; }

        public string CityName { get; set; }

    }

    class Promary {

        public int ProID { get; set; }

        public string ProName { get; set; }

}

14 读取配置中的连接字符串
  <connectionStrings>

    <add name="ConnectionStr" connectionString=@"Data Source=ZHANGLIANG\SQLEXPRESS;Initial Catalog=MyDB;Integrated Security=True"/>

//获取配置连接字符串

  </connectionStrings>

            stringconstr=ConfigurationManager.ConnectionStrings["ConnectionStr"].ConnectionString

 

15 号码归属地查询
private string constr = ConfigurationManager.ConnectionStrings["newconString"].ConnectionString;

 

        private void button1_Click(object sender, EventArgs e)

      {

          using (SqlConnection conn=new SqlConnection(constr))

          {

              conn.Open();

              using (SqlCommand cmd=conn.CreateCommand())

              {

                  cmd.CommandText = "delete from T_Numbers";

                  cmd.ExecuteNonQuery();

              }

          }

            FolderBrowserDialog fbd = new FolderBrowserDialog();

            fbd.SelectedPath = @"F:\c#\c#基础\手机号码归属地";

            if (fbd.ShowDialog() == DialogResult.OK)

            {

                string path = fbd.SelectedPath;

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

                using (SqlConnection conn = new SqlConnection(constr))

                {

                    conn.Open();

                    using (SqlCommand cmd = conn.CreateCommand())

                    {

                        cmd.CommandText = "insert into T_Numbers values(@StartNumber,@EndNumber,@Name)";

 

                        foreach (var file in files)

                        {

                            string fileName = Path.GetFileNameWithoutExtension(file);

                            string[] lines = File.ReadAllLines(file, Encoding.Default);

                            foreach (var line in lines)

                            {

                                string[] str = line.Split('-');

                                string startNumber = str[0];

                                string endNumber = str[1];

                                string city = str[2];

                                cmd.Parameters.Clear();

                                cmd.Parameters.Add("@StartNumber", startNumber);

                                cmd.Parameters.Add("@EndNumber", endNumber);

                                cmd.Parameters.Add("@Name", fileName + city);

                                cmd.ExecuteNonQuery();

                            }

                        }

                    }

                }

            }

        }

/// <summary>

/// 号码所属地查询

/// </summary>

/// <param name="sender"></param>

/// <param name="e"></param>

        private void button2_Click(object sender, EventArgs e)

        {

            string No = txtNumber.Text.Trim();

            using (SqlConnection conn=new SqlConnection(constr))

            {

                conn.Open();

                using (SqlCommand cmd=conn.CreateCommand())

                {

                    cmd.CommandText = "select * from T_Numbers where startnumber<=@No and @No<=endnumber ";

                    cmd.Parameters.Add("@No",No);

                    using (SqlDataReader reader=cmd.ExecuteReader())

                    {

                        while (reader.Read())

                        {

                            MessageBox.Show(reader.GetString(reader.GetOrdinal("name")));

                        }

                    }

                }

            }

        }