ADO.net 2

一 、连接SQL数据库

class Program
    {
        static void Main(string[] args)
        {//1.定义连接字符串
            //SQL身份验证
            //string constr = "data source=.;initial catalog=TestSchool;user id=sa;passwrod=admin";
            //windows身份验证
              string constr = "data source=.;initial catalog=TestSchool;integrated security=true";
         //2.根据链接字符串创建连接对象
           //SqlConnection con = new SqlConnection();
              using (SqlConnection con = new SqlConnection())
              {
                  con.StateChange += new System.Data.StateChangeEventHandler(con_StateChange);//按tab
                  con.ConnectionString = constr;
                  con.Open();//这句话是连接数据库的操作，如果连接失败，就会在这里出现异常。
                  if (con.State == System.Data.ConnectionState.Closed)
                  {
                      con.Open();
                  }
             
                   Console.WriteLine("open");
               }
           // con.Close();//可以重复关闭
           // con.Dispose();
              Console.ReadKey();
        }

        static void con_StateChange(object sender, System.Data.StateChangeEventArgs e)
        {
            Console.WriteLine(e.CurrentState.ToString());
        }
        //static void con_StateChange(object sender, System.Data.StateChangeEventArgs e)
        //{
        //    Console.WriteLine(e.CurrentState.ToString());
        //}
        //SqlCommandBuilder这个类
    }

二、将输出连接SQL的字符串

class Program
    {
        static void Main(string[] args)
        {
            //SqlCommandBuilder scsb = new SqlCommandBuilder();
            //scsb.DataSource = "OEM-20120503WEN";

            SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
            scsb.DataSource = "OEM-20120503WEN";
            scsb.InitialCatalog = "TestSchool";
            scsb.UserID = "sa";
            scsb.Password = "y478950";
            scsb.PersistSecurityInfo = true;

            Console.WriteLine(scsb.ConnectionString);
            Console.ReadKey();
        }
    }

---------------------------------------------------------------------------------------------------------------------------

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

        private void button1_Click(object sender, EventArgs e)
        { //属性控件，可以获取连接SQL数据库的字符串
            SqlConnectionStringBuilder scab = new SqlConnectionStringBuilder();
            propertyGrid1.SelectedObject = scab;

        }

        private void button2_Click(object sender, EventArgs e)
        {
            SqlConnectionStringBuilder scab = (SqlConnectionStringBuilder)propertyGrid1.SelectedObject;
            MessageBox.Show(scab.ConnectionString.ToString());

        }
    }

三、ADO中实现SQL的插入、删除、修改

 public partial class insertDeleteUpdate : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
//插入
        protected void Button1_Click(object sender, EventArgs e)
        {//1.连接字符串
            string constr = "data source=.;initial catalog=TestSchool;integrated security=true";
         //2.创建连接对象
            using (SqlConnection con = new SqlConnection(constr))
            {
                //打开连接
                con.Open();
                //拼接SQL对象
                string sql=string.Format("insert into Class values('{0}','{1}')",txtClassName.Text.Trim(),txtClassDesc.Text.Trim());
                //3.创建命令对象
                using (SqlCommand cmd =new SqlCommand(sql,con))
                {
                int r=cmd.ExecuteNonQuery();
                    this.Response.Write("插入"+r+"行。");
                }
            }
        }
//删除
        protected void btnDelete_Click(object sender, EventArgs e)
        {
            //1连接字符串
            string constr = "data source=.;initial catalog=School;User ID=sa;Password=admin";
            //2创建连接对象
            using (SqlConnection con = new SqlConnection(constr))
            {
                //con.ConnectionString = constr;
                //打开连接
                con.Open();

                //拼接sql语句
                string sql = string.Format("delete from  Class where clsId={0}", txtId.Text.Trim());
                //3创建命令对象
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    //cmd.Connection = con;
                    //cmd.CommandText = sql;
                    int t = cmd.ExecuteNonQuery();//执行insert，delete ，update正合适
                    //它的返回值就是执行命令后影响数据表中记录的条数。（正整数0---n）

                    this.Response.Write("删除" + t + "行。");
                }
            }
        }
//修改
        protected void btnUpdate_Click(object sender, EventArgs e)
        {
            //1连接字符串
            string constr = "data source=.;initial catalog=School;User ID=sa;Password=admin";
            //2创建连接对象
            using (SqlConnection con = new SqlConnection(constr))
            {
                //con.ConnectionString = constr;
                //打开连接

                //拼接sql语句
                string sql = string.Format("update Class set cName='{0}',cDescription='{1}' where clsId={2}", txtUpdateCname.Text.Trim(), txtUpdateCdesc.Text.Trim(), txtUpdateId.Text.Trim());
                //3创建命令对象
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    //cmd.Connection = con;
                    //cmd.CommandText = sql;
                    con.Open();//
                    int t = cmd.ExecuteNonQuery();//执行insert，delete ，update正合适
                    //它的返回值就是执行命令后影响数据表中记录的条数。（正整数0---n）

                    this.Response.Write("修改了" + t + "行。");
                }
            }
        }

    }