datasethelper.cs

解决FTP、拨号、文件读取、Excel等等历程。很哭的彩旦、压缩等等等。

/* ***************************************************************
 * Version		: 2.0
 * Copyright	: XingE Software Co.Ltd,ShangHai,China
 * Created on	: 2006-01-08
 * Author		: zhaopeng
 * Email		: [e-mail]
 * Comment		: 基于数据库表访问助手类
 * ***************************************************************/

using System;
using System.Diagnostics;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using Microsoft.ApplicationBlocks.Data;
using Microsoft.Win32;
using Xinge.AspMng.Common;
using Xinge.AspMng.SystemFramework;

namespace Xinge.AspMng.DataAccess
{
    /// <summary>
    /// 帮助以表结构建立的DataSet存取数据库的助手类
    /// </summary>
    public static partial class DataSetHelper
    {
        /// <summary>
        /// 把DataRow行的每一列转化成参数
        /// </summary>
        /// <param name="siteSetting"></param>
        /// <returns></returns>
        public static SqlParameter[] ParpareSqlParameter(DataRow siteSetting)
        {
            DataColumnCollection columns = siteSetting.Table.Columns;

            SqlParameter[] sps = new SqlParameter[columns.Count];

            for (int index = 0; index < columns.Count; index++)
            {
                sps[index] = new SqlParameter("@" + columns[index].ColumnName, columns[index].DataType);

                if (siteSetting.IsNull(index))
                    sps[index].Value = DBNull.Value;
                else
                    sps[index].Value = siteSetting[index];

                if (columns[index].DataType.FullName == typeof(Decimal).FullName)
                    sps[index].SqlDbType = SqlDbType.Money;

            }

            return sps;
        }


        /// <summary>
        /// 通过ID字段获得指定表内容
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="theDataTable"></param>
        /// <param name="key"></param>
        public static void RetrieveDataTableById(string tableName, DataTable theDataTable, object[] key)
        {
            DataColumn identityCol = FindIdentityColumn(theDataTable.Columns);
            if (identityCol == null) throw new ArgumentException("没有找到唯一标示字段");
            bool needQuotation = false;
            if (identityCol.DataType == typeof(string) || identityCol.DataType == typeof(Guid)) needQuotation = true;
            RetrieveDataTableById(tableName, theDataTable, needQuotation, identityCol.ColumnName, key);
        }


        /// <summary>
        /// 通过自增长字段获得指定表内容
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="theDataTable"></param>
        /// <param name="key"></param>
        public static void RetrieveDataTableById(string tableName, DataTable theDataTable, params int[] key)
        {
            DataColumn identityCol = FindIdentityColumn(theDataTable.Columns);
            RetrieveDataTableById(tableName, theDataTable, identityCol.ColumnName, key);
        }

        /// <summary>
        /// 获得指定表内容
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="theDataTable"></param>
        /// <param name="keyFieldName"></param>
        /// <param name="key"></param>
        public static void RetrieveDataTableById(string tableName, DataTable theDataTable, string keyFieldName, params int[] key)
        {
            string keyStr = string.Empty;
            foreach (int var in key)
            {
                keyStr = string.IsNullOrEmpty(keyStr) ? var.ToString() : keyStr + ", " + var.ToString();
            }

            string sql = "select * from [" + tableName + "] where [" + keyFieldName + "] in (" + keyStr + ")";
            System.Diagnostics.Debug.WriteLine(sql);

            FillTable(sql, theDataTable);
            //DA.FillDataTable(sql, theDataTable);
        }


