|
|
/////////////////////////////////////////////////////////////////
|
|
|
//
|
|
|
// (C) Copyright 2009, Kenneth, Inc.
|
|
|
// All rights reserved. Confidential. Except as pursuant
|
|
|
// to a written agreement with Kenneth, this software may
|
|
|
// not be used or distributed. This software may be covered
|
|
|
// by one or more patents.
|
|
|
//
|
|
|
// 本软件为Kenneth开发，版权所有，违者必究，320325198102218110
|
|
|
// Copyright(c) : Kenneth Weihua Yao
|
|
|
// Creater : yao_wh@hotmail.com;13776068374
|
|
|
//
|
|
|
/////////////////////////////////////////////////////////////////
|
|
|
|
|
|
using System;
|
|
|
using System.Collections.Generic;
|
|
|
using System.Text;
|
|
|
using System.Collections;
|
|
|
using System.Data;
|
|
|
using System.Data.SqlClient;
|
|
|
using System.Configuration;
|
|
|
using System.Data.SQLite;
|
|
|
|
|
|
namespace Kenneth.DotNet.EntityTool
|
|
|
{
|
|
|
/// <summary>
|
|
|
/// The SqlHelper class is intended to encapsulate high performance,
|
|
|
/// scalable best practices for common uses of SqlClient.
|
|
|
/// </summary>
|
|
|
public abstract class SqlHelper
|
|
|
{
|
|
|
/// <summary>
|
|
|
/// 设置command执行超时时间为30分钟
|
|
|
/// </summary>
|
|
|
private static int commandTimeout = 1800;
|
|
|
public static string connectionString = "";
|
|
|
|
|
|
// Hashtable to store cached parameters
|
|
|
private static Hashtable parmCache = Hashtable.Synchronized(new Hashtable());
|
|
|
|
|
|
/// <summary>
|
|
|
/// Execute a SQLiteCommand (that returns no resultset) against the database specified in the connection string
|
|
|
/// using the provided parameters.
|
|
|
/// </summary>
|
|
|
/// <remarks>
|
|
|
/// e.g.:
|
|
|
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
|
|
|
/// </remarks>
|
|
|
/// <param name="connectionString">a valid connection string for a SQLiteConnection</param>
|
|
|
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
|
|
|
/// <param name="commandText">the stored procedure name or T-SQL command</param>
|
|
|
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
|
|
|
/// <returns>an int representing the number of rows affected by the command</returns>
|
|
|
public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters)
|
|
|
{
|
|
|
SQLiteCommand cmd = new SQLiteCommand();
|
|
|
|
|
|
using (SQLiteConnection conn = new SQLiteConnection(connectionString))
|
|
|
{
|
|
|
try
|
|
|
{
|
|
|
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
|
|
|
int val = cmd.ExecuteNonQuery();
|
|
|
cmd.Parameters.Clear();
|
|
|
return val;
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
conn.Close();
|
|
|
throw new Exception(ex.Message);
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// Execute a SQLiteCommand (that returns no resultset) against an existing database connection
|
|
|
/// using the provided parameters.
|
|
|
/// </summary>
|
|
|
/// <remarks>
|
|
|
/// e.g.:
|
|
|
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
|
|
|
/// </remarks>
|
|
|
/// <param name="conn">an existing database connection</param>
|
|
|
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
|
|
|
/// <param name="commandText">the stored procedure name or T-SQL command</param>
|
|
|
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
|
|
|
/// <returns>an int representing the number of rows affected by the command</returns>
|
|
|
public static int ExecuteNonQuery(SQLiteConnection connection, CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters)
|
|
|
{
|
|
|
SQLiteCommand cmd = new SQLiteCommand();
|
|
|
|
|
|
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
|
|
|
int val = cmd.ExecuteNonQuery();
|
|
|
cmd.Parameters.Clear();
|
|
|
return val;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// Execute a SQLiteCommand (that returns no resultset) using an existing SQL Transaction
|
|
|
/// using the provided parameters.
|
|
|
/// </summary>
|
|
|
/// <remarks>
|
|
|
/// e.g.:
|
|
|
/// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
|
|
|
/// </remarks>
|
|
|
/// <param name="trans">an existing sql transaction</param>
|
|
|
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
|
|
|
/// <param name="commandText">the stored procedure name or T-SQL command</param>
|
|
|
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
|
|
|
/// <returns>an int representing the number of rows affected by the command</returns>
|
|
|
public static int ExecuteNonQuery(SQLiteTransaction trans, CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters)
|
|
|
{
|
|
|
SQLiteCommand cmd = new SQLiteCommand();
|
|
|
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
|
|
|
int val = cmd.ExecuteNonQuery();
|
|
|
cmd.Parameters.Clear();
|
|
|
return val;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 填充DataSet
|
|
|
/// </summary>
|
|
|
/// <param name="connectionString"></param>
|
|
|
/// <param name="cmdType"></param>
|
|
|
/// <param name="cmdText"></param>
|
|
|
/// <param name="commandParameters"></param>
|
|
|
/// <returns></returns>
|
|
|
public static DataSet Fill(SQLiteConnection connection, CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters)
|
|
|
{
|
|
|
SQLiteDataReader sdr = ExecuteReader(connection, cmdType, cmdText, commandParameters);
|
|
|
DataSet ds = DataReaderToDataSet(sdr);
|
|
|
sdr.Close();
|
|
|
return ds;
|
|
|
}
|
|
|
|
|
|
public static DataTable ExecuteDataTable(SQLiteConnection connection, CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters)
|
|
|
{
|
|
|
SQLiteDataReader sdr = ExecuteReader(connection, cmdType, cmdText, commandParameters);
|
|
|
DataSet ds = DataReaderToDataSet(sdr);
|
|
|
sdr.Close();
|
|
|
return ds.Tables[0];
|
|
|
}
|
|
|
public static DataTable ExecuteDataTable(SQLiteConnection connection, string cmdText)
|
|
|
{
|
|
|
SQLiteDataReader sdr = ExecuteReader(connection, CommandType.Text, cmdText);
|
|
|
DataSet ds = DataReaderToDataSet(sdr);
|
|
|
sdr.Close();
|
|
|
return ds.Tables[0];
|
|
|
}
|
|
|
public static DataTable ExecuteDataTable(string cmdText)
|
|
|
{
|
|
|
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
|
|
|
{
|
|
|
SQLiteDataReader sdr = ExecuteReader(connection, CommandType.Text, cmdText);
|
|
|
DataSet ds = DataReaderToDataSet(sdr);
|
|
|
sdr.Close();
|
|
|
return ds.Tables[0];
|
|
|
}
|
|
|
}
|
|
|
public static DataTable ExecuteDataTable(SQLiteConnection connection,string cmdText, params SQLiteParameter[] commandParameters)
|
|
|
{
|
|
|
SQLiteDataReader sdr = ExecuteReader(connection, CommandType.Text, cmdText, commandParameters);
|
|
|
DataSet ds = DataReaderToDataSet(sdr);
|
|
|
sdr.Close();
|
|
|
return ds.Tables[0];
|
|
|
}
|
|
|
public static DataTable ExecuteDataTable( string cmdText, params SQLiteParameter[] commandParameters)
|
|
|
{
|
|
|
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
|
|
|
{
|
|
|
SQLiteDataReader sdr = ExecuteReader(connection, CommandType.Text, cmdText, commandParameters);
|
|
|
DataSet ds = DataReaderToDataSet(sdr);
|
|
|
sdr.Close();
|
|
|
return ds.Tables[0];
|
|
|
}
|
|
|
}
|
|
|
public static DataTable ExecuteDataTable(SQLiteConnection connection, string cmdText, int startResord, int maxRecord)
|
|
|
{
|
|
|
DataSet ds = new DataSet();
|
|
|
SQLiteDataAdapter command = new SQLiteDataAdapter(cmdText, connection);
|
|
|
command.Fill(ds, startResord, maxRecord, "ds");
|
|
|
return ds.Tables[0];
|
|
|
}
|
|
|
public static DataTable ExecuteDataTable(string cmdText, int startResord, int maxRecord)
|
|
|
{
|
|
|
DataSet ds = new DataSet();
|
|
|
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
|
|
|
{
|
|
|
SQLiteDataAdapter command = new SQLiteDataAdapter(cmdText, connection);
|
|
|
command.Fill(ds, startResord, maxRecord, "ds");
|
|
|
|
|
|
return ds.Tables[0];
|
|
|
}
|
|
|
}
|
|
|
public static int ExecuteNonQuery(SQLiteConnection connection, string cmdText, params SQLiteParameter[] commandParameters)
|
|
|
{
|
|
|
SQLiteCommand cmd = new SQLiteCommand();
|
|
|
PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, commandParameters);
|
|
|
int val = cmd.ExecuteNonQuery();
|
|
|
cmd.Parameters.Clear();
|
|
|
return val;
|
|
|
}
|
|
|
public static int ExecuteNonQuery(string cmdText, params SQLiteParameter[] commandParameters)
|
|
|
{
|
|
|
SQLiteCommand cmd = new SQLiteCommand();
|
|
|
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
|
|
|
{
|
|
|
PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, commandParameters);
|
|
|
int val = cmd.ExecuteNonQuery();
|
|
|
cmd.Parameters.Clear();
|
|
|
return val;
|
|
|
}
|
|
|
}
|
|
|
public static object ExecuteScalar(SQLiteConnection connection, string cmdText)
|
|
|
{
|
|
|
SQLiteCommand cmd = new SQLiteCommand();
|
|
|
PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, null);
|
|
|
object val = cmd.ExecuteScalar();
|
|
|
cmd.Parameters.Clear();
|
|
|
return val;
|
|
|
}
|
|
|
public static object ExecuteScalar(string cmdText)
|
|
|
{
|
|
|
SQLiteCommand cmd = new SQLiteCommand();
|
|
|
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
|
|
|
{
|
|
|
PrepareCommand(cmd, connection, null, CommandType.Text, cmdText, null);
|
|
|
object val = cmd.ExecuteScalar();
|
|
|
cmd.Parameters.Clear();
|
|
|
return val;
|
|
|
}
|
|
|
}
|
|
|
#region FillDataset
|
|
|
/// <summary>
|
|
|
/// Execute a SQLiteCommand (that returns a resultset and takes no parameters) against the database specified in
|
|
|
/// the connection string.
|
|
|
/// </summary>
|
|
|
/// <remarks>
|
|
|
/// e.g.:
|
|
|
/// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
|
|
|
/// </remarks>
|
|
|
/// <param name="connectionString">A valid connection string for a SQLiteConnection</param>
|
|
|
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
|
|
|
/// <param name="commandText">The stored procedure name or T-SQL command</param>
|
|
|
/// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
|
|
|
/// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
|
|
|
/// by a user defined name (probably the actual table name)</param>
|
|
|
public static void FillDataset(CommandType commandType, string commandText, DataSet dataSet, string[] tableNames)
|
|
|
{
|
|
|
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
|
|
|
if (dataSet == null) throw new ArgumentNullException("dataSet");
|
|
|
|
|
|
// Create & open a SQLiteConnection, and dispose of it after we are done
|
|
|
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
|
|
|
{
|
|
|
connection.Open();
|
|
|
|
|
|
// Call the overload that takes a connection in place of the connection string
|
|
|
FillDataset(connection, commandType, commandText, dataSet, tableNames);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// Execute a SQLiteCommand (that returns a resultset) against the database specified in the connection string
|
|
|
/// using the provided parameters.
|
|
|
/// </summary>
|
|
|
/// <remarks>
|
|
|
/// e.g.:
|
|
|
/// FillDataset(connString, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SQLiteParameter("@prodid", 24));
|
|
|
/// </remarks>
|
|
|
/// <param name="connectionString">A valid connection string for a SQLiteConnection</param>
|
|
|
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
|
|
|
/// <param name="commandText">The stored procedure name or T-SQL command</param>
|
|
|
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
|
|
|
/// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
|
|
|
/// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
|
|
|
/// by a user defined name (probably the actual table name)
|
|
|
/// </param>
|
|
|
public static void FillDataset(CommandType commandType,
|
|
|
string commandText, DataSet dataSet, string[] tableNames,
|
|
|
params SQLiteParameter[] commandParameters)
|
|
|
{
|
|
|
if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString");
|
|
|
if (dataSet == null) throw new ArgumentNullException("dataSet");
|
|
|
// Create & open a SQLiteConnection, and dispose of it after we are done
|
|
|
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
|
|
|
{
|
|
|
connection.Open();
|
|
|
|
|
|
// Call the overload that takes a connection in place of the connection string
|
|
|
FillDataset(connection, commandType, commandText, dataSet, tableNames, commandParameters);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// Execute a SQLiteCommand (that returns a resultset and takes no parameters) against the provided SQLiteConnection.
|
|
|
/// </summary>
|
|
|
/// <remarks>
|
|
|
/// e.g.:
|
|
|
/// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
|
|
|
/// </remarks>
|
|
|
/// <param name="connection">A valid SQLiteConnection</param>
|
|
|
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
|
|
|
/// <param name="commandText">The stored procedure name or T-SQL command</param>
|
|
|
/// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
|
|
|
/// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
|
|
|
/// by a user defined name (probably the actual table name)
|
|
|
/// </param>
|
|
|
public static void FillDataset(SQLiteConnection connection, CommandType commandType,
|
|
|
string commandText, DataSet dataSet, string[] tableNames)
|
|
|
{
|
|
|
FillDataset(connection, commandType, commandText, dataSet, tableNames, null);
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// Execute a SQLiteCommand (that returns a resultset) against the specified SQLiteConnection
|
|
|
/// using the provided parameters.
|
|
|
/// </summary>
|
|
|
/// <remarks>
|
|
|
/// e.g.:
|
|
|
/// FillDataset(conn, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SQLiteParameter("@prodid", 24));
|
|
|
/// </remarks>
|
|
|
/// <param name="connection">A valid SQLiteConnection</param>
|
|
|
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
|
|
|
/// <param name="commandText">The stored procedure name or T-SQL command</param>
|
|
|
/// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
|
|
|
/// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
|
|
|
/// by a user defined name (probably the actual table name)
|
|
|
/// </param>
|
|
|
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
|
|
|
public static void FillDataset(SQLiteConnection connection, CommandType commandType,
|
|
|
string commandText, DataSet dataSet, string[] tableNames,
|
|
|
params SQLiteParameter[] commandParameters)
|
|
|
{
|
|
|
FillDataset(connection, null, commandType, commandText, dataSet, tableNames, commandParameters);
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// Execute a SQLiteCommand (that returns a resultset and takes no parameters) against the provided SQLiteTransaction.
|
|
|
/// </summary>
|
|
|
/// <remarks>
|
|
|
/// e.g.:
|
|
|
/// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"});
|
|
|
/// </remarks>
|
|
|
/// <param name="transaction">A valid SQLiteTransaction</param>
|
|
|
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
|
|
|
/// <param name="commandText">The stored procedure name or T-SQL command</param>
|
|
|
/// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
|
|
|
/// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
|
|
|
/// by a user defined name (probably the actual table name)
|
|
|
/// </param>
|
|
|
public static void FillDataset(SQLiteTransaction transaction, CommandType commandType,
|
|
|
string commandText,
|
|
|
DataSet dataSet, string[] tableNames)
|
|
|
{
|
|
|
FillDataset(transaction, commandType, commandText, dataSet, tableNames, null);
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// Execute a SQLiteCommand (that returns a resultset) against the specified SQLiteTransaction
|
|
|
/// using the provided parameters.
|
|
|
/// </summary>
|
|
|
/// <remarks>
|
|
|
/// e.g.:
|
|
|
/// FillDataset(trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SQLiteParameter("@prodid", 24));
|
|
|
/// </remarks>
|
|
|
/// <param name="transaction">A valid SQLiteTransaction</param>
|
|
|
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
|
|
|
/// <param name="commandText">The stored procedure name or T-SQL command</param>
|
|
|
/// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
|
|
|
/// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
|
|
|
/// by a user defined name (probably the actual table name)
|
|
|
/// </param>
|
|
|
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
|
|
|
public static void FillDataset(SQLiteTransaction transaction, CommandType commandType,
|
|
|
string commandText, DataSet dataSet, string[] tableNames,
|
|
|
params SQLiteParameter[] commandParameters)
|
|
|
{
|
|
|
FillDataset(transaction.Connection, transaction, commandType, commandText, dataSet, tableNames, commandParameters);
|
|
|
}
|
|
|
|
|
|
|
|
|
/// <summary>
|
|
|
/// Private helper method that execute a SQLiteCommand (that returns a resultset) against the specified SQLiteTransaction and SQLiteConnection
|
|
|
/// using the provided parameters.
|
|
|
/// </summary>
|
|
|
/// <remarks>
|
|
|
/// e.g.:
|
|
|
/// FillDataset(conn, trans, CommandType.StoredProcedure, "GetOrders", ds, new string[] {"orders"}, new SQLiteParameter("@prodid", 24));
|
|
|
/// </remarks>
|
|
|
/// <param name="connection">A valid SQLiteConnection</param>
|
|
|
/// <param name="transaction">A valid SQLiteTransaction</param>
|
|
|
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
|
|
|
/// <param name="commandText">The stored procedure name or T-SQL command</param>
|
|
|
/// <param name="dataSet">A dataset wich will contain the resultset generated by the command</param>
|
|
|
/// <param name="tableNames">This array will be used to create table mappings allowing the DataTables to be referenced
|
|
|
/// by a user defined name (probably the actual table name)
|
|
|
/// </param>
|
|
|
/// <param name="commandParameters">An array of SqlParamters used to execute the command</param>
|
|
|
private static void FillDataset(SQLiteConnection connection, SQLiteTransaction transaction, CommandType commandType,
|
|
|
string commandText, DataSet dataSet, string[] tableNames,
|
|
|
params SQLiteParameter[] commandParameters)
|
|
|
{
|
|
|
if (connection == null) throw new ArgumentNullException("connection");
|
|
|
if (dataSet == null) throw new ArgumentNullException("dataSet");
|
|
|
|
|
|
// Create a command and prepare it for execution
|
|
|
SQLiteCommand command = new SQLiteCommand();
|
|
|
bool mustCloseConnection = false;
|
|
|
PrepareCommand(command, connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection);
|
|
|
|
|
|
// Create the DataAdapter & DataSet
|
|
|
using (SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter(command))
|
|
|
{
|
|
|
|
|
|
// Add the table mappings specified by the user
|
|
|
if (tableNames != null && tableNames.Length > 0)
|
|
|
{
|
|
|
string tableName = "Table";
|
|
|
for (int index = 0; index < tableNames.Length; index++)
|
|
|
{
|
|
|
if (tableNames[index] == null || tableNames[index].Length == 0) throw new ArgumentException("The tableNames parameter must contain a list of tables, a value was provided as null or empty string.", "tableNames");
|
|
|
dataAdapter.TableMappings.Add(tableName, tableNames[index]);
|
|
|
tableName = "Table" + (index + 1).ToString();
|
|
|
}
|
|
|
}
|
|
|
|
|
|
// Fill the DataSet using default values for DataTable names, etc
|
|
|
dataAdapter.Fill(dataSet);
|
|
|
|
|
|
// Detach the SQLiteParameters from the command object, so they can be used again
|
|
|
command.Parameters.Clear();
|
|
|
}
|
|
|
|
|
|
if (mustCloseConnection)
|
|
|
connection.Close();
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
/// <summary>
|
|
|
///
|
|
|
/// </summary>
|
|
|
/// <param name="reader"></param>
|
|
|
/// <returns></returns>
|
|
|
private static DataSet DataReaderToDataSet(IDataReader reader)
|
|
|
{
|
|
|
DataTable table = new DataTable();
|
|
|
int fieldCount = reader.FieldCount;
|
|
|
for (int i = 0; i < fieldCount; i++)
|
|
|
{
|
|
|
table.Columns.Add(reader.GetName(i), reader.GetFieldType(i));
|
|
|
}
|
|
|
table.BeginLoadData();
|
|
|
object[] values = new object[fieldCount];
|
|
|
while (reader.Read())
|
|
|
{
|
|
|
reader.GetValues(values);
|
|
|
table.LoadDataRow(values, true);
|
|
|
}
|
|
|
table.EndLoadData();
|
|
|
DataSet ds = new DataSet();
|
|
|
ds.Tables.Add(table);
|
|
|
return ds;
|
|
|
}
|
|
|
|
|
|
|
|
|
/// <summary>
|
|
|
/// Execute a SQLiteCommand that returns a resultset against the database specified in the connection string
|
|
|
/// using the provided parameters.
|
|
|
/// </summary>
|
|
|
/// <remarks>
|
|
|
/// e.g.:
|
|
|
/// SQLiteDataReader r = ExecuteReader(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
|
|
|
/// </remarks>
|
|
|
/// <param name="connectionString">a valid connection string for a SQLiteConnection</param>
|
|
|
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
|
|
|
/// <param name="commandText">the stored procedure name or T-SQL command</param>
|
|
|
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
|
|
|
/// <returns>A SQLiteDataReader containing the results</returns>
|
|
|
public static SQLiteDataReader ExecuteReader(SQLiteConnection conn, CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters)
|
|
|
{
|
|
|
SQLiteCommand cmd = new SQLiteCommand();
|
|
|
// we use a try/catch here because if the method throws an exception we want to
|
|
|
// close the connection throw code, because no datareader will exist, hence the
|
|
|
// commandBehaviour.CloseConnection will not work
|
|
|
try
|
|
|
{
|
|
|
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
|
|
|
SQLiteDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
|
|
|
cmd.Parameters.Clear();
|
|
|
return rdr;
|
|
|
}
|
|
|
catch (Exception ex)
|
|
|
{
|
|
|
conn.Close();
|
|
|
throw new Exception(ex.Message);
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// Execute a SQLiteCommand that returns the first column of the first record against the database specified in the connection string
|
|
|
/// using the provided parameters.
|
|
|
/// </summary>
|
|
|
/// <remarks>
|
|
|
/// e.g.:
|
|
|
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
|
|
|
/// </remarks>
|
|
|
/// <param name="connectionString">a valid connection string for a SQLiteConnection</param>
|
|
|
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
|
|
|
/// <param name="commandText">the stored procedure name or T-SQL command</param>
|
|
|
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
|
|
|
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
|
|
|
public static object ExecuteScalar(CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters)
|
|
|
{
|
|
|
SQLiteCommand cmd = new SQLiteCommand();
|
|
|
|
|
|
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
|
|
|
{
|
|
|
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
|
|
|
object val = cmd.ExecuteScalar();
|
|
|
cmd.Parameters.Clear();
|
|
|
return val;
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// Execute a SQLiteCommand that returns the first column of the first record against an existing database connection
|
|
|
/// using the provided parameters.
|
|
|
/// </summary>
|
|
|
/// <remarks>
|
|
|
/// e.g.:
|
|
|
/// Object obj = ExecuteScalar(connString, CommandType.StoredProcedure, "PublishOrders", new SQLiteParameter("@prodid", 24));
|
|
|
/// </remarks>
|
|
|
/// <param name="conn">an existing database connection</param>
|
|
|
/// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
|
|
|
/// <param name="commandText">the stored procedure name or T-SQL command</param>
|
|
|
/// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
|
|
|
/// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
|
|
|
public static object ExecuteScalar(SQLiteConnection connection, CommandType cmdType, string cmdText, params SQLiteParameter[] commandParameters)
|
|
|
{
|
|
|
|
|
|
SQLiteCommand cmd = new SQLiteCommand();
|
|
|
|
|
|
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
|
|
|
object val = cmd.ExecuteScalar();
|
|
|
cmd.Parameters.Clear();
|
|
|
return val;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// add parameter array to the cache
|
|
|
/// </summary>
|
|
|
/// <param name="cacheKey">Key to the parameter cache</param>
|
|
|
/// <param name="cmdParms">an array of SqlParamters to be cached</param>
|
|
|
public static void CacheParameters(string cacheKey, params SQLiteParameter[] commandParameters)
|
|
|
{
|
|
|
parmCache[cacheKey] = commandParameters;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// Retrieve cached parameters
|
|
|
/// </summary>
|
|
|
/// <param name="cacheKey">key used to lookup parameters</param>
|
|
|
/// <returns>Cached SqlParamters array</returns>
|
|
|
public static SQLiteParameter[] GetCachedParameters(string cacheKey)
|
|
|
{
|
|
|
SQLiteParameter[] cachedParms = (SQLiteParameter[])parmCache[cacheKey];
|
|
|
|
|
|
if (cachedParms == null)
|
|
|
return null;
|
|
|
|
|
|
SQLiteParameter[] clonedParms = new SQLiteParameter[cachedParms.Length];
|
|
|
|
|
|
for (int i = 0, j = cachedParms.Length; i < j; i++)
|
|
|
clonedParms[i] = (SQLiteParameter)((ICloneable)cachedParms[i]).Clone();
|
|
|
|
|
|
return clonedParms;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// Prepare a command for execution
|
|
|
/// </summary>
|
|
|
/// <param name="cmd">SQLiteCommand object</param>
|
|
|
/// <param name="conn">SQLiteConnection object</param>
|
|
|
/// <param name="trans">SQLiteTransaction object</param>
|
|
|
/// <param name="cmdType">Cmd type e.g. stored procedure or text</param>
|
|
|
/// <param name="cmdText">Command text, e.g. Select * from Products</param>
|
|
|
/// <param name="cmdParms">SQLiteParameters to use in the command</param>
|
|
|
private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, CommandType cmdType, string cmdText, SQLiteParameter[] cmdParms)
|
|
|
{
|
|
|
if (conn.State != ConnectionState.Open)
|
|
|
conn.Open();
|
|
|
|
|
|
cmd.Connection = conn;
|
|
|
cmd.CommandTimeout = commandTimeout;
|
|
|
cmd.CommandText = cmdText;
|
|
|
|
|
|
if (trans != null)
|
|
|
cmd.Transaction = trans;
|
|
|
|
|
|
cmd.CommandType = cmdType;
|
|
|
|
|
|
if (cmdParms != null)
|
|
|
{
|
|
|
foreach (SQLiteParameter parm in cmdParms)
|
|
|
{
|
|
|
if (parm.Value == null)
|
|
|
{
|
|
|
parm.Value = DBNull.Value;
|
|
|
}
|
|
|
cmd.Parameters.Add(parm);
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// This method opens (if necessary) and assigns a connection, transaction, command type and parameters
|
|
|
/// to the provided command
|
|
|
/// </summary>
|
|
|
/// <param name="command">The SQLiteCommand to be prepared</param>
|
|
|
/// <param name="connection">A valid SQLiteConnection, on which to execute this command</param>
|
|
|
/// <param name="transaction">A valid SQLiteTransaction, or 'null'</param>
|
|
|
/// <param name="commandType">The CommandType (stored procedure, text, etc.)</param>
|
|
|
/// <param name="commandText">The stored procedure name or T-SQL command</param>
|
|
|
/// <param name="commandParameters">An array of SQLiteParameters to be associated with the command or 'null' if no parameters are required</param>
|
|
|
/// <param name="mustCloseConnection"><c>true</c> if the connection was opened by the method, otherwose is false.</param>
|
|
|
private static void PrepareCommand(SQLiteCommand command, SQLiteConnection connection, SQLiteTransaction transaction, CommandType commandType, string commandText, SQLiteParameter[] commandParameters, out bool mustCloseConnection)
|
|
|
{
|
|
|
if (command == null) throw new ArgumentNullException("command");
|
|
|
if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText");
|
|
|
command.CommandTimeout = commandTimeout;
|
|
|
|
|
|
|
|
|
// If the provided connection is not open, we will open it
|
|
|
if (connection.State != ConnectionState.Open)
|
|
|
{
|
|
|
mustCloseConnection = true;
|
|
|
connection.Open();
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
mustCloseConnection = false;
|
|
|
}
|
|
|
|
|
|
// Associate the connection with the command
|
|
|
command.Connection = connection;
|
|
|
|
|
|
// Set the command text (stored procedure name or SQL statement)
|
|
|
command.CommandText = commandText;
|
|
|
|
|
|
// If we were provided a transaction, assign it
|
|
|
if (transaction != null)
|
|
|
{
|
|
|
if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction");
|
|
|
command.Transaction = transaction;
|
|
|
}
|
|
|
|
|
|
// Set the command type
|
|
|
command.CommandType = commandType;
|
|
|
|
|
|
// Attach the command parameters if they are provided
|
|
|
if (commandParameters != null)
|
|
|
{
|
|
|
AttachParameters(command, commandParameters);
|
|
|
}
|
|
|
return;
|
|
|
}
|
|
|
|
|
|
private static void AttachParameters(SQLiteCommand command, SQLiteParameter[] commandParameters)
|
|
|
{
|
|
|
if (command == null) throw new ArgumentNullException("command");
|
|
|
if (commandParameters != null)
|
|
|
{
|
|
|
foreach (SQLiteParameter p in commandParameters)
|
|
|
{
|
|
|
if (p != null)
|
|
|
{
|
|
|
// Check for derived output value with no value assigned
|
|
|
if ((p.Direction == ParameterDirection.InputOutput ||
|
|
|
p.Direction == ParameterDirection.Input) &&
|
|
|
(p.Value == null))
|
|
|
{
|
|
|
p.Value = DBNull.Value;
|
|
|
}
|
|
|
command.Parameters.Add(p);
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, SQLiteTransaction trans, string cmdText, SQLiteParameter[] cmdParms)
|
|
|
{
|
|
|
if (conn.State != ConnectionState.Open)
|
|
|
conn.Open();
|
|
|
cmd.Connection = conn;
|
|
|
cmd.CommandText = cmdText;
|
|
|
cmd.CommandTimeout = commandTimeout;
|
|
|
if (trans != null)
|
|
|
cmd.Transaction = trans;
|
|
|
cmd.CommandType = CommandType.Text;//cmdType;
|
|
|
if (cmdParms != null)
|
|
|
{
|
|
|
foreach (SQLiteParameter parameter in cmdParms)
|
|
|
{
|
|
|
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
|
|
|
(parameter.Value == null))
|
|
|
{
|
|
|
parameter.Value = DBNull.Value;
|
|
|
}
|
|
|
cmd.Parameters.Add(parameter);
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// 仅支持SQL语句查询，返回DataSet(Add by Jackie.Shi 2008-08-20)
|
|
|
/// </summary>
|
|
|
/// <param name="connectionString">连接字符串</param>
|
|
|
/// <param name="SQLString">查询语句</param>
|
|
|
/// <returns>DataSet</returns>
|
|
|
public static DataSet Fill(string SQLString, params SQLiteParameter[] cmdParms)
|
|
|
{
|
|
|
|
|
|
using (SQLiteConnection connection = new SQLiteConnection(connectionString))
|
|
|
{
|
|
|
SQLiteCommand cmd = new SQLiteCommand();
|
|
|
PrepareCommand(cmd, connection, null, SQLString, cmdParms);
|
|
|
using (SQLiteDataAdapter da = new SQLiteDataAdapter(cmd))
|
|
|
{
|
|
|
DataSet ds = new DataSet();
|
|
|
try
|
|
|
{
|
|
|
da.Fill(ds, "ds");
|
|
|
cmd.Parameters.Clear();
|
|
|
}
|
|
|
catch (System.Data.SqlClient.SqlException ex)
|
|
|
{
|
|
|
throw new Exception(ex.Message);
|
|
|
}
|
|
|
finally
|
|
|
{
|
|
|
cmd.Dispose();
|
|
|
connection.Close();
|
|
|
}
|
|
|
return ds;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// DataSet转换为泛型集合
|
|
|
/// </summary>
|
|
|
/// <typeparam name="T"></typeparam>
|
|
|
/// <param name="p_DataSet"></param>
|
|
|
/// <param name="p_TableIndex"></param>
|
|
|
/// <returns></returns>
|
|
|
public static List<T> DataSetToList<T>(DataSet p_DataSet, int p_TableIndex)
|
|
|
{
|
|
|
IList<T> ilist = DataSetToIList<T>(p_DataSet, p_TableIndex);
|
|
|
List<T> result = new List<T>(ilist);
|
|
|
return result;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// DataSet转换为泛型集合
|
|
|
/// </summary>
|
|
|
/// <typeparam name="T"></typeparam>
|
|
|
/// <param name="p_DataSet">DataSet</param>
|
|
|
/// <param name="p_TableIndex">待转换数据表索引</param>
|
|
|
/// <returns></returns>
|
|
|
public static IList<T> DataSetToIList<T>(DataSet p_DataSet, int p_TableIndex)
|
|
|
{
|
|
|
// 返回值初始化
|
|
|
IList<T> result = new List<T>();
|
|
|
|
|
|
if (p_DataSet == null || p_DataSet.Tables.Count < 0)
|
|
|
return result;
|
|
|
|
|
|
if (p_TableIndex > p_DataSet.Tables.Count - 1)
|
|
|
return result;
|
|
|
|
|
|
if (p_TableIndex < 0)
|
|
|
p_TableIndex = 0;
|
|
|
|
|
|
DataTable p_Data = p_DataSet.Tables[p_TableIndex];
|
|
|
|
|
|
for (int j = 0; j < p_Data.Rows.Count; j++)
|
|
|
{
|
|
|
T _t = (T)Activator.CreateInstance(typeof(T));
|
|
|
System.Reflection.PropertyInfo[] propertys = _t.GetType().GetProperties();
|
|
|
foreach (System.Reflection.PropertyInfo pi in propertys)
|
|
|
{
|
|
|
for (int i = 0; i < p_Data.Columns.Count; i++)
|
|
|
{
|
|
|
// 属性与字段名称一致的进行赋值
|
|
|
string columnName = p_Data.Columns[i].ColumnName.ToUpper();
|
|
|
if (pi.Name.ToUpper().Equals(columnName) || pi.Name.ToUpper().Equals(columnName.Replace("_", string.Empty)))
|
|
|
{
|
|
|
// 数据库NULL值单独处理
|
|
|
if (p_Data.Rows[j][i] != DBNull.Value && p_Data.Rows[j][i] != null)
|
|
|
{
|
|
|
if (pi.PropertyType == typeof(Boolean))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToBoolean(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(Int16))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToInt16(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(Int32))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToInt32(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(Int64))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToInt64(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(Decimal))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToDecimal(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(Int32?))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToInt32(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(DateTime))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToDateTime(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(byte))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToByte(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(string))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToString(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
pi.SetValue(_t, p_Data.Rows[j][i], null);
|
|
|
}
|
|
|
}
|
|
|
else
|
|
|
pi.SetValue(_t, null, null);
|
|
|
break;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
result.Add(_t);
|
|
|
}
|
|
|
|
|
|
return result;
|
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
|
/// DataSet转换为泛型集合
|
|
|
/// </summary>
|
|
|
/// <typeparam name="T"></typeparam>
|
|
|
/// <param name="p_DataSet">DataSet</param>
|
|
|
/// <param name="p_TableName">待转换数据表名称</param>
|
|
|
/// <returns></returns>
|
|
|
public static IList<T> DataSetToIList<T>(DataSet p_DataSet, string p_TableName)
|
|
|
{
|
|
|
int _TableIndex = 0;
|
|
|
|
|
|
if (p_DataSet == null || p_DataSet.Tables.Count < 0)
|
|
|
return new List<T>();
|
|
|
|
|
|
if (string.IsNullOrEmpty(p_TableName))
|
|
|
return new List<T>();
|
|
|
|
|
|
for (int i = 0; i < p_DataSet.Tables.Count; i++)
|
|
|
{
|
|
|
// 获取Table名称在Tables集合中的索引值
|
|
|
if (p_DataSet.Tables[i].TableName.ToUpper().Equals(p_TableName.ToUpper()))
|
|
|
{
|
|
|
_TableIndex = i;
|
|
|
break;
|
|
|
}
|
|
|
}
|
|
|
return DataSetToIList<T>(p_DataSet, _TableIndex);
|
|
|
}
|
|
|
|
|
|
#region DataTable转换为泛型集合
|
|
|
public static T DataRow0ToEntity<T>(DataTable p_Data)
|
|
|
{
|
|
|
// 返回值初始化
|
|
|
if (p_Data.Rows.Count > 0)
|
|
|
{
|
|
|
T _t = (T)Activator.CreateInstance(typeof(T));
|
|
|
System.Reflection.PropertyInfo[] propertys = _t.GetType().GetProperties();
|
|
|
int j = 0;
|
|
|
|
|
|
foreach (System.Reflection.PropertyInfo pi in propertys)
|
|
|
{
|
|
|
for (int i = 0; i < p_Data.Columns.Count; i++)
|
|
|
{
|
|
|
// 属性与字段名称一致的进行赋值
|
|
|
string columnName = p_Data.Columns[i].ColumnName.ToUpper();
|
|
|
if (pi.Name.ToUpper().Equals(columnName) || pi.Name.ToUpper().Equals(columnName.Replace("_", string.Empty)))
|
|
|
{
|
|
|
// 数据库NULL值单独处理
|
|
|
if (p_Data.Rows[j][i] != DBNull.Value && p_Data.Rows[j][i] != null)
|
|
|
{
|
|
|
if (pi.PropertyType == typeof(Boolean))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToBoolean(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(Int16?))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToInt16(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(Int32?))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToInt32(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(Int64?))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToInt64(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(Decimal?))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToDecimal(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(Int32?))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToInt32(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(DateTime?))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToDateTime(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(byte?))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToByte(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(string))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToString(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
pi.SetValue(_t, p_Data.Rows[j][i], null);
|
|
|
}
|
|
|
}
|
|
|
else
|
|
|
pi.SetValue(_t, null, null);
|
|
|
break;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
return _t;
|
|
|
}
|
|
|
|
|
|
return default(T);
|
|
|
}
|
|
|
/// <summary>
|
|
|
/// DataTable转换为泛型集合
|
|
|
/// </summary>
|
|
|
/// <typeparam name="T"></typeparam>
|
|
|
/// <param name="p_DataSet">DataSet</param>
|
|
|
/// <param name="p_TableIndex">待转换数据表索引</param>
|
|
|
/// <returns></returns>
|
|
|
public static IList<T> DataTableToIList<T>(DataTable p_Data)
|
|
|
{
|
|
|
// 返回值初始化
|
|
|
IList<T> result = new List<T>();
|
|
|
for (int j = 0; j < p_Data.Rows.Count; j++)
|
|
|
{
|
|
|
T _t = (T)Activator.CreateInstance(typeof(T));
|
|
|
System.Reflection.PropertyInfo[] propertys = _t.GetType().GetProperties();
|
|
|
foreach (System.Reflection.PropertyInfo pi in propertys)
|
|
|
{
|
|
|
for (int i = 0; i < p_Data.Columns.Count; i++)
|
|
|
{
|
|
|
// 属性与字段名称一致的进行赋值
|
|
|
string columnName = p_Data.Columns[i].ColumnName.ToUpper();
|
|
|
if (pi.Name.ToUpper().Equals(columnName) || pi.Name.ToUpper().Equals(columnName.Replace("_", string.Empty)))
|
|
|
{
|
|
|
// 数据库NULL值单独处理
|
|
|
if (p_Data.Rows[j][i] != DBNull.Value && p_Data.Rows[j][i] != null)
|
|
|
{
|
|
|
if (pi.PropertyType == typeof(Boolean))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToBoolean(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(Int16?))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToInt16(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(Int32?))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToInt32(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(Int64?))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToInt64(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(Decimal?))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToDecimal(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(Int32?))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToInt32(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(DateTime?))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToDateTime(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(byte?))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToByte(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else if (pi.PropertyType == typeof(string))
|
|
|
{
|
|
|
pi.SetValue(_t, Convert.ToString(p_Data.Rows[j][i]), null);
|
|
|
}
|
|
|
else
|
|
|
{
|
|
|
pi.SetValue(_t, p_Data.Rows[j][i], null);
|
|
|
}
|
|
|
}
|
|
|
else
|
|
|
pi.SetValue(_t, null, null);
|
|
|
break;
|
|
|
}
|
|
|
}
|
|
|
}
|
|
|
result.Add(_t);
|
|
|
}
|
|
|
|
|
|
return result;
|
|
|
}
|
|
|
#endregion
|
|
|
|
|
|
#region PageList Without Proc
|
|
|
/// <summary>
|
|
|
/// 获取分页数据 在不用存储过程情况下
|
|
|
/// </summary>
|
|
|
/// <param name="records">总记录条数</param>
|
|
|
/// <param name="fields">选择的列逗号隔开,支持top num</param>
|
|
|
/// <param name="tableName">表名字</param>
|
|
|
/// <param name="whereStr">条件字符 必须前加 and</param>
|
|
|
/// <param name="orderby">排序 例如 ID</param>
|
|
|
/// <param name="pageIdex">当前索引页</param>
|
|
|
/// <param name="pageSize">每页记录数</param>
|
|
|
/// <returns></returns>
|
|
|
public static DataTable GetPager(out int records, string fields, string tableName, string whereStr, string orderby, string sortorder, int pageIdex, int pageSize)
|
|
|
{
|
|
|
int rows = 0;
|
|
|
string sqlStr = string.Format("select {0} from {1} where 1=1 {2}", fields, tableName, whereStr);
|
|
|
if (!string.IsNullOrEmpty(orderby)) { sqlStr += string.Format(" Order by {0} {1}", orderby, sortorder); }
|
|
|
|
|
|
string sqlCount = string.Format("select count(*) from {0} where 1=1 {1} ", tableName, whereStr);
|
|
|
//获取行数
|
|
|
object obj = ExecuteScalar(sqlCount);
|
|
|
if (obj != null)
|
|
|
{
|
|
|
rows = Convert.ToInt32(obj);
|
|
|
}
|
|
|
DataTable dt = ExecuteDataTable(sqlStr, (pageIdex - 1) * pageSize, pageSize);
|
|
|
records = rows;
|
|
|
return dt;
|
|
|
}
|
|
|
#endregion
|
|
|
}
|
|
|
}
|
