AdoNetFirstDay

01SqlConnectionDemo

点击查看代码

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

namespace _01SqlConnectionDemo
{
class Program
{
static void Main(string[] args)
{
#region 01第一个链接对象
//连接字符串：就是对我们的连接进行设的字符串。
//server:表示连接的服务，可以用 . 机器名 ip地址等表示
//uid：sqlserver用户名 pwd：密码
//database表示要连接的数据库。
//string connStr = "server=127.0.0.1;uid=sa;pwd=123456;database=demo";
//SqlConnection conn =new SqlConnection(connStr);

////这才是真正的打开数据库
//conn.Open();//如果链接成功了，那么不会抛出异常。
//Console.WriteLine("打开数据库");

////Thread.Sleep(1000);

//conn.Close();
//conn.Dispose();
//Console.WriteLine("数据库关闭了");
#endregion

#region 02 SqlCommand对象
//连接上数据库，然后往 数据库中添加一条数据库。

//string strConn = "server=(local);database=demo;uid=sa;pwd=123456";
//string strConn = "Data Source=127.0.0.1;Initial Catalog=demo;User ID=sa;Password=123456";

string strConn = "Data Source=127.0.0.1;Initial Catalog=Hotel;Integrated Security=True;";
//根据链接字符串创建了一个链接对象
#region trycatch写法
//SqlConnection conn = new SqlConnection(strConn);
//try
//{
// //创建一个Sql命令对象
// SqlCommand cmd = new SqlCommand();
// //给命令对象指定 连接对象。
// cmd.Connection = conn;

// conn.Open();//一定要在执行命令之前打开就可以了。

// //此属性放我们的sql脚本
// cmd.CommandText = "insert into DboUserInfo(Name,DelFlag,Demo,cons)values('ss5559999',0,'sss',0)";

// cmd.ExecuteNonQuery();//执行一个非查询sql语句，返回受影响的行数。
//}
//finally
//{
// conn.Close();//*****不要忘记关闭数据库连接。
//}
#endregion

using ( SqlConnection conn = new SqlConnection(strConn))
{
//创建一个Sql命令对象
using (SqlCommand cmd = new SqlCommand())
{
//给命令对象指定 连接对象。
cmd.Connection = conn;

conn.Open(); //一定要在执行命令之前打开就可以了。

//此属性放我们的sql脚本
cmd.CommandText = "insert into DboUserInfo(Name,DelFlag,Demo,cons)values('4444448888',0,'sss',0)";

cmd.ExecuteNonQuery(); //执行一个非查询sql语句，返回受影响的行数。

//cmd.CommandText = "sel";
//cmd.ExecuteNonQuery()。。。。
}
}


#endregion

Console.ReadKey();

}

//public int Id { get; set; }
}
}

02ConnectionPoolDemoTest

点击查看代码

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

namespace _02ConnectionPoolDemoTest
{
    class Program
    {
        static void Main(string[] args)
        {
            string connStrPool = "Data Source=.;Initial Catalog=test01;User ID=sa;Password=123;Min Pool Size=4";

            string connStr = "Data Source=.;Initial Catalog=test01;User ID=sa;Password=123;Pooling=false";
            int i = 0;
            Stopwatch sw =new Stopwatch();
            sw.Start();
            while (i<1000)
            {
                using (SqlConnection conn = new SqlConnection(connStrPool))
                {
                    conn.Open();
                }
                i++;
            }
            sw.Stop();
            Console.WriteLine(sw.Elapsed.Milliseconds);

            sw.Reset();
            sw.Restart();
            i = 0;
            while (i < 1000)
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    conn.Open();
                }
                i++;
            }
            sw.Stop();
            
            Console.WriteLine(sw.Elapsed.Milliseconds);
            Console.ReadKey();
        }
    }
}

03SqlConnectionStringBuilderDemo

点击查看代码

using System;
using System.Collections.Generic;
using System.ComponentModel;
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 _03SqlConnectionStringBuilderDemo
{
    public partial class MainFrm : Form
    {
        public MainFrm()
        {
            InitializeComponent();

            SqlConnectionStringBuilder  scsb =new SqlConnectionStringBuilder();
            scsb.UserID = "sa";
            scsb.DataSource = ".";

            this.propGrid4ConString.SelectedObject = scsb;
        }

        private void btnGetString_Click(object sender, EventArgs e)
        {
            string str = this.propGrid4ConString.SelectedObject.ToString();
            Clipboard.Clear();
            Clipboard.SetText(str);
            MessageBox.Show(str);
        }
    }
}

