c#数据库的两种访问方式总结

 

 

本例子通过8个按钮实现了通过sqlcommand 和dataset来分别实现增、删、改、查的功能，button1～button8 对应相应的代码。尤其是dataset删除时，表格必须要有主键，否则删除不能同步到数据库，

姓名对应的textbox2,生日对应的dateTimePicker1,电话对应的是textbox3.

 

//查询数据库

private void button1_Click(object sender, EventArgs e)
{

listView1.Items.Clear();
string conString = @"Data Source=JSJ123-D0BB945A\SQLEXPRESS;Initial Catalog=ContactsDB;Integrated Security=True;Pooling=False";
SqlConnection con = new SqlConnection(conString);
con.Open();
//MessageBox.Show(con.State.ToString());

string sql = "select * from Contacts";
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandText = sql;

SqlDataReader sdr=com.ExecuteReader();
//textBox1.AppendText("序号"+"\t"+"姓名"+"\t"+"生日"+"\t\t"+"电话号码");
//textBox1.AppendText("\r\n");
while(sdr.Read())
{
//textBox1.AppendText(sdr[0].ToString() + "\t" + sdr[1].ToString() + "\t" + sdr.GetDateTime(2).ToShortDateString() + "\t" + sdr[3].ToString());
//textBox1.AppendText ( "\r\n");

ListViewItem lvi = new ListViewItem(sdr[1].ToString());
lvi.SubItems.Add(sdr.GetDateTime(2).ToShortDateString());
lvi.SubItems.Add(sdr[3].ToString());

listView1.Items.Add(lvi);
}
con.Close();

}

 

//增加一条数据
private void button2_Click(object sender, EventArgs e)
{

string conString = @"Data Source=JSJ123-D0BB945A\SQLEXPRESS;Initial Catalog=ContactsDB;Integrated Security=True;Pooling=False";
SqlConnection con = new SqlConnection(conString);

con.Open();
string sql = "insert into contacts(Name,DayOfBirth,Phone) values('";
sql += textBox2.Text+"','";
sql += Convert.ToDateTime(dateTimePicker1.Text) + "','";
sql += textBox3.Text + "')";


SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandText = sql;

int n=com.ExecuteNonQuery();

if (n == 1)
MessageBox.Show("添加成功");

con.Close();


}

 

//删除一条数据
private void button3_Click(object sender, EventArgs e)
{


string conString = @"Data Source=JSJ123-D0BB945A\SQLEXPRESS;Initial Catalog=ContactsDB;Integrated Security=True;Pooling=False";
SqlConnection con = new SqlConnection(conString);

con.Open();


string sql = "delete from contacts where Name='";
sql += listView1.SelectedItems[0].Text;
sql += "'";

MessageBox.Show(sql);
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandText = sql;

int n = com.ExecuteNonQuery();

if (n == 1)
MessageBox.Show("删除成功");

con.Close();
}


//修改一条数据
private void button4_Click(object sender, EventArgs e)
{
string conString = @"Data Source=JSJ123-D0BB945A\SQLEXPRESS;Initial Catalog=ContactsDB;Integrated Security=True;Pooling=False";
SqlConnection con = new SqlConnection(conString);

con.Open();


string sql = "update contacts set DayOfBirth='";
sql +=Convert.ToDateTime( dateTimePicker1.Text);
sql += "' , Phone='";
sql += textBox3.Text + "' where Name='";
sql += textBox2.Text + "'";

MessageBox.Show(sql);
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandText = sql;

int n = com.ExecuteNonQuery();

if (n == 1)
MessageBox.Show("更新成功");

con.Close();
}

 