        /// <summary>
        /// 获得指定表内容
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="theDataTable"></param>
        /// <param name="needQuotation">是否需要单引号</param>
        /// <param name="keyFieldName">主键字段</param>
        /// <param name="key">值</param>
        public static void RetrieveDataTableById(string tableName, DataTable theDataTable, bool needQuotation, string keyFieldName, object[] key)
        {
            DataSetHelper.RetrieveDataTableById(tableName, theDataTable, needQuotation, keyFieldName, key, string.Empty);
        }
        /// <param name="orderBy">排序sql语句,例：" order by InsuranceBeginDate"</param>
        public static void RetrieveDataTableById(string tableName, DataTable theDataTable, bool needQuotation, string keyFieldName, object[] key,string orderBy)
        {
            string keyStr = string.Empty;

            foreach (object var in key)
            {
                if (needQuotation)
                    keyStr = string.IsNullOrEmpty(keyStr) ? "'" + var.ToString() + "'" : "'" + keyStr + "'" + ", " + var.ToString();
                else
                    keyStr = string.IsNullOrEmpty(keyStr) ? var.ToString() : keyStr + ", " + var.ToString();
            }

            StringBuilder sql = new StringBuilder();

            sql.Append(" select ");
            if (theDataTable.Columns.Count <= 0)
                sql.Append(" * ");
            else
            {
                foreach (DataColumn col in theDataTable.Columns)
                {
                    sql.Append("[" + col.ColumnName + "]");
                    if (col.Ordinal < (theDataTable.Columns.Count - 1)) sql.Append(",");
                }
            }

            sql.Append(" from [" + tableName + "] where [" + keyFieldName + "] in (" + keyStr + ") ");
            if (!string.IsNullOrEmpty(orderBy))
            {
                sql.Append(orderBy);
            }
            System.Diagnostics.Debug.WriteLine(sql);
            SqlHelper.FillTable(AspMngConfiguration.DBConString, CommandType.Text, sql.ToString(), theDataTable);
        }

        /// <summary>
        /// 分页返回指定查询语句的内容
        /// </summary>
        /// <param name="table">数据表对象</param>
        /// <param name="pager">分页信息</param>
        /// <param name="sqlSentence">返回数据的查询语句</param>
        /// <param name="sqlCountSentence">返回与查询语句相对应的数据行数</param>
        /// <param name="sqlParameters">查询语句需要的参数，如果没有则设置null</param>
        public static void RetrieveByPage(DataTable table, Pager pager, string sqlSentence, string sqlCountSentence, params SqlParameter[] sqlParameters)
        {
            using (SqlConnection conn = new SqlConnection(AspMngConfiguration.DBConString))
            {
                pager.RowCount = (int)SqlHelper.ExecuteScalar(conn, CommandType.Text, sqlCountSentence, sqlParameters);

                SqlCommand cmd = new SqlCommand(sqlSentence, conn);
                if (sqlParameters != null) cmd.Parameters.AddRange(sqlParameters);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                adp.Fill(pager.FromRow, pager.ToRow, table);
            }
        }
        public static void RetrieveAll(DataTable table, string TableName)
        {
            using (SqlConnection conn = new SqlConnection(AspMngConfiguration.DBConString))
            {
                SqlCommand cmd = new SqlCommand("select * from " + TableName, conn);
                SqlDataAdapter adp = new SqlDataAdapter(cmd);
                adp.Fill(table);
            }

        }


        /// <summary>
        /// 保存新建数据行,只适合单表保存
        /// </summary>
        /// <param name="tableName">要保存到的表名</param>
        /// <param name="row"></param>
        public static void SaveDataRowForNew(string tableName, DataRow row)
        {
            if (row.RowState == DataRowState.Detached) row.Table.Rows.Add(row);
            if (row.RowState != DataRowState.Added)
            {
                Debug.WriteLine("该行不是新添加的，不执行操作");
                return;
            }

            string fieldsName = string.Empty;
            string valueName = string.Empty;
            DataColumn identityColumn = null;

            foreach (DataColumn col in row.Table.Columns)
            {
                if (col.AutoIncrement == true && col.Unique == true)
                {
                    if (identityColumn != null) throw new ApplicationException("不支持多个唯一标识的自增长字段");
                    identityColumn = col;
                    continue;
                }

                fieldsName = string.IsNullOrEmpty(fieldsName) ? " [" + col.ColumnName + "] " : fieldsName + ", [" + col.ColumnName + "]";
                valueName = string.IsNullOrEmpty(valueName) ? "@" + col.ColumnName : valueName + ",@" + col.ColumnName;
            }

            string sql = " INSERT INTO [{0}] ({1}) VALUES ({2})";
            if (identityColumn != null) sql += ";  SELECT SCOPE_IDENTITY()";
            sql = string.Format(sql, tableName, fieldsName, valueName);
            System.Diagnostics.Debug.WriteLine(sql);

            SqlParameter[] sps = ParpareSqlParameter(row);
            object result = SqlHelper.ExecuteScalar(AspMngConfiguration.DBConString, CommandType.Text, sql, sps);

            if (result != null)
            {
                //修改重复的键值
                UpdateTableRowIdIfSameValue(row.Table, result);

                bool r = identityColumn.ReadOnly;
                identityColumn.ReadOnly = false;
                row[identityColumn] = result;
                identityColumn.ReadOnly = r;
            }
            row.AcceptChanges();
        }


