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

10 案例登录：
        /// <summary>

        /// 登录成功后清空errorTimes

        /// </summary>

        /// <param name="conStr"></param>

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

        private static void ResetErrorTimes(string conStr, string name)

        {

            using (SqlConnection conn = new SqlConnection(conStr))

            {

                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = "update T_user set errortimes=0 where name=@Name";

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

                    cmd.ExecuteNonQuery();

                }

            }

        }

        /// <summary>

        /// 登录错误后errortimes+1

        /// </summary>

        /// <param name="conStr"></param>

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

        private static void IncErrorTimes(string conStr, string name)

        {

            using (SqlConnection conn = new SqlConnection(conStr))

            {

                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = "update T_user set errortimes+=1 where name=@Name";

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

                    cmd.ExecuteNonQuery();

                }

            }

        }

 

        /// <summary>

        /// 登录三次错误后退出

        /// </summary>

        /// <param name="conStr"></param>

        static void ThreeLogin(string conStr)

        {

            do

            {

                Console.WriteLine("用户名：");

                string name = Console.ReadLine();

                Console.WriteLine("密码：");

                string pwd = Console.ReadLine();

                int errortimes = 0;

                using (SqlConnection conn = new SqlConnection(conStr))

                {

                    conn.Open();

                    using (SqlCommand cmd = conn.CreateCommand())

                    {

                        cmd.CommandText = "select * from T_user where name=@Name ";

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

                        using (SqlDataReader reader = cmd.ExecuteReader())

                        {

                            if (reader.Read())

                            {

                                errortimes = reader.GetInt32(reader.GetOrdinal("errortimes"));

                                if (errortimes >= 3)

                                {

                                    Console.WriteLine("登录错误超过三次，不能再登录了");

                                    break;

                                }

                                else

                                {

                                    if (reader.GetString(reader.GetOrdinal("password")) == pwd)

                                    {

                                        Console.WriteLine("登录成功");

                                        ResetErrorTimes(conStr, name);

                                        break;

                                    }

                                    else

                                    {

                                        Console.WriteLine("登录失败！");

                                        //reader.Close();//在连接没有关闭前不能有其他操作

                                        //cmd.CommandText = "update T_user set errortimes+=1 where name=@Name";

                                        //cmd.ExecuteNonQuery();

                                        IncErrorTimes(conStr, name);

                                    }

 

                                }

 

                            }

                            else

                            {

                                Console.WriteLine("用户不存在,请重新输入！");

                            }

                        }

                    }

 

                }

            } while (true);

        }

11   导入导出数据：
        /// <summary>

        /// 插入数据到数据库中

        /// </summary>

        /// <param name="conStr"></param>

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

        /// <param name="age"></param>

        private static void InsertData(string conStr,string name,int age) {

            using (SqlConnection conn=new SqlConnection(conStr))

            {

                conn.Open();

                using (SqlCommand cmd=conn.CreateCommand())

                {

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

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

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

                    cmd.ExecuteNonQuery();

                }

            }

        }

        /// <summary>

        /// 文件导入数据库

        /// </summary>

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

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

        private void button1_Click(object sender, EventArgs e)

        {

            string conStr = @"Data Source=ZHANGLIANG\SQLEXPRESS;Initial Catalog=MyDB;Integrated Security=True";

            OpenFileDialog ofd=new OpenFileDialog();

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

            {

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

                {

                    using (StreamReader reader=new StreamReader(filestream))

                    {

                        string lines = null;

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

                        {

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

                            string name = line[0];

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

                            InsertData(conStr,name,age);

                        }

                    }

                }

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

            }

        }

        /// <summary>

        /// 导出

        /// </summary>

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

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

        private void button2_Click(object sender, EventArgs e)

        {

            using (SqlConnection conn = new SqlConnection(conStr))

            {

                conn.Open();

                using (SqlCommand cmd = conn.CreateCommand())

                {

                    cmd.CommandText = "select name,age from T_Person";

                    using (SqlDataReader reader = cmd.ExecuteReader())

                    {

                        SaveFileDialog sfd = new SaveFileDialog();

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

                        {

                            ///StringBuilder sb = new StringBuilder();

                            while (reader.Read())

                            {

                                string name = reader.GetString(reader.GetOrdinal("name"));

                                int age = reader.GetInt32(reader.GetOrdinal("age"));

                                using (FileStream stream=new FileStream(sfd.FileName, FileMode.Append))

                                {

                                    using (StreamWriter writer=new StreamWriter(stream,Encoding.Default))

                                    {

                                        writer.WriteLine(string.Format("{0}|{1}",name,age));

                                    }

                                }

                            }

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

                        }

                    }

                }

            }

        }