ADO.Net之初入江湖
// 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", "Test@163.com", "中国深圳南山区");
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
}
}
