AdoNetSecondDay

01SqlConnectionInnerConnection

点击查看代码

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace _01SqlConnectionInnerConnection
{
    class Program
    {
        static void Main(string[] args)
        {
            string connStr = "server=.;uid=sa;pwd=123;database=test01";
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    conn.Open();
                    cmd.CommandText = "insert into student_1 (ID,studentname,studentage,score)values(1,'张三',34,34)";
                    cmd.Connection = conn;
                    cmd.ExecuteNonQuery();
                    Console.WriteLine("插入成功");
                    Console.ReadKey();
                }
            }


        }
    }
}

02ProvinceCitySelect

点击查看代码

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

namespace _02ProvinceCitySelect
{
    public partial class MainFrm : Form
    {
        public MainFrm()
        {
            InitializeComponent();
        }

        private void MainFrm_Load(object sender, EventArgs e) 
        {
            //加载数据库中的所有的省的数据
            string connStr = ConfigurationManager.ConnectionStrings["sqlConn"].ConnectionString;

            //创建链接对象
            using (SqlConnection conn =new SqlConnection(connStr))
            {
                using (SqlCommand cmd =conn.CreateCommand())
                {
                    conn.Open();//***********8
                    cmd.CommandText =@"select AreaId, AreaName, AreaPid from [dbo].[AreaFull] where AreaPId=0";
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        while ( reader.Read())
                        {
                            //int AreadId = int.Parse(reader["AreaId"].ToString());
                            //把表格的数据转换成 对象数据
                            AreaInfo areaInfo = new AreaInfo();
                            areaInfo.AreaId = int.Parse(reader["AreaId"].ToString());
                            areaInfo.AreaName = reader["AreaName"].ToString();
                            areaInfo.AreaPId = int.Parse(reader["AreaPId"].ToString());
                            //把省的信息放到 ComboBox中。ComboBox显示信息是 Item对象的ToString（）
                            this.cbxProvince.Items.Add(areaInfo);
                        }
                    }//end useing  reader
                }//end  useing cmd
            }//end  using conn

            this.cbxProvince.SelectedIndex = 0;
        }

        private void cbxProvince_SelectedIndexChanged(object sender, EventArgs e)
        {
            AreaInfo provinceAreaInfo= this.cbxProvince.SelectedItem as AreaInfo;
            //判断是否 拿到的城市为空
            if (provinceAreaInfo == null)
            {
                return;
            }
            //根据省的Id获取所有的城市信息

            //加载数据库中的所有的省的数据
            string connStr = ConfigurationManager.ConnectionStrings["sqlConn"].ConnectionString;

            //创建链接对象
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    conn.Open();//***********8
                    cmd.CommandText = @"select AreaId, AreaName, AreaPid from [dbo].[AreaFull] where AreaPId="+provinceAreaInfo.AreaId;
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        this.cbxCity.Items.Clear();
                        while (reader.Read())
                        {
                            //int AreadId = int.Parse(reader["AreaId"].ToString());
                            //把表格的数据转换成 对象数据
                            AreaInfo areaInfo = new AreaInfo();
                            areaInfo.AreaId = int.Parse(reader["AreaId"].ToString());
                            areaInfo.AreaName = reader["AreaName"].ToString();
                            areaInfo.AreaPId = int.Parse(reader["AreaPId"].ToString());
                            //把省的信息放到 ComboBox中。ComboBox显示信息是 Item对象的ToString（）
                            this.cbxCity.Items.Add(areaInfo);
                        }
                    }//end useing  reader
                }//end  useing cmd
            }//en
            this.cbxCity.SelectedIndex = 0;

        }

        private void btbExport_Click(object sender, EventArgs e)
        {
            #region 选择保存文件
            string fileName = string.Empty;// 保存的文件名
            //让用户选择 要保存的文件路径
            using (SaveFileDialog sfd = new SaveFileDialog())
            {
                if (sfd.ShowDialog() != DialogResult.OK)
                {
                    return;
                }
                fileName = sfd.FileName;
            } 
            #endregion

            // 查询数据数据，写入数据
            string connStr = ConfigurationManager.ConnectionStrings["sqlConn"].ConnectionString;

            using (SqlConnection conn =new SqlConnection(connStr))
            {
                using (SqlCommand cmd =conn.CreateCommand())
                {
                    conn.Open();

                    cmd.CommandText = "select AreaId, AreaName, AreaPid from [dbo].[AreaFull]";

                    using (SqlDataReader reader =cmd.ExecuteReader())
                    {
                        string tempLine = string.Empty;
                        using (StreamWriter writer =new StreamWriter(fileName))
                        {
                            while (reader.Read())
                            {
                                tempLine = reader["AreaId"] + "," +
                                           reader["AreaName"] + "," +
                                           reader["AreaPId"];
                                writer.WriteLine(tempLine);//写入文本文件
                            }
                        }
                      
                    }
                }
            }
        }
    }
}

