//ADO.NET非连接模型
//实例化Connection对象
SqlConnection conn = new SqlConnection(connString);
string sql = "select SNO,SName from student";
【写法1】默认selectCommand类
//实例化DataAdapter对象
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
//【写法2】标准
//实例化Command
SqlCommand cmd = new SqlCommand(sql, conn);
//实例化DataAdapter
SqlDataAdapter da = new SqlDataAdapter(cmd);
//指定操作类型
da.SelectCommand = cmd;
//打开Connection
conn.Open();
//填充DataSet
da.Fill(ds,"student");
//关闭
conn.Close();
dgvStudent.DataSource = null;
dgvStudent.DataSource = ds.Tables["student"];
===================写入数据库===============
private void button1_Click(object sender, EventArgs e)
{
string sql = "insert into Student(SNO,SName) values({0},'{1}')";
sql = string.Format(sql, txtSNO.Text.Trim(), txtSName.Text.Trim());
//实例化Command
SqlCommand cmd = new SqlCommand(sql, conn);
//实例化DataAdapter
SqlDataAdapter sa = new SqlDataAdapter(cmd);
sa.InsertCommand = cmd;
//打开连接
conn.Open();
//更新
sa.Update(ds.Tables["student"]);
//关闭连接
conn.Close();
MessageBox.Show("更新成功!");
}
======================================
private void LoadData()
{
string sql = "select SNO,SName from Student;select CNO,CName from Course";
conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.SelectCommand = cmd;//指定类型
//指定映射
sda.TableMappings.Add("Table", "student");
sda.TableMappings.Add("Table1", "course");
conn.Open();//打开连接
sda.Fill(ds);//填充数据
conn.Close();//关闭连接
}
======================================
private void LoadDataTables()
{
Dictionary<string, string> DicTest = new Dictionary<string, string>();
DicTest.Add("江苏省", "南京");
DicTest.Add("浙江省", "杭州");
DicTest.Add("湖北省", "武汉");
DicTest.Add("山东省", "济南");
foreach (KeyValuePair<string,string> item in DicTest)
{
listBox1.Items.Add(item.Key + "的省会:"+ item.Value);
//访问方法2
listBox1.Items.Add(DicTest[item.Key]);
}
conn = new SqlConnection(connString);
//准备SQL语句对应的表名
Dictionary<string, string> DicTables = new Dictionary<string, string>();
DicTables.Add("student", "select SNO,SName from Student");
DicTables.Add("course", "select CNO,CName from Course");
foreach (KeyValuePair<string,string> item in DicTables)
{
SqlCommand cmd = new SqlCommand(item.Value, conn);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
conn.Open();
sda.Fill(ds, item.Key);
conn.Close();
}
===============一个完整的综合示例==========================
public partial class Form1 : Form
{
private string connString = ConfigurationManager.ConnectionStrings["connstring"].ToString();
SqlConnection conn = new SqlConnection();
DataSet ds = new DataSet();
private int actionFlag = 0;
public Form1()
{
InitializeComponent();
LoadData();
dgvStudent.DataSource = null;
dgvStudent.DataSource = ds.Tables["student"];
txtSNO.Text = dgvStudent.Rows[0].Cells[0].Value.ToString();
txtSName.Text = dgvStudent.Rows[0].Cells[1].Value.ToString();
groupBox1.Enabled = false;
}
private void LoadData()
{
conn = new SqlConnection(connString);
string sql = "select SNO,SName from Student";
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
conn.Open();
sda.Fill(ds, "student");
conn.Close();
ds.Tables["student"].PrimaryKey = new DataColumn[] {ds.Tables["student"].Columns["SNO"] };
}
private void dgvStudent_SelectionChanged(object sender, EventArgs e)
{
if (dgvStudent.Rows.Count == 0) return;
txtSNO.Text = dgvStudent.CurrentRow.Cells[0].Value.ToString();
txtSName.Text = dgvStudent.CurrentRow.Cells[1].Value.ToString();
}
private void btnAdd_Click(object sender, EventArgs e)
{
actionFlag = 1;
groupBox1.Enabled = true;
txtSNO.Text = string.Empty;
txtSNO.Focus();
txtSName.Text = string.Empty;
//btnCommit.Enabled = true;
}
private void btnUpdate_Click(object sender, EventArgs e)
{
actionFlag = 2;
groupBox1.Enabled = true;
txtSNO.Enabled = false;
}
private void btnCommit_Click(object sender, EventArgs e)
{
groupBox1.Enabled = false;
switch (actionFlag)
{
case 1:
DataRow dr = ds.Tables["student"].NewRow();
dr["SNO"] = txtSNO.Text.Trim().ToString();
dr["SName"] = txtSName.Text.Trim().ToString();
ds.Tables["student"].Rows.Add(dr);
string sql = "insert into Student(SNO,SName) values({0},'{1}')";
sql = string.Format(sql, txtSNO.Text.Trim(), txtSName.Text.Trim());
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.InsertCommand = cmd;
conn.Open();
sda.Update(ds.Tables["student"]);
conn.Close();
break;
case 2:
dr = ds.Tables["student"].Rows.Find(txtSNO.Text.Trim());
if (dr != null)
{
dr.BeginEdit();
dr["SName"] = txtSName.Text.Trim();
dr.EndEdit();
txtSNO.Enabled = true;
}
sql = "update student set SName='" + txtSName.Text.Trim()+"'where SNO="+txtSNO.Text.Trim() ;
cmd = new SqlCommand(sql, conn);
sda = new SqlDataAdapter(cmd);
sda.UpdateCommand = cmd;
conn.Open();
sda.Update(ds.Tables["student"]);
MessageBox.Show("修改成功!");
conn.Close();
break;
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
string sno = txtSNO.Text.Trim();
string sql = "delete from student where SNO=" + sno;
ds.Tables["student"].Rows.Find(sno).Delete();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.DeleteCommand = cmd;
conn.Open();
sda.Update(ds.Tables["student"]);
//sda.Fill(ds, "student");
conn.Close();
}
}