C# 数据库连接笔记

// 连接
private OleDbConnection oleConnection1 = null;

public static string connection
        {
            get
            {return"Data Source=dormMIS.mdb;Jet OLEDB:Engine Type=5;Provider=Microsoft.Jet.OLEDB.4.0;";}
        }

 DataSet ds //数据源

this.oleConnection1=new OleDbConnection(DormMIS.database.dbConnection.connection); // 指定数据库连接信息

oleConnection1.Open(); //打开连接

OleDbCommand cmd=new OleDbCommand("",oleConnection1);//定义查询数据类

string sql="select * from userinfo where UName='"+name.Text.Trim()+"' and PWD='"+password.Text.Trim()+"'"; //查询语句
cmd.CommandText=sql; 



if (null!=cmd.ExecuteScalar())  //判断查询结果是否为空
{
//.........................遍历查询结果
      OleDbDataReader dr;  //查询结果类
      cmd.CommandText = sql; // 结果查询语句
      dr = cmd.ExecuteReader(); //返回值附给dr
        while (dr.Read()) //遍历返回的结果
         {
             Console.WriteLine(reader[0].ToString());
         }
         dr.Close();

//.........................放入数据表

    ds= new DataSet(); //定义数据源
    string sql="select dormID as 宿舍号,PCome as 来访人,PLook as 被访人,DateCome as 来访日期,DateLeave as 离开日期,"+                "Remark as 备注,RegisterID as 来访编号 from register";
      OleDbDataAdapter adp=new OleDbDataAdapter(sql,oleConnection1);  //表示一组数据命令和数据连接用来填充DataSet
    ds.Clear(); //清空数据源中的记录
    adp.Fill(ds,"register");
    //指定数据表的数据集
    dataGrid1.DataSource = ds.Tables["register"].DefaultView;            
    dataGrid1.CaptionText = "共有"+ds.Tables["register"].Rows.Count+"条记录";    
}
oleConnection1.Close(); //关闭链接


//..........................删除数据表中的记录  注：修改数据库同样用这样的语句
            if (dataGrid1.DataSource!=null&&dataGrid1.CurrentRowIndex>=0&&dataGrid1[dataGrid1.CurrentCell]!=null)
            {
                oleConnection1.Open();
                string sql = "delete * from register where RegisterID="+ds.Tables[0].Rows[dataGrid1.CurrentCell.RowNumber][6]+"";
                OleDbCommand cmd = new OleDbCommand(sql,oleConnection1);
                cmd.ExecuteNonQuery(); //执行和返回影响的行数
                MessageBox.Show("删除成功","提示");
                oleConnection1.Close();
            }