点击查看代码

namespace _02ProvinceCitySelect
{
    partial class MainFrm
    {
        /// <summary>
        /// 必需的设计器变量。
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// 清理所有正在使用的资源。
        /// </summary>
        /// <param name="disposing">如果应释放托管资源，为 true；否则为 false。</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Windows 窗体设计器生成的代码

        /// <summary>
        /// 设计器支持所需的方法 - 不要
        /// 使用代码编辑器修改此方法的内容。
        /// </summary>
        private void InitializeComponent()
        {
            this.cbxProvince = new System.Windows.Forms.ComboBox();
            this.cbxCity = new System.Windows.Forms.ComboBox();
            this.btbExport = new System.Windows.Forms.Button();
            this.SuspendLayout();
            // 
            // cbxProvince
            // 
            this.cbxProvince.FormattingEnabled = true;
            this.cbxProvince.Location = new System.Drawing.Point(67, 56);
            this.cbxProvince.Name = "cbxProvince";
            this.cbxProvince.Size = new System.Drawing.Size(121, 20);
            this.cbxProvince.TabIndex = 0;
            this.cbxProvince.SelectedIndexChanged += new System.EventHandler(this.cbxProvince_SelectedIndexChanged);
            // 
            // cbxCity
            // 
            this.cbxCity.FormattingEnabled = true;
            this.cbxCity.Location = new System.Drawing.Point(250, 56);
            this.cbxCity.Name = "cbxCity";
            this.cbxCity.Size = new System.Drawing.Size(121, 20);
            this.cbxCity.TabIndex = 0;
            // 
            // btbExport
            // 
            this.btbExport.Location = new System.Drawing.Point(76, 110);
            this.btbExport.Name = "btbExport";
            this.btbExport.Size = new System.Drawing.Size(75, 23);
            this.btbExport.TabIndex = 1;
            this.btbExport.Text = "导出数据";
            this.btbExport.UseVisualStyleBackColor = true;
            this.btbExport.Click += new System.EventHandler(this.btbExport_Click);
            // 
            // MainFrm
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(510, 356);
            this.Controls.Add(this.btbExport);
            this.Controls.Add(this.cbxCity);
            this.Controls.Add(this.cbxProvince);
            this.Name = "MainFrm";
            this.Text = "Form1";
            this.Load += new System.EventHandler(this.MainFrm_Load);
            this.ResumeLayout(false);

        }

        #endregion

        private System.Windows.Forms.ComboBox cbxProvince;
        private System.Windows.Forms.ComboBox cbxCity;
        private System.Windows.Forms.Button btbExport;
    }
}

点击查看代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace _02ProvinceCitySelect
{
    static class Program
    {
        /// <summary>
        /// 应用程序的主入口点。
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new MainFrm());
        }
    }
}

03LoginLockDemo

点击查看代码

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

