private void button1_Click(object sender, EventArgs e)
{//避免增加除主键外的相同记录
string MyCompanyName="深圳唯佳物流公司";
string MyPhone="0589-86523158";
string MySQL="SELECT * FROM Shippers";
string MyConnectionString="Data Source = .;Database = Northwind;uid=sa;pwd=top-each123;Integrated Security=SSPI";
SqlDataAdapter MyAdapter=new SqlDataAdapter(MySQL,MyConnectionString);
SqlCommandBuilder MyBuilder=new SqlCommandBuilder(MyAdapter);
DataSet MySet=new DataSet();
MyAdapter.Fill(MySet, "Shippers");
DataColumn []MyKeys=new DataColumn[2];
MyKeys[0] = MySet.Tables["Shippers"].Columns["CompanyName"];
MyKeys[1] = MySet.Tables["Shippers"].Columns["Phone"];
MySet.Tables["Shippers"].PrimaryKey = MyKeys;
string[] MySupplier = {MyCompanyName ,MyPhone};
DataRow MyFindRow = MySet.Tables["Shippers"].Rows.Find(MySupplier);
if (MyFindRow == null)
{
DataRow MyNewRow =MySet.Tables["Shippers"].NewRow();
MyNewRow["CompanyName"] = MySupplier[0];
MyNewRow["Phone"] = MySupplier[1];
MySet.Tables["Shippers"].Rows.Add(MyNewRow);
MyAdapter.Update(MySet, "Shippers");
MessageBox.Show("增加记录操作成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
else
MessageBox.Show("该记录已经存在！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
private void button2_Click(object sender, EventArgs e)
{//以参数化方式增加数据库记录
string MyCompanyName="深圳唯佳物流公司";
string MyPhone="0589-86523158";
string MyConnectionString = "Data Source = .;Database = Northwind;uid=sa;pwd=top-each123;Integrated Security=SSPI";
SqlConnection MyConnection=null;
try
{
MyConnection=new SqlConnection(MyConnectionString);
MyConnection.Open();
SqlCommand MyCommand=MyConnection.CreateCommand();
MyCommand.CommandText = "INSERT INTO Shippers VALUES(@CompanyName, @Phone)";
MyCommand.Parameters.Add(new SqlParameter("@CompanyName", SqlDbType.VarChar,30));
MyCommand.Parameters.Add(new SqlParameter("@Phone", SqlDbType.VarChar, 30));
MyCommand.Prepare();
MyCommand.Parameters["@CompanyName"].Value =MyCompanyName;
MyCommand.Parameters["@Phone"].Value =MyPhone;
MyCommand.ExecuteNonQuery();
MessageBox.Show("增加记录操作成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch(Exception ex)
{
MessageBox.Show("增加记录出现错误：" + ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
MyConnection.Close();
}
}
private void button3_Click(object sender, EventArgs e)
{//使用事务实现更新多个数据表
try
{
string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True";
string MySQL1 = "INSERT Customers (CustomerID, CompanyName) VALUES ('BOGUS','Bogus Company')";
string MySQL2 = "INSERT Orders (CustomerID, EmployeeID, ShipVia) VALUES ('BOGUS', 1, 1)";
string MySQL3 = "DELETE FROM Orders WHERE CustomerID = 'BOGUS'";
string MySQL4 = "DELETE FROM Customers WHERE CustomerID = 'BOGUS'";
SqlConnection MyConnection = new SqlConnection(MyConnectionString);
SqlTransaction MyTransaction = null;
int MyAffectedCount = 0;
string MyTitle = "";
MyConnection.Open();
try
{
MyTransaction = MyConnection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
MyTitle = "插入操作提示";
SqlCommand MyCommand = new SqlCommand(MySQL1, MyConnection);
MyCommand.CommandType = CommandType.Text;
MyCommand.Transaction = MyTransaction;
MyAffectedCount = MyCommand.ExecuteNonQuery();
MyCommand.CommandText = MySQL2;
MyAffectedCount += MyCommand.ExecuteNonQuery();
MyTransaction.Commit();
MyTitle = "删除操作提示";
MyTransaction = MyConnection.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
MyCommand.CommandText = MySQL3;
MyCommand.Transaction = MyTransaction;
MyAffectedCount += MyCommand.ExecuteNonQuery();
MyCommand.CommandText = MySQL4;
MyAffectedCount += MyCommand.ExecuteNonQuery();
MyTransaction.Commit();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message, MyTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
try
{
MyTransaction.Rollback();
}
catch (SqlException MyEx)
{
MessageBox.Show(MyEx.Message, MyTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
finally
{
MyConnection.Close();
string MyInfo;
if (MyAffectedCount == 4)
MyInfo = "成功实现插入和删除事务操作";
else
MyInfo = "实现插入和删除事务操作失败，请检查Customers和Orders数据表";
MessageBox.Show(MyInfo, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void button4_Click(object sender, EventArgs e)
{//创建隐式事务管理数据库更新
//在【添加引用】对话框的【.NET】标签页列表视图中选择“System.Transactions”选项
using(TransactionScope MyScope = new TransactionScope())
{
string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True";
string MySQL1 = "INSERT Customers (CustomerID, CompanyName) VALUES ('BOGUS','Bogus Company')";
string MySQL2 = "INSERT Orders (CustomerID, EmployeeID, ShipVia) VALUES ('BOGUS', 1, 1)";
string MySQL3 = "DELETE FROM Orders WHERE CustomerID = 'BOGUS'";
string MySQL4 = "DELETE FROM Customers WHERE CustomerID = 'BOGUS'";
SqlConnection MyConnection = new SqlConnection(MyConnectionString);
int MyAffectedCount = 0;
string MyTitle = "";
try
{
MyConnection.Open();
MyTitle = "插入操作提示";
SqlCommand MyCommand= new SqlCommand(MySQL1, MyConnection);
MyCommand.CommandType = CommandType.Text;
MyAffectedCount = MyCommand.ExecuteNonQuery();
MyCommand.CommandText = MySQL2;
MyAffectedCount += MyCommand.ExecuteNonQuery();
MyTitle = "删除操作提示";
MyCommand.CommandText = MySQL3;
MyAffectedCount += MyCommand.ExecuteNonQuery();
MyCommand.CommandText = MySQL4;
MyAffectedCount += MyCommand.ExecuteNonQuery();
MyScope.Complete();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message, MyTitle, MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
MyConnection.Close();
string MyInfo ;
if(MyAffectedCount == 4)
MyInfo = "成功实现插入和删除事务操作";
else
MyInfo = "实现插入和删除事务操作失败，请检查Customers和Orders数据表";
MessageBox.Show(MyInfo, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
}
private void button5_Click(object sender, EventArgs e)
{//以批量方式导入导出数据库记录
string MyConnectionString = "Data Source=.;Initial Catalog=shop;uid=sa;pwd=top-each123;Integrated Security=True";
string MySQL = "SELECT * into 新客户表 From tbl_order Where 1<>1";
SqlConnection MyConnection = null;
try
{
//新建一个数据表“新客户表”
MyConnection = new SqlConnection(MyConnectionString);
SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);
MyCommand.Connection.Open();
MyCommand.ExecuteNonQuery();
//从“Customers”数据表批量导入数据库记录到“新客户表”
DataSet MySet=new DataSet();
SqlDataAdapter MyAdapter=new SqlDataAdapter("Select top 1000 * From tbl_order",MyConnection);
MyAdapter.Fill(MySet);
SqlBulkCopy MyBulkCopy=new SqlBulkCopy(MyConnection);
MyBulkCopy.DestinationTableName = "新客户表";
MyBulkCopy.WriteToServer(MySet.Tables[0]);
MessageBox.Show("从“Customers”数据表批量导入数据库记录到“新客户表”操作成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
MyConnection.Close();
}
}
private void button6_Click(object sender, EventArgs e)
{//合并两个数据表的数据库记录
string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True";
SqlConnection MyConnection = null;
try
{
//创建“Germany”数据表
DataTable MyGermanyTable = new DataTable("Germany");
MyConnection = new SqlConnection(MyConnectionString);
SqlDataAdapter MyAdapter = new SqlDataAdapter("Select * From Customers Where Country='Germany'", MyConnection);
MyAdapter.Fill(MyGermanyTable);
//创建“Mexico”数据表
DataTable MyMexicoTable = new DataTable("Mexico");
MyConnection = new SqlConnection(MyConnectionString);
MyAdapter = new SqlDataAdapter("Select * From Customers Where Country='Mexico'", MyConnection);
MyAdapter.Fill(MyMexicoTable);
//合并两个数据表
MyMexicoTable.Merge(MyGermanyTable);
this.dataGridView1.DataSource = MyMexicoTable;
MessageBox.Show("合并两个数据表操作成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
MyConnection.Close();
}
}
private void button7_Click(object sender, EventArgs e)
{//使用数据表获取数据读取器内容
string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True";
SqlConnection MyConnection = null;
try
{
MyConnection = new SqlConnection(MyConnectionString);
SqlCommand MyCommand = new SqlCommand("Select * From Customers", MyConnection);
MyConnection.Open();
SqlDataReader MyReader = MyCommand.ExecuteReader();
DataTable MyTable = new DataTable();
MyTable.Load(MyReader);
this.dataGridView1.DataSource = MyTable;
MessageBox.Show("使用数据表获取数据读取器内容操作成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
MyConnection.Close();
}
}
private void button8_Click(object sender, EventArgs e)
{//使用数据读取器获取多个结果集
string MyConnectionString = "Data Source=.;Initial Catalog=Northwind;uid=sa;pwd=top-each123;Integrated Security=True";
SqlConnection MyConnection = null;
try
{
//定义并打开SqlConnection 对象
MyConnection=new SqlConnection(MyConnectionString);
MyConnection.Open();
//定义SqlCommand 获取多结果集
String MySQL = "Select top 4 CompanyName From Customers;Select top 5 City,Region From Employees;Select top 6 ProductName From Products";
SqlCommand MyCommand =new SqlCommand(MySQL, MyConnection);
MyCommand.CommandType = CommandType.Text;
//定义并创建SqlDataReader
//当关闭SqlDataReader时关闭数据连接
string MyInfo="";
SqlDataReader MyReader= MyCommand.ExecuteReader(CommandBehavior. CloseConnection);
if(MyReader.HasRows)
{
MyInfo+="\n第1个结果集的第一个字段所有记录数据是：";
while(MyReader.Read())
{
MyInfo+="\n"+MyReader[0].ToString();
}
int MyCount= 1;
while(MyReader.NextResult())
{
MyCount = MyCount + 1;
MyInfo+="\n第"+MyCount+"个结果集的第一个字段所有记录数据是：";
while(MyReader.Read())
{
MyInfo+="\n"+MyReader[0].ToString();
}
}
}
MyReader.Close();
MessageBox.Show(MyInfo, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
if(MyConnection.State ==ConnectionState.Open)
MyConnection.Close();
}
}
private void button9_Click(object sender, EventArgs e)
{//以参数化方式查询数据库记录
SqlConnection MyConnection = null;
try
{
string MySQL = "Select * From Customers Where Country=@MyCountry";
string MyConnectionString = "Data Source = localhost;Database = Northwind;uid=sa;pwd=top-each123;Integrated Security=SSPI";
SqlParameter MySqlParameter = new SqlParameter();
MyConnection = new SqlConnection(MyConnectionString);
MyConnection.Open();
SqlCommand MyCommand = new SqlCommand(MySQL, MyConnection);
MySqlParameter.ParameterName = "@MyCountry";
MySqlParameter.Value = "Germany";
MyCommand.Parameters.Clear();
MyCommand.Parameters.Add(MySqlParameter);
DataTable MyTable = new DataTable();
SqlDataAdapter MyAdapter = new SqlDataAdapter(MyCommand);
MyAdapter.Fill(MyTable);
this.dataGridView1.DataSource = MyTable;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
if (MyConnection.State == ConnectionState.Open)
MyConnection.Close();
}
}
private void button10_Click(object sender, EventArgs e)
{//创建和使用无连接数据表
try
{
//创建数据表
DataColumn[] MyKey = new DataColumn[1];
DataTable MyTable = new DataTable("MyClassmate");
DataColumn MyColumn = new DataColumn();
MyColumn.DataType = System.Type.GetType("System.Int32");
MyColumn.ColumnName = "ID";
MyTable.Columns.Add(MyColumn);
MyKey[0] = MyColumn;
MyTable.PrimaryKey = MyKey;
MyTable.Columns.Add("Name", typeof(String));
MyTable.Columns.Add("Tel", typeof(String));
MyTable.Columns.Add("MP", typeof(String));
MyTable.Columns.Add("Company", typeof(String));
//在数据表中添加记录一
DataRow MyRow = MyTable.NewRow();
MyRow["ID"] = 87121;
MyRow["Name"] = "罗斌";
MyRow["Tel"] = "023-40231026";
MyRow["MP"] = "13036371686";
MyRow["Company"] = "无锡宝特软件有限公司";
MyTable.Rows.Add(MyRow);
//在数据表中添加记录二
MyRow = MyTable.NewRow();
MyRow["ID"] = "87123";
MyRow["Name"] = "蒋兰坤";
MyRow["Tel"] = "023-68015059";
MyRow["MP"] = "13062308583";
MyRow["Company"] = "重庆百货大楼股份有限公司";
MyTable.Rows.Add(MyRow);
//在数据表中添加记录三
MyRow = MyTable.NewRow();
MyRow["ID"] = 87124;
MyRow["Name"] = "王彬";
MyRow["Tel"] = "023-40513307";
MyRow["MP"] = "13883070418";
MyRow["Company"] = "重庆日报印刷厂";
MyTable.Rows.Add(MyRow);
this.dataGridView1.DataSource = MyTable;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}