黑马程序员-ADO.Net

---------------------- Windows Phone 7手机开发、Net培训、期待与您交流！ ----------------------

程序要和数据库交互要通过ado.net进行，通过ado.net能在程序中执行sql。ado.net中提供了对不同数据库的统一操作接口。

连接字符串：

DataSource=.\SQLEXPRESS:AttachDBFilename|DtaDirectory|\Database1.mdf;Integrated Security=True;User Instance=True

ExecuteNoneQuery()返回受影响的行数

ExecuteScalar()返回第一行第一列的值，类型为Object类型

ExecuteReader()返回一行数据，类型为SqlDataReader。

open（）close（）打开连接 关闭连接

close（）：关闭后还能打开

Dispose（）：销毁，不能再用

导入和导出

导入

private void btnInport_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            using(FileStream filestream=File.OpenRead(openFileDialog1.FileName))
            {
                using(SqlConnection conn=new SqlConnection(str))
                {
                    conn.Open();
                    using(StreamReader streamReader=new StreamReader(filestream))
                    {
                        using (SqlCommand cmd = new SqlCommand())
                        {
                            cmd.CommandText = "insert into Table_1 values(@name,@password)";
                            cmd.Connection = conn;
                            string line = null;
                            while ((line = streamReader.ReadLine()) != null)
                            {
                                string[] strs = line.Split('|');
                                string name = strs[0];
                                string pwd = strs[1].ToString();
                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@name",name);
                                cmd.Parameters.AddWithValue("@password",pwd);
                                cmd.ExecuteNonQuery();
                            }
                        }
                    }
                }
            }
            MessageBox.Show("导入成功");
        }

导出

private void button1_Click(object sender, EventArgs e)
        {
            if (saveFileDialog1.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            using (FileStream filestream = File.OpenWrite(saveFileDialog1.FileName))
            {
                using (SqlConnection conn = new SqlConnection(str))
                {
                    conn.Open();
                    using (StreamWriter streamWriter = new StreamWriter(filestream))
                    {
                        using (SqlCommand cmd = new SqlCommand())
                        {
                            cmd.CommandText = "select * from Table_1";
                            cmd.Connection = conn;
                            using (SqlDataReader dr = cmd.ExecuteReader())
                            {
                                while (dr.Read())
                                {
                                    string line = null;                                   
                                    string name = dr.GetString(1);
                                    string pwd = dr.GetString(2);
                                    streamWriter.WriteLine(name + "|" + pwd);
                                }
                            }
                        }
                    }
                }
            }
            MessageBox.Show("保存成功");
        }

 

手机号码归属地查询

导入手机号码数据

        private void button1_Click(object sender, EventArgs e)
        {
            FolderBrowserDialog dlg = new FolderBrowserDialog();
            if (dlg.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            string path = dlg.SelectedPath;
            using(SqlConnection conn=new SqlConnection(connstr))
            {
                conn.Open();
                using(SqlCommand cmd=new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "insert into phonetb values(@sno,@eno,@name)";

                    string[] files=Directory.GetFiles(path,"*.txt",SearchOption.AllDirectories);
                    foreach (string file in files)
                    {
                        string yys = Path.GetFileNameWithoutExtension(file);
                        string[] lines = File.ReadAllLines(file,Encoding.Default);//不用StreamReader,因为文件小，一次性加载不占但是内存，经反编译得知，ReadAllLines默认编码为UTF-8
                        foreach (string line in lines)
                        {
                           // MessageBox.Show(line);
                            string[] strs = line.Split('-');
                            string sno = strs[0];
                            string eno = strs[1];
                            string city = strs[2];

                            cmd.Parameters.Clear();
                            cmd.Parameters.AddWithValue("@sno",sno);
                            cmd.Parameters.AddWithValue("@eno", eno);
                            cmd.Parameters.AddWithValue("@name", city);
                            cmd.ExecuteNonQuery();
                        }
                    }
                }
            }
            MessageBox.Show("成功");
        }

查询手机归属地

 private void button2_Click(object sender, EventArgs e)
        {
            if (textBox1.Text == null||textBox1.Text.Length<=0)
            {
                MessageBox.Show("号码不能为空！");
                return;
            }
            if (textBox1.Text.Length != 11)
            {
                MessageBox.Show("长度必须为11位！");
                return;
            }
            string phoneNo = textBox1.Text.Trim();
            using (SqlConnection conn = new SqlConnection(connstr))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = "select [name] from phonetb where cast(substring(@phoneNo,1,7) as int) between cast(substring(sno,1,7) as int) and cast(substring(eno,1,7) as int) ";
                    cmd.Parameters.AddWithValue("@phoneNo",phoneNo);

                    string name;
                    if (cmd.ExecuteScalar()==null)
                    {
                        name = "没找到！可能数据库没更新！";
                    }
                    else
                    {
                        name = "归属地为:"+cmd.ExecuteScalar().ToString();
                        
                    } 
                    MessageBox.Show(name);
                }
            }
        }

上面输入的手机号码没验证完。。。。

---------------------- Windows Phone 7手机开发、Net培训、期待与您交流！ ----------------------

详细请查看： http://net.itheima.com