namespace _03LoginLockDemo
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void btnLogin_Click(object sender, EventArgs e)
        {
            //第一步：先用户的Id，用户名，用户密码，用户错误次数，用户最后的错误时间  where UserName=txtUserName.Text  and UserPwd=txtUserPwd.Text

            string connStr = ConfigurationManager.ConnectionStrings["sqlConn"].ConnectionString;

            using (SqlConnection conn=new SqlConnection(connStr))
            {
                using (SqlCommand cmd =conn.CreateCommand())
                {
                    conn.Open();
                    cmd.CommandText = @"SELECT  [UserId]
                                              ,[UserName]
                                              ,[UserPwd]
                                              ,[LastErrorDateTime]
                                              ,[ErrorTimes]
                                          FROM [UserInfo] 
                                          WHERE UserName='"+txtName.Text
                                          +"' and UserPwd='"+txtPwd.Text+"' ";

                    UserInfo userInfo = null;//查询来的数据封装的对象。

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        //把查询出来的数据赋值到userInfo
                        if (reader.Read())
                        {
                            userInfo =new UserInfo();
                            userInfo.UserId = int.Parse(reader["UserId"].ToString());
                            userInfo.UserPwd = reader["UserPwd"].ToString();
                            userInfo.LastErrorDateTime = DateTime.Parse(reader["LastErrorDateTime"].ToString());
                            userInfo.ErrorTimes = int.Parse(reader["ErrorTimes"].ToString());
                        }

                    }//花括号执行结束之前，链接一直没有关闭，这时候Reader一直占用Connection对象。
                    
                    //如果查询结果为空，说明用户名密码错误，修改错误次数和 错误时间。
                    if (userInfo==null)
                    {
                        //修改 错误时间，错误次数  where UserName=txtUserName.Text
                        cmd.CommandText =
                            "update UserInfo set LastErrorDateTime=getdate(), ErrorTimes=ErrorTimes+1 where UserName='" +
                            txtName.Text.Trim() + "'";
                        cmd.ExecuteNonQuery();
                        MessageBox.Show("用户名密码Error");
                        return;
                    }

                    //如果有数据。后面校验时间 错误次数。
                    if (userInfo.ErrorTimes<3 || DateTime.Now.Subtract(userInfo.LastErrorDateTime).Minutes>15 )
                    {
                        MessageBox.Show("登陆成功！");
                        //
                        cmd.CommandText =
                          "update UserInfo set LastErrorDateTime=getdate(), ErrorTimes=0 where UserId=" +userInfo.UserId;
                        cmd.ExecuteNonQuery();
                    }
                    else
                    {
                        MessageBox.Show("登陆失败！账号被锁定！");
                    }
                }
            }
        }
    }
}

点击查看代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace _03LoginLockDemo
{
    public class UserInfo
    {
        public int UserId { get; set; }

        public string UserName { get; set; }

        public string UserPwd { get; set; }

        public int ErrorTimes { get; set; }

        public DateTime LastErrorDateTime { get; set; }
    }
}

点击查看代码

namespace _03LoginLockDemo
{
    partial class Form1
    {
        /// <summary>
        /// 必需的设计器变量。
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// 清理所有正在使用的资源。
        /// </summary>
        /// <param name="disposing">如果应释放托管资源，为 true；否则为 false。</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Windows 窗体设计器生成的代码

        /// <summary>
        /// 设计器支持所需的方法 - 不要
        /// 使用代码编辑器修改此方法的内容。
        /// </summary>
        private void InitializeComponent()
        {
            this.btnLogin = new System.Windows.Forms.Button();
            this.label1 = new System.Windows.Forms.Label();
            this.txtName = new System.Windows.Forms.TextBox();
            this.label2 = new System.Windows.Forms.Label();
            this.txtPwd = new System.Windows.Forms.TextBox();
            this.SuspendLayout();
            // 
            // btnLogin
            // 
            this.btnLogin.Location = new System.Drawing.Point(89, 121);
            this.btnLogin.Name = "btnLogin";
            this.btnLogin.Size = new System.Drawing.Size(75, 23);
            this.btnLogin.TabIndex = 0;
            this.btnLogin.Text = "登陆";
            this.btnLogin.UseVisualStyleBackColor = true;
            this.btnLogin.Click += new System.EventHandler(this.btnLogin_Click);
            // 
            // label1
            // 
            this.label1.AutoSize = true;
            this.label1.Location = new System.Drawing.Point(74, 41);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(53, 12);
            this.label1.TabIndex = 1;
            this.label1.Text = "用户名：";
            // 
            // txtName
            // 
            this.txtName.Location = new System.Drawing.Point(141, 38);
            this.txtName.Name = "txtName";
            this.txtName.Size = new System.Drawing.Size(100, 21);
            this.txtName.TabIndex = 2;
            // 
            // label2
            // 
            this.label2.AutoSize = true;
            this.label2.Location = new System.Drawing.Point(74, 81);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(41, 12);
            this.label2.TabIndex = 1;
            this.label2.Text = "密码：";
            // 
            // txtPwd
            // 
            this.txtPwd.Location = new System.Drawing.Point(141, 78);
            this.txtPwd.Name = "txtPwd";
            this.txtPwd.Size = new System.Drawing.Size(100, 21);
            this.txtPwd.TabIndex = 2;
            // 
            // Form1
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(479, 387);
            this.Controls.Add(this.txtPwd);
            this.Controls.Add(this.label2);
            this.Controls.Add(this.txtName);
            this.Controls.Add(this.label1);
            this.Controls.Add(this.btnLogin);
            this.Name = "Form1";
            this.Text = "Form1";
            this.ResumeLayout(false);
            this.PerformLayout();

        }

