// Use Easy5DB;
// CREATE TABLE tb_SelCustomer//(// ID INT IDENTITY(1,1) PRIMARY KEY, /*ID,主键*/// Name varchar(20) NOT NULL, /*姓名*/// Sex char(1) default('0'), /*性别：0为男，1为女，默认为0*/// CustomerType char(1) default('0'), /*客户类型：0为普通用户，1为VIP用户，默认为0*/// Phone varchar(12), /*联系电话*/// Email varchar(50), /*电子邮件*/// ContactAddress varchar(200), /*联系地址*/// Lat float, /*所在位置维度，用于在地图显示*/// Lng float, /*所在位置经度，用于在地图显示*/// Postalcode varchar(10), /*邮政编码*/// Remark varchar(50) /*备注*///) using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data.SqlClient;
using System.Data;namespace StartUp
{ class Program { static void Main(string[] args) { //TestExecuteNonQuery(); //TestExecuteExcuteReader(); //TestExecuteScalar(); //TestBeginExecuteNonQuery(); //TestSqlParamterExecuteNonQuery("测试客户1", "123456789123", "", "中国深圳南山区"); TestExecuteScalarForGetInsertRowId(); }#region Command 对象的普通操作
private static void TestExecuteNonQuery()
{ string connStr = @"Data Source=.\SQLEXPRESS;Initial Catalog=Easy5DB;User ID=sa;Password=123456"; using (SqlConnection connection = new SqlConnection(connStr)) { try { connection.Open();using (SqlCommand command = new SqlCommand())
{ StringBuilder strSQL = new StringBuilder(); strSQL.Append("insert into tb_SelCustomer "); strSQL.Append("values("); strSQL.Append("'liuhao','0','0','13822223333','liuhaorain@163.com','广东省深圳市宝安区',12.234556,34.222234,'422900','备注信息')");command.Connection = connection;
command.CommandType = CommandType.Text; command.CommandText = strSQL.ToString();try
{ int rows = command.ExecuteNonQuery(); Console.WriteLine("影响行数：{0}", rows); Console.ReadKey(); } catch (Exception) { Console.WriteLine("执行命令失败"); Console.ReadKey(); } }}
catch (Exception) { Console.WriteLine("打开数据库失败"); Console.ReadKey(); } } }/// <summary>
/// SqlDataReader sqlDataReader = command.ExecuteReader();基于连接 /// for (int i = 0; i < sqlDataReader.FieldCount; i++) ///{ /// Console.WriteLine("{0}:{1}", sqlDataReader.GetName(i), sqlDataReader.GetValue(i)); ///} /// ///看到用SqlDataReader不符合快速开发应用程序（RAD）， ///完美的解决方案是用SqlDataAdapter /// </summary> private static void TestExecuteExcuteReader() { string connStr = @"Data Source=.\SQLEXPRESS;Initial Catalog=Easy5DB;User ID=sa;Password=123456"; using (SqlConnection connection = new SqlConnection(connStr)) { try { connection.Open();using (SqlCommand command = new SqlCommand())
{ StringBuilder strSQL = new StringBuilder(); strSQL.Append("select * from tb_SelCustomer");command.Connection = connection;
command.CommandType = CommandType.Text; command.CommandText = strSQL.ToString();try
{ SqlDataReader sqlDataReader = command.ExecuteReader(); while (sqlDataReader.Read()) { for (int i = 0; i < sqlDataReader.FieldCount; i++) { Console.WriteLine("{0}:{1}", sqlDataReader.GetName(i), sqlDataReader.GetValue(i)); }Console.WriteLine("----------------------------");
} Console.ReadKey(); } catch (Exception) { Console.WriteLine("执行命令失败"); Console.ReadKey(); } }}
catch (Exception) { Console.WriteLine("打开数据库失败"); Console.ReadKey(); } } }private static void TestExecuteScalar()
{ string connStr = @"Data Source=.\SQLEXPRESS;Initial Catalog=Easy5DB;User ID=sa;Password=123456"; using (SqlConnection connection = new SqlConnection(connStr)) { try { connection.Open();using (SqlCommand command = new SqlCommand())
{ StringBuilder strSQL = new StringBuilder(); strSQL.Append("Select count(*) from tb_SelCustomer ");command.Connection = connection;
command.CommandType = CommandType.Text; command.CommandText = strSQL.ToString();try
{ int rows = (int)command.ExecuteScalar(); Console.WriteLine("执行ExcuteScalar方法：共{0}行记录", rows); Console.ReadKey(); } catch (Exception) { Console.WriteLine("执行命令失败"); Console.ReadKey(); } }}
catch (Exception) { Console.WriteLine("打开数据库失败"); Console.ReadKey(); } } }#endregion
#region Command对象的异步操作
private static void TestBeginExecuteNonQuery()
{ string connStr = @"Data Source=.\SQLEXPRESS;" + "Initial Catalog=Easy5DB;" + "User ID=sa;Password=123456;" + "Asynchronous Processing=true"; //必须开启SqlConnection的异步查询 using (SqlConnection connection = new SqlConnection(connStr)) { try { connection.Open();using (SqlCommand command = new SqlCommand())
{ StringBuilder strSQL = new StringBuilder(); //插入测试客户 for (int i = 1; i <= 10; ++i) { strSQL.Append("insert into tb_SelCustomer "); strSQL.Append("values('"); string name = "测试客户" + i.ToString(); strSQL.Append(name); strSQL.Append("','0','0','13822223333','liuhaorain@163.com','广东省深圳市宝安区',12.234556,34.222234,'422900','备注信息'); "); }command.Connection = connection;
command.CommandType = CommandType.Text; command.CommandText = strSQL.ToString();try
{ double time = 0.0; IAsyncResult iar = command.BeginExecuteNonQuery();//插入没有完成，在可以别的事情
while (!iar.IsCompleted) { System.Threading.Thread.Sleep(1); ++time; Console.WriteLine("已经插入{0}个", time); } Console.WriteLine("完成", time); Console.ReadKey();command.EndExecuteNonQuery(iar);
} catch (Exception) { Console.WriteLine("执行命令失败"); Console.ReadKey(); } }}
catch (Exception) { Console.WriteLine("打开数据库失败"); Console.ReadKey(); } } }#endregion
/// <summary>
/// 使用参数化查询 /// </summary> /// <param name="name"></param> /// <param name="phone"></param> /// <param name="email"></param> /// <param name="address"></param> private static void TestSqlParamterExecuteNonQuery(string name, string phone, string email, string address) { string connStr = @"Data Source=.\SQLEXPRESS;Initial Catalog=Easy5DB;User ID=sa;Password=123456"; using (SqlConnection connection = new SqlConnection(connStr)) { try { connection.Open();using (SqlCommand command = new SqlCommand())
{ //参数化，避免SQL注入攻击 SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@Phone", SqlDbType.VarChar, 12), new SqlParameter("@Email", SqlDbType.VarChar, 50), new SqlParameter("@Address", SqlDbType.VarChar, 200), new SqlParameter("@Name", SqlDbType.VarChar, 20), };parameters[0].Value = phone;
parameters[1].Value = email; parameters[2].Value = address; parameters[3].Value = name;foreach (var item in parameters)
{ command.Parameters.Add(item); }StringBuilder strSQL = new StringBuilder();
strSQL.Append("Update tb_SelCustomer Set "); strSQL.Append("Phone = @Phone,"); strSQL.Append("Email = @Email,"); strSQL.Append("ContactAddress = @Address "); strSQL.Append("where Name = @Name;");command.Connection = connection;
command.CommandType = CommandType.Text; command.CommandText = strSQL.ToString();try
{ int rows = command.ExecuteNonQuery(); Console.WriteLine("影响行数：{0}", rows); Console.ReadKey(); } catch (Exception e) { Console.WriteLine("执行命令失败:" + e.Message); Console.ReadKey(); } }}
catch (Exception) { Console.WriteLine("打开数据库失败"); Console.ReadKey(); } } }/// <summary>
/// 用SQL Server数据库原生的OUTPUT关键字。 /// /// OUTPUT关键字返回INSERT操作的一个字段（一般是主键ID）。 /// /// 因此我们只要结合OUTPUT关键字以及ExecuteScalar方法， /// 就很容易得到插入行的主键。 /// </summary> private static void TestExecuteScalarForGetInsertRowId() { string connectionString = @"Data Source=.\SQLEXPRESS;Initial Catalog=Easy5DB;User ID=sa;Password=123456"; //string connectionString = @"Data Source=127.0.0.1;Initial Catalog=Easy5DB;User ID=sa;Password=123456"; using (SqlConnection connection = new SqlConnection(connectionString)) { try { connection.Open();using (SqlCommand command = new SqlCommand())
{ StringBuilder strSql = new StringBuilder(); strSql.Append("insert tb_SelCustomer(Name)"); strSql.Append("OUTPUT inserted.ID values(@Name)");command.Connection = connection;
command.CommandType = CommandType.Text; command.CommandText = strSql.ToString();SqlParameter parmName = new SqlParameter("@Name", SqlDbType.VarChar, 20);
parmName.Value = "人才"; command.Parameters.Add(parmName);try
{ int inserteId = (int)command.ExecuteScalar(); Console.WriteLine("InsetID:{0}", inserteId); Console.ReadKey(); } catch (Exception e) { Console.WriteLine("执行命令失败："+ e.Message); Console.ReadKey(); }}
} catch (Exception e) { Console.WriteLine("打开数据库失败："+ e.Message); } } }#region DataAdapter 和 DataSet
/*1.DataAdapter:基于连接的 * 它为外部数据源与本地DataSet集合架起了一座坚实的桥梁， * 将从外部数据源检索到的数据合理正确的调配到本地的DataSet集合中。 * * •表或列名映射：维护本地DataSet表名和列名与外部数据源表名与列名的映射关系。 * （不想SqlDataReader那样人工映射）*
* 2.DataSet:基于非连接的 * DataSet是基于非连接的（不需要连接数据库） */
#endregion
}}