点击查看代码

namespace _03SqlConnectionStringBuilderDemo
{
    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.btnGetString = new System.Windows.Forms.Button();
            this.txtString = new System.Windows.Forms.TextBox();
            this.propGrid4ConString = new System.Windows.Forms.PropertyGrid();
            this.SuspendLayout();
            // 
            // btnGetString
            // 
            this.btnGetString.Location = new System.Drawing.Point(12, 12);
            this.btnGetString.Name = "btnGetString";
            this.btnGetString.Size = new System.Drawing.Size(75, 23);
            this.btnGetString.TabIndex = 0;
            this.btnGetString.Text = "获取链接字符串";
            this.btnGetString.UseVisualStyleBackColor = true;
            this.btnGetString.Click += new System.EventHandler(this.btnGetString_Click);
            // 
            // txtString
            // 
            this.txtString.Location = new System.Drawing.Point(12, 41);
            this.txtString.Multiline = true;
            this.txtString.Name = "txtString";
            this.txtString.Size = new System.Drawing.Size(205, 246);
            this.txtString.TabIndex = 1;
            // 
            // propGrid4ConString
            // 
            this.propGrid4ConString.CategoryForeColor = System.Drawing.SystemColors.InactiveCaptionText;
            this.propGrid4ConString.Location = new System.Drawing.Point(242, 12);
            this.propGrid4ConString.Name = "propGrid4ConString";
            this.propGrid4ConString.Size = new System.Drawing.Size(276, 364);
            this.propGrid4ConString.TabIndex = 2;
            // 
            // MainFrm
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(578, 418);
            this.Controls.Add(this.propGrid4ConString);
            this.Controls.Add(this.txtString);
            this.Controls.Add(this.btnGetString);
            this.Name = "MainFrm";
            this.Text = "生成链接字符串";
            this.ResumeLayout(false);
            this.PerformLayout();

        }

        #endregion

        private System.Windows.Forms.Button btnGetString;
        private System.Windows.Forms.TextBox txtString;
        private System.Windows.Forms.PropertyGrid propGrid4ConString;
    }
}

06DataImport

点击查看代码

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 _06DataImport
{
    public partial class MainFrm : Form
    {
        public MainFrm()
        {
            InitializeComponent();
        }

        private void btnSelectDataFile_Click(object sender, EventArgs e)
        {
            using (OpenFileDialog ofd = new OpenFileDialog())
            {
                ofd.Filter = "文本文件|*.txt";
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    this.txtFilePath.Text = ofd.FileName;

                    //导入数据工作
                    ImportData(ofd.FileName);
                    MessageBox.Show("OK");
                }
            }
        }

        //做数据导入工作
        private void ImportData(string fileName)
        {
            string temp = string.Empty;
            //第一步：拿到文件
            //File.ReadAllLines();
            using (StreamReader reader = new StreamReader(fileName, Encoding.UTF8))
            {
                reader.ReadLine();//去掉第一行。
                //string connStr = "server=.;uid=sa;pwd=123456;database=itcastdb";
                string connStr = ConfigurationManager.ConnectionStrings["sqlConn"].ConnectionString;

                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    using (SqlCommand cmd = conn.CreateCommand())
                    {
                        conn.Open();//连接只能打开一次。
                        while (!string.IsNullOrEmpty(temp = reader.ReadLine()))
                        {
                            //把字符串进行分割然后生成一条sql插入到数据库中去。
                            var strs = temp.Split(',');
                            string sql = string.Format(@"
                     insert into tblStudent
                     (stuName,stuSex,stuBirthDate,stuPhone)
                     values('{0}','{1}','{2}','{3}')", strs[1], strs[2], strs[3], strs[4]);
                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                        }// end  while
                    }//end  using  cmd
                }//end using conn
            }//end  reader
        }
    }
}

点击查看代码

