26
2012
05

c# 数据库进行连接和操作详解

一:数据库连接代码:
SqlConnection objSqlConnection = new SqlConnection ("server = 127.0.0.1;uid = sa; pwd =;database =test");
objSqlConnection.Open();
二:数据库的添加记录代码:
            int i = 0;
          string s1 = "", s2 = "";
          i = Convert.ToInt16(textBox1.Text);
              s1 = textBox2.Text;
          s2 = textBox3.Text;

            SqlConnection objSqlConnection = new SqlConnection("server = 127.0.0.1;uid = sa; pwd =;database =test");
            objSqlConnection.Open();
           
            MessageBox.Show("数据库连接成功", "好");

               try
               {
                  SqlCommand sqlcom = new SqlCommand("insert into info(id,name,sex) values( " + i + ",'" + s1 + "','" + s2 + "')", objSqlConnection);
                  sqlcom.ExecuteNonQuery();
                   MessageBox.Show("添加成功!", "啊");
               }
              catch (Exception a)
               {
                   MessageBox.Show(a.ToString());
               }
            MessageBox.Show("添加成功!", "啊");
}

三:数据库的修改代码:
            int i = 0;
            string s1 = "", s2 = "";
            s1 = textBox2.Text;
            s2 = textBox3.Text;
            if (textBox1.Text.Length == 0)
                i = 0;
            else
                i = Convert.ToInt32(textBox1.Text);

            SqlConnection objSqlConnection = new SqlConnection("server = 127.0.0.1;uid = sa; pwd =;database =test");
            objSqlConnection.Open();
            MessageBox.Show("数据库连接成功", "好");
            try
            {
                SqlCommand sqlcom = new SqlCommand("update info set name='"+s1+"',sex='"+s2+"'"+"where id=" + i, objSqlConnection);
                sqlcom.ExecuteNonQuery();
                MessageBox.Show("修改成功!", "啊");
                objSqlConnection.Close();
            }
            catch (Exception a)
            {
                MessageBox.Show(a.ToString());
            }
四:数据库的删除代码:
            int i = 0;
            string s1 = "", s2 = "";
            s1 = textBox2.Text;
            s2 = textBox3.Text;
            if(textBox1.Text.Length==0)
                i=0;
            else
                 i = Convert.ToInt16(textBox1.Text);
            SqlConnection objSqlConnection = new SqlConnection("server = 127.0.0.1;uid = sa; pwd =;database =test");           
            objSqlConnection.Open();
            MessageBox.Show("数据库连接成功", "好");
            try
            {
                SqlCommand sqlcom = new SqlCommand("delete from info where id="+i, objSqlConnection);

                sqlcom.ExecuteNonQuery();

                MessageBox.Show("删除成功!", "啊");

                objSqlConnection.Close();
            }
            catch (Exception a)
            {
                MessageBox.Show(a.ToString());
            }
五:数据库的查询代码:
            1.类开始:
                  DataTable dt1 = new DataTable();
                  SqlDataAdapter da1 = new SqlDataAdapter();

            2.按钮代码:
            int i = 0,n=0;
            string s1 = "", s2 = "";
            s1 = textBox2.Text;
            s2 = textBox3.Text;
           if (textBox1.Text.Length == 0)
                i = 0;
            else
                i = Convert.ToInt32(textBox1.Text);
            SqlConnection objSqlConnection = new SqlConnection("server = 127.0.0.1;uid = sa; pwd =;database =test");
            objSqlConnection.Open();
            MessageBox.Show("数据库连接成功", "好");

            string query = "SELECT * from info where id="+i;

            DataSet objDataSet = new DataSet();

            SqlDataAdapter obj = new SqlDataAdapter();   
      
            obj.SelectCommand = new SqlCommand(query, objSqlConnection);

            obj.Fill(objDataSet, "info");        

            SqlCommand objSqlCommand = new SqlCommand(query, objSqlConnection);

            SqlDataReader objSqlReader = objSqlCommand.ExecuteReader();

             while (objSqlReader.Read())
                {
                    n += 1;
                    MessageBox.Show("编号: " + objSqlReader.Getvalue(0) + " 姓名:" + objSqlReader.Getvalue(1) + " 性别" + objSqlReader.Getvalue(2));
                }
                if (n == 0)
                    MessageBox.Show("数据库中没有这样的记录!");

六:数据库的查询代码:
       int i = 0;
           // int n = 0;
            string s1 = "", s2 = "";
            string sql;
            s1 = textBox2.Text;
            s2 = textBox3.Text;

            if (textBox1.Text.Length == 0)
            {
                i = 0;
               
            }
            else
                i = Convert.ToInt32(textBox1.Text);
           SqlConnection objSqlConnection = new SqlConnection("server = 127.0.0.1;uid = sa; pwd =;database =test");         
            objSqlConnection.Open();
            MessageBox.Show("数据库连接成功", "好");

            string query = "SELECT * from info where id="+i;

            if(i==0)
                  sql = "select * from info ";
            else
                  sql = "select * from info where id=" + i;

            da1 = new SqlDataAdapter(sql, objSqlConnection);


            dt1.Clear();
            da1.Fill(dt1);
            dataGridView1.DataSource = dt1;


数据库的封装类代码:
class DBClass
    {

        public void dbclass(string sql)
        {
            try
            {
                SqlConnection sqlcon = new SqlConnection("server = 127.0.0.1;uid = sa; pwd =;database =test");
                sqlcon.Open();
            
                SqlTransaction objt = sqlcon.BeginTransaction(); //事物开始
             
                SqlCommand sqlcom = new SqlCommand(sql, sqlcon);
               
                sqlcom.Transaction = objt; //将Command 对象设置为事物处理的对象
           
                sqlcom.ExecuteNonQuery();
                objt.Commit();         //提交事物
                sqlcon.Close();
            }
            catch (Exception a)
            {
                MessageBox.Show(a.ToString());
            }
          
        }
    }

--db2 数据库连接代码:

   string strcon = "Provider = IBMDADB2; Data Source=hfzd;User Id=db2admin;Password=db2admin";
            //string sql = "select * from ADMINISTRATOR.HFZD";
          

            string sql = "delete from ADMINISTRATOR.HFZD where ID=1";

            OleDbConnection olecon = new OleDbConnection(strcon);
            olecon.Open();
            MessageBox.Show("数据库已连接上");
             dt.Clear();
            da = new OleDbDataAdapter(sql, olecon);
            da.Fill(dt);
            dataGridView1.DataSource = dt;
            olecon.Close();

« 上一篇下一篇 »

发表评论:

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。