随笔- 173&
&&&&&&&&&&&
datareader对象提供只读单向数据的快速传递，单向:您只能依次读取下一条数据;只读:DataReader中的数据是只读的,不能修改;相对地,DataSet中的数据可以任意读取和修改
01.using (SqlConnection connection = 02.
new SqlConnection(connectionString))
SqlCommand command =
new SqlCommand(queryString, connection);
connection.Open();
SqlDataReader reader = command.ExecuteReader();
// 判断数据是否读到尾.
while (reader.Read())
Console.WriteLine(String.Format("{0}, {1}",
reader[0], reader[1]));
// 一定要关闭 reader 对象.
reader.Close();
SqlDataReader对象GetOrdinal()方法可以查看序列号的值01.SqlCommand cmd = new SqlCommand();
02.//存储过程应用
03.cmd.Connection =
mandType = CommandType.StoredP
mandText = "ListTable";
06.cmd.Parameters.AddWithValue("@fileName", "ClsName, Sort");
07.cmd.Parameters.AddWithValue("@tableName", "Class");
08.cmd.Parameters.AddWithValue("@Sort", null);
09.cmd.Parameters.AddWithValue("@whereStr", "ID & 10");
10.cmd.Parameters.AddWithValue("@groupStr", null);
11.SqlDataReader rdr = cmd.ExecuteReader();
12.int intClsName = rdr.GetOrdinal("ClsName");
13.int intSort = rdr.GetOrdinal("Sort");
15.while (rdr.Read())
Console.WriteLine("ClsName : {0}, Sort:{1}", rdr[intClsName], rdr[intSort]);
//可以调用SqlDataReader 的 Get 方法来避免由于装箱，拆箱而导致的性能损失
Console.WriteLine("ClsName : {0}, Sort:{1}", rdr.GetString(intClsName), rdr.GetInt32(intSort));
//调用SqlDataReader 的 Get 方法在碰到 NULL 值时将会产生一个 SqlNullValueException.可以使用IsDBNull方法判断
articleClass arc = new articleClass();
arc.clsname = rdr[0];
if (rdr.IsDBNull(rdr[1]))
arc.sort =
arc.sort = rdr[1];
arc.clsname = rdr[1];
arc.Add(arc);
30.rdr.Close();
C# 数据库使用SqlCommand进行增，删，改，查询操作using Susing System.Collections.Gusing System.T
// We will make use of the SQL server// provider, however it would also be// permissible to make use of the ADO.NET// factory pattern for greater flexibility.using System.Dusing System.Data.SqlC
namespace AutoLotConnectedLayer{public class InventoryDAL{
#region Connection and Disconnection details.
// This member will be used by all methods.
private SqlConnection sqlCn = new SqlConnection();
public void OpenConnection(string connectionString)
sqlCn.ConnectionString = connectionS
sqlCn.Open();
public void CloseConnection()
sqlCn.Close();
#endregion
#region Insert logic (both versions)
//public void InsertAuto(int id, string color, string make, string petName)
// // Format and execute SQL statement.
// string sql = string.Format("Insert Into Inventory" +
"(CarID, Make, Color, PetName) Values" +
"('{0}', '{1}', '{2}', '{3}')", id, make, color, petName);
// // Execute using our connection.
// using(SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
cmd.ExecuteNonQuery();
public void InsertAuto(int id, string color, string make, string petName)
// Note the 'placeholders' in the SQL query.
string sql = string.Format("Insert Into Inventory" +
"(CarID, Make, Color, PetName) Values" +
"(@CarID, @Make, @Color, @PetName)");
// This command will have internal parameters.
using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
// Fill params collection.
SqlParameter param = new SqlParameter();
param.ParameterName = "@CarID";
param.Value =
param.SqlDbType = SqlDbType.I
cmd.Parameters.Add(param);
param = new SqlParameter();
param.ParameterName = "@Make";
param.Value =
param.SqlDbType = SqlDbType.C
param.Size = 10;
cmd.Parameters.Add(param);
param = new SqlParameter();
param.ParameterName = "@Color";
param.Value =
param.SqlDbType = SqlDbType.C
param.Size = 10;
cmd.Parameters.Add(param);
param = new SqlParameter();
param.ParameterName = "@PetName";
param.Value = petN
param.SqlDbType = SqlDbType.C
param.Size = 10;
cmd.Parameters.Add(param);
cmd.ExecuteNonQuery();
#endregion
#region Delete logic
public void DeleteCar(int id)
// Get ID of car to delete, then do so.
string sql = string.Format("Delete from Inventory where CarID = '{0}'",
using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
cmd.ExecuteNonQuery();
catch (SqlException ex)
Exception error = new Exception("Sorry! That car is on order!", ex);
#endregion
#region Update logic
public void UpdateCarPetName(int id, string newPetName)
// Get ID of car to modify and new pet name.
string sql =
string.Format("Update Inventory Set PetName = '{0}' Where CarID = '{1}'",
newPetName, id);
using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
cmd.ExecuteNonQuery();
#endregion
#region Select logic
public DataTable GetAllInventory()
// This will hold the records.
DataTable inv = new DataTable();
// Prep command object.
string sql = "Select * From Inventory";
using (SqlCommand cmd = new SqlCommand(sql, this.sqlCn))
SqlDataReader dr = cmd.ExecuteReader();
// Fill the DataTable with data from the reader and clean up.
inv.Load(dr);
dr.Close();
#endregion
#region Trigger stored proc logic
public string LookUpPetName(int carID)
string carPetName = string.E
// Establish name of stored proc.
using (SqlCommand cmd = new SqlCommand("GetPetName", this.sqlCn))
mandType = CommandType.StoredP
// Input param.
SqlParameter param = new SqlParameter();
param.ParameterName = "@carID";
param.SqlDbType = SqlDbType.I
param.Value = carID;
param.Direction = ParameterDirection.I
cmd.Parameters.Add(param);
// Output param.
param = new SqlParameter();
param.ParameterName = "@petName";
param.SqlDbType = SqlDbType.C
param.Size = 10;
param.Direction = ParameterDirection.O
cmd.Parameters.Add(param);
// Execute the stored proc.
cmd.ExecuteNonQuery();
// Return output param.
carPetName = ((string)cmd.Parameters["@petName"].Value).Trim();
return carPetN
#endregion
#region Tx Method
// A new member of the InventoryDAL class.
public void ProcessCreditRisk(bool throwEx, int custID)
// First, look up current name based on customer ID.
string fName = string.E
string lName = string.E
SqlCommand cmdSelect = new SqlCommand(
string.Format("Select * from Customers where CustID = {0}", custID), sqlCn);
using (SqlDataReader dr = cmdSelect.ExecuteReader())
while (dr.Read())
fName = (string)dr["FirstName"];
lName = (string)dr["LastName"];
// Create command objects which represent each step of the operation.
SqlCommand cmdRemove = new SqlCommand(
string.Format("Delete from Customers where CustID = {0}", custID), sqlCn);
SqlCommand cmdInsert = new SqlCommand(string.Format("Insert Into CreditRisks" +
"(CustID, FirstName, LastName) Values" +
"({0}, '{1}', '{2}')", custID, fName, lName), sqlCn);
// We will get this from the Connection object.
SqlTransaction tx =
tx = sqlCn.BeginTransaction();
// Enlist the commands into this transaction.
cmdInsert.Transaction =
cmdRemove.Transaction =
// Execute the commands.
cmdInsert.ExecuteNonQuery();
cmdRemove.ExecuteNonQuery();
// Simulate error.
if (throwEx)
throw new ApplicationException("Sorry! Database error! Tx failed...");
// Commit it!
tx.Commit();
catch (Exception ex)
Console.WriteLine(ex.Message);
// Any error will rollback transaction.
tx.Rollback();
SqlCommand 对象的 ExecuteScalar 方法返回一个 Object 数据类型中第一行第一列的值。
view plaincopy to clipboardprint?01.SqlCommand cmd = new SqlCommand();
02.//存储过程应用
03.cmd.Connection =
mandType = CommandType.StoredP
mandText = "ListTable";
06.cmd.Parameters.AddWithValue("@fileName", "top 1 ID");
07.cmd.Parameters.AddWithValue("@tableName", "Class");
08.cmd.Parameters.AddWithValue("@Sort", null);
09.cmd.Parameters.AddWithValue("@whereStr", "ID & 4");
10.cmd.Parameters.AddWithValue("@groupStr", null);
11.//可以直接使用 object Obj = cmd.ExecuteScalar(); 然后判断 Obj == null 并进行下步操作
12.int ClassID = (int)cmd.ExecuteScalar();
13.Console.WriteLine(ClassID);
ExecuteNonQuery执行不返回结果集的查询，SqlCommand 对象的 ExecuteNonQuery 方法
view plaincopy to clipboardprint?01.string UPDATE_SP = "update class set clsname = @ClassName where id = @ClassID";
02.SqlCommand cmd = new SqlCommand(UPDATE_SP, conn);
03.SqlParameter[] p = new SqlParameter[2]{
new SqlParameter("@ClassName", DbType.String),
new SqlParameter("@ClassID", DbType.Int32)
07.p[0].Value = "Ado.Net";
08.p[1].Value = 7;
10.foreach (SqlParameter parm in p)
cmd.Parameters.Add(p);
13.int ExecuteNum = cmd.ExecuteNonQuery();
14.Console.WriteLine(ExecuteNum);
Parameters参数化查询 ，上述代码中已经实现参数化查询。Parameters 的作用可有效防止注入。利用AddWithValue 方法把参数传递给 cmd 对象。SqlParameter 对象详解
view plaincopy to clipboardprint?01.//SqlParameter 对象，利用SqlCommand AddWithValue方法创建 同等于下面
02.cmd.Parameters.AddWithValue("@ClassID", 3);
04.//SqlParameter 对象
05.SqlParameter p = new SqlParameter("@ClassID", DbType.Int16);
06.p.Value = 3;
07.cmd.Parameters.Add(p);
批量操作查询 ，用StatementCompleted事件获取批量操作查询时个语句返回的影响数
view plaincopy to clipboardprint?01.static void HandleStatementCompleted(object sender, StatementCompletedEventArgs e)
Console.WriteLine("查询影响：{0} row(s)", e.RecordCount);
05.//..........................................
06.string SQL = "update class set num = 1 where id = 3;" +
"update class set num = 2 where id = 4;" +
"update class set num = 3 where id = 5;" +
"update class set num = 4 where id = 6;";
10.SqlCommand cmd = new SqlCommand(SQL, conn);
11.cmd.StatementCompleted += new StatementCompletedEventHandler(HandleStatementCompleted);
12.int ExecuteNum = cmd.ExecuteNonQuery();
13.Console.WriteLine("批量查询共影响:{0} row(s)", ExecuteNum);
15.//输出来自查询的多个结果集
while (rdr.Read())
Console.WriteLine("{0} - {1}", rdr[0], rdr[1]);
20.} while (rdr.NextResult());
执行异步查询，SqlCommand公开了Begin, End方法，例如：BeginExecuteReader, EndExecuteReader方法 每个 Begin 方法返回一个实现 IAsyncResult 接口对象。这一返回对象是查询状态的一个句柄，IAsyncResult 接口是用于异步方法的.NET Framework 模式的一部分，其设计目的：帮助确定该操作是否已经完成如果需要等待该操作完成则中断当前线程；用作方法调用的句柄。 IAsyncResult 接口的IsCompleted 属性，以查看 BeginExecuteReader 调用是否已经完成。调用IAsyncResult.AsyncWaitHandle.WaitOne 使用IAsyncResult接口来等待该调用完成 。
view plaincopy to clipboardprint?01.using System.T //需引用此命名空间
03.conn.Open();
04.string SQL = "waitfor delay '00:00:10' ;" +
"select * from class";
06.SqlCommand cmd = new SqlCommand(SQL, conn);
07.IAsyncResult iar = cmd.BeginExecuteReader();
08.Console.WriteLine("异步查询是否已经获取记录?");
09.while (!iar.IsCompleted)
Console.WriteLine("否，还在等待.");
iar.AsyncWaitHandle.WaitOne(1000, true);
14.Console.WriteLine("是，已经获取数据.");
16.SqlDataReader rdr = cmd.EndExecuteReader(iar);
17.while (rdr.Read())
Console.WriteLine(rdr["ClsName"]);
21.rdr.Close();
阅读(...) 评论()当前位置：&>&&>&
c# 委托反射 DataTable转换为实体集的方法
发布时间：编辑：
为大家介绍在c#编程中，如何使用委托反射把datatable转换为实体集的方法，有需要的朋友可以参考下。
为大家介绍在c#编程中，如何使用委托反射把datatable转换为实体集的方法，有需要的朋友可以参考下。
1、类泛型的约束：
复制代码 代码示例:
public static class ToModel&T& where T : class, new()
2、定义委托：
复制代码 代码示例:
public delegate void SetString(string value);
3、创建委托方法：
复制代码 代码示例:
private static SetString CreateStringDelegate(T model, string propertyName)
MethodInfo mi = model.GetType().GetProperty(propertyName).GetSetMethod();
Type type = typeof(SetString);
return Delegate.CreateDelegate(type, model, mi) as SetS
4、借助反射和委托将DataTable转换为实体集：
复制代码 代码示例:
public static IList&T& GetDelegate_ToModelList(DataTable dt)
IList&T& list = new List&T&();
if (dt == null || dt.Rows.Count & 1)
SetString setDelegateS
foreach (DataRow dr in dt.Rows)
T model = new T();
foreach (DataColumn dc in dt.Columns)
setDelegateString = CreateStringDelegate(model, dc.ColumnName);
setDelegateString(dr[dc.ColumnName].ToString());
list.Add(model);
这样写会有问题，因为委托定义的参数时string类型的，因为我们实体中可能有int或者DateTime类型的，这时就需要用上泛型委托了。
5、定义委托：
public delegate void SetString&PT&(PT value)
创建委托方法（有点小问题，不知如何处理）：
复制代码 代码示例:
private static SetString CreateStringDelegate(T model, string propertyName)
MethodInfo mi = model.GetType().GetProperty(propertyName).GetSetMethod();
Type type = typeof(model).GetProperty(propertyName).PropertyT
return Delegate.CreateDelegate(type, model, mi) as SetString&type&;
6、利用反射和委托将DataTable转换为实体集：
复制代码 代码示例:
public static IList&T& GetDelegate_ToModelList(DataTable dt)
IList&T& list = new List&T&();
if (dt == null || dt.Rows.Count & 1)
foreach (DataRow dr in dt.Rows)
T model = new T();
foreach (DataColumn dc in dt.Columns)
SetString&typeof(T).GetProperty(dc.ColumnName).PropertyType& setDelegateString = CreateStringDelegate(model, dc.ColumnName);
setDelegateString(dr[dc.ColumnName].ToString());
list.Add(model);
7、泛型可以动态构建的，简略代码：
复制代码 代码示例:
using System.Collections.G
using System.L
using System.T
using System.D
using System.R
namespace RftToModel {
class Program {
static void Main(string[] args) {
var result = ToModel&TestModel&.GetDelegate_ToModelList(BuildSampleTable());
foreach (var item in result) {
Console.WriteLine(item);
Console.Read();
static DataTable BuildSampleTable() {
DataTable result = new DataTable();
result.Columns.Add(&ID&, typeof(int));
result.Columns.Add(&Name&, typeof(string));
result.Columns.Add(&IsDeleted&, typeof(bool));
result.Rows.Add(new object[] { 1, &M.K&, false });
result.Rows.Add(new object[] { 2, &B.G&, true });
public class TestModel {
public int ID { }
public string Name { }
public bool IsDeleted { }
public override string ToString() {
return string.Format(&ID:{0} Name:{1} IsDeleted:{2}&, ID, Name, IsDeleted);
public delegate void SetValue&T&(T value);
public static class ToModel&T& where T : class, new() {
private static Delegate CreateSetDelegate(T model, string propertyName) {
MethodInfo mi = model.GetType().GetProperty(propertyName).GetSetMethod();
//这里构造泛型委托类型
Type delType = typeof(SetValue&&).MakeGenericType(GetPropertyType(propertyName));
return Delegate.CreateDelegate(delType, model, mi);
private static Type GetPropertyType(string propertyName) {
return typeof(T).GetProperty(propertyName).PropertyT
public static IList&T& GetDelegate_ToModelList(DataTable dt) {
IList&T& list = new List&T&();
if (dt == null || dt.Rows.Count & 1)
Delegate setD
foreach (DataRow dr in dt.Rows) {
T model = new T();
foreach (DataColumn dc in dt.Columns) {
setDelegate = CreateSetDelegate(model, dc.ColumnName);
//这里改变类型
setDelegate.DynamicInvoke(Convert.ChangeType(dr[dc.ColumnName], GetPropertyType(dc.ColumnName)));
list.Add(model);
传进去SqlDataReader和DataTable都可以转换了，没想到DynamicInvoke这个方法。
与 c# 委托反射 DataTable转换为实体集的方法 有关的文章
本文标题：
本页链接：
12345678910
12345678910&&&&&&&&&&&&&&&&&&&&&&&&
&&&&&&&&&&&&&&
&&&&&&&&&&&&&&&&&&&&&&&&& 16:07:02
&&网站联系: qq: email:&