asp.net C#操作資料庫總結 (1/2)

studentnum和studentname.
一、SQL語句:

代碼如下	複製代碼
--create database Demo use Demo create table   Student ( studentnum char(14) primary key, studentname varchar(30) not null )

insert into Student values('20041000010201','張揚')
二、代碼：
1.引入名稱空間:using System.Data.SqlClient;
2.定義連接字串,連線物件，命令對象:
   private String connectionstr;
   private SqlConnection connection;
   private SqlCommand command;
3.在建構函式中初始化連接字串，連線物件，命令對象

   (1)初始化連接字串：
    方式① connectionstr="server=localhost;uid=sa;pwd=123456;database=Demo";
    方式② connectionstr="server=127.0.0.1";Integrade Security=SSPI;database=Demo";
    其中，SIMS是我要串連的資料庫名.(1)中的uid 和pwd是你登入資料庫的登入名稱和密碼
    註：這種串連是串連本地的資料庫，若要串連區域網路內其它機子上的資料庫，可將方式①的"server=localhost;"改為"server=資料庫所在機子的IP;"

代碼如下

複製代碼

//        連接字串：String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=product.mdb"; //        建立串連：OleDbConnection connection = new OleDbConnection(connectionString); //        使用OleDbCommand類來執行Sql語句： //        OleDbCommand cmd = new OleDbCommand(sql, connection); //        connection.Open();         //        cmd.ExecuteNonQuery();         #endregion         #region 連接字串         //string strcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:程式書籍軟體c#程式碼access資料庫操作addressList.mdb"; //絕對路徑     //    string strcon = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+Environment.CurrentDirectory+"\addressList.mdb";

//相對路徑

   (2)初始化連線物件           
      connection = new SqlConnection(connectionstr);
   (3)初始化命令對象
      command =new SqlCommand();
      command .Connection =connection ;
4.操作資料庫中的資料
   (1)查詢資料庫中的資料
   方法一：  
     

代碼如下

複製代碼

string snum=tBstudentnum .Text .Trim ();              string str = "select * from Student where studentnum='" + snum + "'";              command .CommandText =str;              connection.Open();              if (command.ExecuteScalar() == null)              {                  MessageBox.Show("您輸入的學號對應的學生不存在！", "錯誤",   MessageBoxButtons.OK,MessageBoxIcon.Error);              }              else              {                  SqlDataReader sdr = command.ExecuteReader();                  while (sdr.Read())                  {                     tBstudentnum .Text = sdr["studentnum"].ToString();                     tBstudentname.Text = sdr["studentname"].ToString();                  }                  sdr.Close();              }              connection.Close();

   方法二：     
          

代碼如下

複製代碼

string snum=tBstudentnum .Text .Trim ();              string str = "select * from Student where studentnum='" + snum + "'";              command .CommandText =str;              connection.Open();              if (command.ExecuteScalar() == null)              {                  MessageBox.Show("您輸入的學號對應的學生不存在！", "錯誤",                                   MessageBoxButtons.OK,MessageBoxIcon.Error);                         }              else              {                  SqlDataAdapter sda = new SqlDataAdapter(str,connection );                  DataSet ds = new DataSet();                  sda.Fill(ds, "Student");                  DataTable dt = ds.Tables["Student"];                  tBstudentnum.Text = dt.Rows[0]["studentnum"].ToString();                  tBstudentname.Text = dt.Rows[0]["studentname"].ToString();              }              connection.Close();

(2)向資料庫中添加資料
       方法一:
          

代碼如下

複製代碼

string snum = tBstudentnum.Text.Trim ();              string sname = tBstudentname.Text.Trim();              if (snum == "" || sname == "")              {                  MessageBox.Show("學生學號或姓名不可為空！", "錯誤", MessageBoxButtons.OK,                                   MessageBoxIcon.Error);              }              else              {                  string insertstr="insert into Student values('"+snum +"','"+sname +"')";                  command.CommandText = insertstr;                  connection.Open();                  command.ExecuteNonQuery();                  MessageBox.Show("學生添加成功！", "提示", MessageBoxButtons.OK,                      MessageBoxIcon.Information);                  connection.Close();              }

方法二:
   

代碼如下

複製代碼

string str = "select * from Student";            string insertstr = "insert into Student values('" + snum + "','" + sname + "')";            SqlDataAdapter sda = new SqlDataAdapter(str, connection);            DataSet ds = new DataSet();            sda.Fill(ds, "Student");            DataTable dt = ds.Tables["Student"];            DataRow dr = dt.NewRow();            dr["studentnum"] = snum;            dr["studentname"] = sname;            dt.Rows.Add(dr);            sda.InsertCommand = new SqlCommand(insertstr, connection);            sda.Update(ds, "Student");            MessageBox.Show("學生添加成功！", "提示", MessageBoxButtons.OK,                                   MessageBoxIcon.Information);

首頁 1 2 末頁