namespace _06DataImport
{
    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.btnSelectDataFile = new System.Windows.Forms.Button();
            this.txtFilePath = new System.Windows.Forms.TextBox();
            this.SuspendLayout();
            // 
            // btnSelectDataFile
            // 
            this.btnSelectDataFile.Location = new System.Drawing.Point(430, 11);
            this.btnSelectDataFile.Name = "btnSelectDataFile";
            this.btnSelectDataFile.Size = new System.Drawing.Size(75, 23);
            this.btnSelectDataFile.TabIndex = 0;
            this.btnSelectDataFile.Text = "打开";
            this.btnSelectDataFile.UseVisualStyleBackColor = true;
            this.btnSelectDataFile.Click += new System.EventHandler(this.btnSelectDataFile_Click);
            // 
            // txtFilePath
            // 
            this.txtFilePath.Location = new System.Drawing.Point(13, 13);
            this.txtFilePath.Name = "txtFilePath";
            this.txtFilePath.Size = new System.Drawing.Size(411, 21);
            this.txtFilePath.TabIndex = 1;
            // 
            // MainFrm
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(517, 436);
            this.Controls.Add(this.txtFilePath);
            this.Controls.Add(this.btnSelectDataFile);
            this.Name = "MainFrm";
            this.Text = "文件导入";
            this.ResumeLayout(false);
            this.PerformLayout();

        }

        #endregion

        private System.Windows.Forms.Button btnSelectDataFile;
        private System.Windows.Forms.TextBox txtFilePath;
    }
}

点击查看代码

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

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

07SqlDataReader

点击查看代码

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

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

        private void MainFrm_Load(object sender, EventArgs e)
        {
            List<StudentInfo> stuList =new List<StudentInfo>();

            //加载数据库中数据  
            string conStr = "server=.;uid=sa;pwd=123456;database=itcastdb";
            using (SqlConnection con = new SqlConnection(conStr))
            {
                using (SqlCommand cmd =con.CreateCommand())
                {
                    con.Open();
                    cmd.CommandText = "select  stuName,stuId, stuSex, stuBirthdate, stuPhone from [tblStudent] ";
                    //reader  指向了  sql命令执行后的查询结果。
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        //reader每执行一次read操作，就会让指针往下一条数据走
                        while (reader.Read())
                        {
                            #region 参考
                            // //reader 取数据
                            //int stuId= reader.GetInt32(1);
                            ////string stuName = reader.GetString(0);

                            ////建议用下面
                            //string stuName2 = reader["stuName"].ToString();
                            //Console.WriteLine(
                            //    string.Format("{0}-{1}-{2}-{3}", reader["stuName"].ToString(),reader["stuId"].ToString(),reader["stuBirthdate"].ToString(),reader["stuPhone"].ToString())
                            //    ); 
                            #endregion
                            StudentInfo stu =new StudentInfo();
                            stu.StuId = int.Parse(reader["stuId"].ToString());
                            stu.StuName = reader["stuName"] == DBNull.Value ? string.Empty : reader["stuName"].ToString();

                            stu.StuPhone = reader["stuPhone"].ToString();
                            stu.StuSex = reader["stuSex"].ToString()[0];
                            stu.DateTime = DateTime.Parse(reader["stuBirthdate"]==DBNull.Value?SqlDateTime.MinValue.ToString(): reader["stuBirthdate"].ToString());
                            stuList.Add(stu);
                        }//end  while
                    }// end  using reader
                }//end  usering  cmd
            }//end using connn

            this.dgvStudent.DataSource = stuList;
            //this.dgvStudent.data

            //把数据放到窗体的表格
        }
    }
}

点击查看代码

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

namespace _07SqlDataReader
{
    public class StudentInfo
    {
        public string StuName { get; set; }

        public int StuId { get; set; }

        public DateTime DateTime { get; set; }

        public Char StuSex { get; set; }

        public string StuPhone { get; set; }
    }
}

点击查看代码

namespace _07SqlDataReader
{
    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.dgvStudent = new System.Windows.Forms.DataGridView();
            ((System.ComponentModel.ISupportInitialize)(this.dgvStudent)).BeginInit();
            this.SuspendLayout();
            // 
            // dgvStudent
            // 
            this.dgvStudent.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
            this.dgvStudent.Location = new System.Drawing.Point(66, 12);
            this.dgvStudent.Name = "dgvStudent";
            this.dgvStudent.RowTemplate.Height = 23;
            this.dgvStudent.Size = new System.Drawing.Size(367, 132);
            this.dgvStudent.TabIndex = 0;
            // 
            // MainFrm
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 12F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(529, 376);
            this.Controls.Add(this.dgvStudent);
            this.Name = "MainFrm";
            this.Text = "Form1";
            this.Load += new System.EventHandler(this.MainFrm_Load);
            ((System.ComponentModel.ISupportInitialize)(this.dgvStudent)).EndInit();
            this.ResumeLayout(false);

        }

        #endregion

        private System.Windows.Forms.DataGridView dgvStudent;
    }
}

点击查看代码

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

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