//数据查询
private void button5_Click(object sender, EventArgs e)
{
listView1.Items.Clear();
string conString = @"Data Source=JSJ123-D0BB945A\SQLEXPRESS;Initial Catalog=ContactsDB;Integrated Security=True;Pooling=False";
SqlConnection con=new SqlConnection(conString);

string sql = "select * from Contacts";
SqlDataAdapter sda = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
sda.Fill(ds, "Contacts");//获取离线是数据不用打开数据库,见p268页

foreach (DataRow dr in ds.Tables["Contacts"].Rows)
{
ListViewItem lvi = new ListViewItem(dr["Name"].ToString());
lvi.SubItems.Add(dr["DayOfBirth"].ToString());
lvi.SubItems.Add(dr["Phone"].ToString());

listView1.Items.Add(lvi);


}


}

 

//dataset增加一行数据
private void button6_Click(object sender, EventArgs e)
{
string conString = @"Data Source=JSJ123-D0BB945A\SQLEXPRESS;Initial Catalog=ContactsDB;Integrated Security=True;Pooling=False";
SqlConnection con = new SqlConnection(conString);

string sql = "select * from Contacts";
SqlDataAdapter sda = new SqlDataAdapter(sql, con);
DataSet ds = new DataSet();
sda.Fill(ds, "Contacts");//获取离线是数据不用打开数据库,保持数据库状态，原来是打开的还是打开，原来是关闭的，读出数据后，还关闭。

DataRow dr = ds.Tables["Contacts"].NewRow();
dr["Name"] = textBox2.Text;
dr["DayOfBirth"] = dateTimePicker1.Text;
dr["Phone"] = textBox3.Text;
ds.Tables["Contacts"].Rows.Add(dr);

SqlCommandBuilder scb = new SqlCommandBuilder(sda);

sda.Update(ds, "Contacts");

MessageBox.Show("添加成功！");


}

 

//dataset 删除一条数据
private void button7_Click(object sender, EventArgs e)
{
string conString = @"Data Source=JSJ123-D0BB945A\SQLEXPRESS;Initial Catalog=ContactsDB;Integrated Security=True;Pooling=False";
SqlConnection con = new SqlConnection(conString);

string sql = "select * from Contacts";
SqlDataAdapter sda = new SqlDataAdapter(sql, con);

DataSet ds = new DataSet();
sda.Fill(ds, "Contacts");
int index=-1;

foreach (DataRow dr in ds.Tables["Contacts"].Rows)
{
if (dr["Name"].ToString() == listView1.SelectedItems[0].SubItems[0].Text)
index=ds.Tables["Contacts"].Rows.IndexOf(dr);
}

ds.Tables["Contacts"].Rows[index].Delete();
//要想删除有效，必须设置主键

SqlCommandBuilder scb = new SqlCommandBuilder(sda);
sda.Update(ds.Tables["Contacts"]);

MessageBox.Show("删除成功！");
}

 

//dataset修改一条数据。
private void button8_Click(object sender, EventArgs e)
{

string conString = @"Data Source=JSJ123-D0BB945A\SQLEXPRESS;Initial Catalog=ContactsDB;Integrated Security=True;Pooling=False";
SqlConnection con = new SqlConnection(conString);

string sql = "select * from Contacts";
SqlDataAdapter sda = new SqlDataAdapter(sql, con);

DataSet ds = new DataSet();
sda.Fill(ds, "Contacts");//获取离线是数据不用打开数据库,见p268页



foreach (DataRow dr in ds.Tables["Contacts"].Rows)
{
if (dr["Name"].ToString() == listView1.SelectedItems[0].SubItems[0].Text)
{
dr["DayOfBirth"] = dateTimePicker1.Text;
dr["Phone"] = textBox3.Text;
}
}

SqlCommandBuilder scb = new SqlCommandBuilder(sda);
sda.Update(ds.Tables["Contacts"]);

MessageBox.Show("更新成功！");
}

 

 

//这个是让listview1和上面的姓名等信息绑定。

private void listView1_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
textBox2.Text = listView1.SelectedItems[0].SubItems[0].Text;

dateTimePicker1.Text = listView1.SelectedItems[0].SubItems[1].Text;

textBox3.Text = listView1.SelectedItems[0].SubItems[2].Text;
}
catch
{

}
}