        #endregion

        private System.Windows.Forms.Button btnLogin;
        private System.Windows.Forms.Label label1;
        private System.Windows.Forms.TextBox txtName;
        private System.Windows.Forms.Label label2;
        private System.Windows.Forms.TextBox txtPwd;
    }
}

点击查看代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace _03LoginLockDemo
{
    static class Program
    {
        /// <summary>
        /// 应用程序的主入口点。
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Form1());
        }
    }
}

04DataSetDemos

点击查看代码

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace _04DataSetDemos
{
    public partial class MainFrm : Form
    {
        public MainFrm()
        {
            InitializeComponent();
        }

        private void MainFrm_Load(object sender, EventArgs e)
        {
            //创建一个内存的数据集
            DataSet ds =new DataSet("DS5");

            //创建一张内存表
            DataTable dt1 =new DataTable("dt1");

            //把表放到数据集里面去。
            ds.Tables.Add(dt1);

            //给表定义列
            DataColumn dcName = new DataColumn("Name",typeof(string));
            DataColumn dcAge = new DataColumn("Age",typeof(int));
            DataColumn dcId=new DataColumn("Id",typeof(int));

            //把列放到表里面去。
            dt1.Columns.AddRange(new DataColumn[]{dcId,dcName,dcAge});

            //给表添加数据
            dt1.Rows.Add(1, "老马", 18);
            dt1.Rows.Add(1, "赵黑", 29);
            dt1.Rows.Add(1, "老王", 18);
            dt1.Rows.Add(1, "老汪", 19);




            //-------

            //创建一张内存表
            DataTable dt2 = new DataTable("dt2");

            //把表放到数据集里面去。
            ds.Tables.Add(dt2);

            //给表定义列
            DataColumn dcName2 = new DataColumn("Name", typeof(string));
            DataColumn dcAge2 = new DataColumn("Age", typeof(int));
            DataColumn dcId2 = new DataColumn("Id", typeof(int));

            //把列放到表里面去。
            dt2.Columns.AddRange(new DataColumn[] { dcId2, dcName2, dcAge2 });

            //给表添加数据
            dt2.Rows.Add(1, "老马", 18);
            dt2.Rows.Add(1, "赵黑", 29);
            dt2.Rows.Add(1, "老王", 18);
            dt2.Rows.Add(1, "老汪", 19);


            foreach(DataTable tb in ds.Tables)
            {
                foreach (DataRow dataRow in tb.Rows)
                {
                    Console.WriteLine(dataRow[0]+"  " +dataRow[1]+"  "+dataRow[2]);
                }
            }



        }
    }
}

点击查看代码

namespace _04DataSetDemos
{
    partial class MainFrm
    {
        /// <summary>
        /// 必需的设计器变量。
        /// </summary>
        private System.ComponentModel.IContainer components = null;

        /// <summary>
        /// 清理所有正在使用的资源。
        /// </summary>
        /// <param name="disposing">如果应释放托管资源，为 true；否则为 false。</param>
        protected override void Dispose(bool disposing)
        {
            if (disposing && (components != null))
            {
                components.Dispose();
            }
            base.Dispose(disposing);
        }

        #region Windows 窗体设计器生成的代码

        /// <summary>
        /// 设计器支持所需的方法 - 不要
        /// 使用代码编辑器修改此方法的内容。
        /// </summary>
        private void InitializeComponent()
        {
            this.SuspendLayout();
            // 
            // MainFrm
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(528, 364);
            this.Name = "MainFrm";
            this.Text = "Form1";
            this.Load += new System.EventHandler(this.MainFrm_Load);
            this.ResumeLayout(false);

        }

        #endregion
    }
}

点击查看代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace _04DataSetDemos
{
    static class Program
    {
        /// <summary>
        /// 应用程序的主入口点。
        /// </summary>
        [STAThread]
        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new MainFrm());
        }
    }
}