get char crud ole insert object config geb man
- public partial class Form1 : Form
- {
- public Form1()
- {
- InitializeComponent();
- }
- private void Form1_Load(object sender, EventArgs e)
- {
- //把cities表中的数据加载到窗体的datagridview
- string connString = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
- using(SqlConnection sqlconn=new SqlConnection(connString))
- {
- string selectstring = @"select id, cityid, city, provinceid from cities;select * from provinces";
- #region 单张表
- //创建一个适配器类
- //using(SqlDataAdapter sqladapter=new SqlDataAdapter(selectstring,sqlconn))
- //{
- // //此时adapter已经连接到了一个表
- // DataTable dataTable = new DataTable();
- // //将关联表的数据填充到dataTable
- // //sqladapter会自动打开数据库连接,并执行sql脚本
- // sqladapter.Fill(dataTable);
- // //this.dataGridView1.DataSource = dataTable;
- // List<cities> mycitylist = new List<cities>();
- // //类定义时字段{get;set;}不写显示不出来
- // foreach(DataRow datarow in dataTable.Rows)
- // {
- // Console.WriteLine(datarow["id"]+" "+datarow[1]);
- // //把每一行数据封装成city类
- // mycitylist.Add(new cities(){
- // id=int.Parse(datarow["id"].ToString()),
- // cityid=int.Parse(datarow["cityid"].ToString()),
- // city=datarow["city"].ToString(),
- // provinceid = int.Parse(datarow["provinceid"].ToString())
- // });
- // }
- // //把datatable的数据转储成List<city>类型
- // this.dataGridView1.DataSource = mycitylist;
- #endregion
- #region 多张表
- using(SqlDataAdapter sqlDataAdapter =new SqlDataAdapter(selectstring,sqlconn))
- {
- DataSet dataset=new DataSet();
- sqlDataAdapter.Fill(dataset);
- this.dataGridView1.DataSource = dataset.Tables[0];
- }
- #endregion
- }
- }
- }
增删改查
- private void button1_Click(object sender, EventArgs e)
- {
- //把dataGridView修改的数据保存到数据库中
- string connString = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
- string selectstring = @"select id, cityid, city, provinceid from cities;select * from provinces";
- using(SqlDataAdapter dataAdapter=new SqlDataAdapter(selectstring,connString))
- {
- //拿到修改完了之后的datatable
- DataTable dt = this.dataGridView1.DataSource as DataTable;
- //修改后dt的变化映射到数据库中对应表格的变化
- //帮助dataAdapter生成相关的CRUD 的SqlCommand
- using (SqlCommandBuilder sqlcomBulider = new SqlCommandBuilder(dataAdapter))
- {
- dataAdapter.Update(dt);
- }
- }
- MessageBox.Show("保存成功");
手动增删改查
- public static SqlDataAdapter CreateCustomerAdapter(
- SqlConnection connection)
- {
- SqlDataAdapter adapter = new SqlDataAdapter();
- // Create the SelectCommand.
- SqlCommand command = new SqlCommand("SELECT * FROM Customers " +
- "WHERE Country = @Country AND City = @City", connection);
- // Add the parameters for the SelectCommand.
- command.Parameters.Add("@Country", SqlDbType.NVarChar, 15);
- command.Parameters.Add("@City", SqlDbType.NVarChar, 15);
- adapter.SelectCommand = command;
- // Create the InsertCommand.
- command = new SqlCommand(
- "INSERT INTO Customers (CustomerID, CompanyName) " +
- "VALUES (@CustomerID, @CompanyName)", connection);
- // Add the parameters for the InsertCommand.
- command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
- command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
- adapter.InsertCommand = command;
- // Create the UpdateCommand.
- command = new SqlCommand(
- "UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName " +
- "WHERE CustomerID = @oldCustomerID", connection);
- // Add the parameters for the UpdateCommand.
- command.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
- command.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 40, "CompanyName");
- SqlParameter parameter = command.Parameters.Add(
- "@oldCustomerID", SqlDbType.NChar, 5, "CustomerID");
- parameter.SourceVersion = DataRowVersion.Original;
- adapter.UpdateCommand = command;
- // Create the DeleteCommand.
- command = new SqlCommand(
- "DELETE FROM Customers WHERE CustomerID = @CustomerID", connection);
- // Add the parameters for the DeleteCommand.
- parameter = command.Parameters.Add(
- "@CustomerID", SqlDbType.NChar, 5, "CustomerID");
- parameter.SourceVersion = DataRowVersion.Original;
- adapter.DeleteCommand = command;
- return adapter;
- }
sqldataAdapter/dataset/datatable的使用
来源: http://www.bubuko.com/infodetail-2361899.html