        /// <summary>
        /// 将行更新到数据库后,返回真实的ROWID时如果行所在的TABLE已有此ID,则把那相同的ID改掉.
        /// </summary>
        /// <param name="oT">行所在的表</param>
        /// <param name="iNewId">新的自增长字段ID的值</param>
        static void UpdateTableRowIdIfSameValue(DataTable oT, object oNewId)
        {
            if (oT == null)
                return;

            //如果只有一行不操作
            if (oT.Rows.Count < 2)
                return;

            //检查是否有自增长字段
            bool bHasAutoIncrement = false;
            string sAutoIncrementName = string.Empty;

            foreach (DataColumn col in oT.Columns)
            {
                if (col.AutoIncrement == true && col.Unique == true)
                {
                    bHasAutoIncrement = true;
                    sAutoIncrementName = col.ColumnName;
                    break;
                }
            }

            //如果没有自增长的字段则退出
            if (!bHasAutoIncrement)
                return;

            //再次检查是不是整形
            int iNewId = 0;
            try
            {
                iNewId = Convert.ToInt32(oNewId);
            }
            catch
            {
                //不是自增长,整形
                return;
            }


            //修改重复值
            foreach (DataRow oRow in oT.Rows)
            {
                if (((int)oRow[sAutoIncrementName]) == iNewId)
                {
                    DataColumn ocol = oT.Columns[sAutoIncrementName];
                    bool r = ocol.ReadOnly;
                    ocol.ReadOnly = false;
                    oRow[sAutoIncrementName] = (-1 * iNewId);
                    ocol.ReadOnly = r;
                    break;
                }
            }

        }

        public static string SaveDataRowForInsert(string tableName, DataRow row)
        {
            if (row.RowState == DataRowState.Detached) row.Table.Rows.Add(row);
            if (row.RowState != DataRowState.Added)
            {
                Debug.WriteLine("该行不是新添加的，不执行操作");
                return string.Empty;
            }

            string fieldsName = string.Empty;
            string valueName = string.Empty;
            DataColumn identityColumn = null;

            foreach (DataColumn col in row.Table.Columns)
            {
                if (col.AutoIncrement == true && col.Unique == true)
                {
                    if (identityColumn != null) throw new ApplicationException("不支持多个唯一标识的自增长字段");
                    identityColumn = col;
                    continue;
                }

                fieldsName = string.IsNullOrEmpty(fieldsName) ? " [" + col.ColumnName + "] " : fieldsName + ", [" + col.ColumnName + "]";
                valueName = string.IsNullOrEmpty(valueName) ? "@" + col.ColumnName : valueName + ",@" + col.ColumnName;
            }

            string sql = " INSERT INTO [{0}] ({1}) VALUES ({2})";
            if (identityColumn != null) sql += ";  SELECT SCOPE_IDENTITY()";
            sql = string.Format(sql, tableName, fieldsName, valueName);
            System.Diagnostics.Debug.WriteLine(sql);

            SqlParameter[] sps = ParpareSqlParameter(row);
            object result = SqlHelper.ExecuteScalar(AspMngConfiguration.DBConString, CommandType.Text, sql, sps);

            if (result != null)
            {
                //修改重复的键值
                UpdateTableRowIdIfSameValue(row.Table, result);

                bool r = identityColumn.ReadOnly;
                identityColumn.ReadOnly = false;
                row[identityColumn] = result;
                identityColumn.ReadOnly = r;
            }
            row.AcceptChanges();
            return result.ToString();
        }
        /// <summary>
        /// 保存新建数据行,只适合单表保存
        /// </summary>
        /// <param name="tableName">要保存到的表名</param>
        /// <param name="table"></param>
        public static void SaveDataRowForNew(string tableName, DataTable table)
        {
            foreach (DataRow row in table.Rows)
            {
                if (row.RowState != DataRowState.Added) continue;
                SaveDataRowForNew(tableName, row);
            }
        }


        /// <summary>
        /// 更新到指定的表。
        /// 说明：此方法根据DataRowSate的状态完成新增、更新、和删除。
        /// 但是新增不能返回自增长字段的值，可以使用SaveDataRowForNew完成。
        /// </summary>
        /// <remarks>只适合单表更新</remarks>
        /// <param name="table">要保存到的表名</param>
        /// <param name="companyRow"></param>