C#使用Ado.Net读写数据库
记性不好,记几个C#使用Ado.Net读写数据库的方式,以便随时取用.
1.使用DataReader方式读取资料
String connString = ConfigurationManager.ConnectionStrings["astt"].ConnectionString;
//如果数据库连接字符串有加密,可以经过解密后重新获得连接字符串
DbConnectionStringBuilder connBuilder = new DbConnectionStringBuilder();
connBuilder.ConnectionString = connString;
connBuilder["Data Source"] = connBuilder["Data Source"]; //可加上解密方法
connBuilder["User ID"] = connBuilder["User ID"]; //可加上解密方法
connBuilder["Password"] = connBuilder["Password"]; //可加上解密方法
connString = connBuilder.ConnectionString;
using (OleDbConnection conn = new OleDbConnection(connString))
{
try
{
conn.Open();
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.CommandText = "select * from s_userm where rownum <= ?";
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
cmd.Parameters.Add("?", OleDbType.Integer).Value = 5; //cmd.Parameters.Add(new OleDbParameter("?", 5));
using (OleDbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
MessageBox.Show(reader.GetString(1), "资料");
}
}
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "error");
return;
}
}
2.使用DataAdapter与DataSet方式读取资料.
String connString = ConfigurationManager.ConnectionStrings["astt"].ConnectionString;
using (OleDbConnection conn = new OleDbConnection(connString))
{
try
{
using (OleDbCommand cmd = new OleDbCommand())
{
cmd.CommandText = "select * from s_userm where rownum <= ?";
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
cmd.Parameters.Add("?", OleDbType.Integer).Value = 5; //cmd.Parameters.Add(new OleDbParameter("?", 5));
DataSet ds = new DataSet();
OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
adapter.Fill(ds, "s_userm");
foreach (DataRow dr in ds.Tables[0].Rows)
{
MessageBox.Show(Convert.ToString(dr["user_nm"]), "资料");
}
//listBox1.DataSource = ds.Tables[0].DefaultView;
//listBox1.DisplayMember = "user_nm";
//listBox1.ValueMember = "user_no";
listBox1.DataSource = ds;
listBox1.DisplayMember = "s_userm.user_nm";
listBox1.ValueMember = "s_userm.user