autobase.cs

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

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Xml;
using System.Data.SqlClient;
using System.Threading;

using Xinge.AspMng.DataAccess;
using Microsoft.ApplicationBlocks.Data;
using Xinge.AspMng.SystemFramework;
using Xinge.AspMng.Common.BizEntity;
using Xinge.AspMng.Common;
using System.Collections;



namespace Xinge.AspMng.AspAutoSystem
{
    public class AutoBase
    {
        /// <summary>
        /// 填充表格
        /// </summary>
        /// <param name="sCmd"></param>
        /// <param name="dt"></param>
        /// <param name="ParnName"></param>
        /// <param name="value"></param>
        protected virtual void FillTable(string sCmd, DataTable dt, string ParnName, object value)
        {
            SqlParameter[] objList = new SqlParameter[] { new SqlParameter(ParnName, value) };
            FillTable(sCmd, dt, objList);
        }
        /// <summary>
        /// 填充表格
        /// </summary>
        /// <param name="sCmd"></param>
        /// <param name="dt"></param>
        /// <param name="objList"></param>
        protected virtual void FillTable(string sCmd, DataTable dt, SqlParameter[] objList)
        {
            using (SqlConnection conn = new SqlConnection(AspMngConfiguration.DBConString))
            {
                conn.Open();
                SqlHelper.FillTable(conn, CommandType.Text, sCmd, dt, objList);
                conn.Close();
            }
        }
        /// <summary>
        /// 填充表格
        /// </summary>
        /// <param name="sCmd"></param>
        /// <param name="dt"></param>
        /// <param name="objList">SqlParameter </param>
        protected virtual void FillTable(string sCmd, DataTable dt, ArrayList objList)
        {
            SqlParameter[] mm = CreateSqlParameterListByParamArray(objList);
            FillTable(sCmd, dt, mm);            
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sCmd"></param>
        /// <param name="objList"></param>
        /// <returns></returns>
        protected virtual int ExecuteNoQuery(string sCmd, string ParnName, object value)
        {
            SqlParameter[] objList = new SqlParameter[] { new SqlParameter(ParnName, value) };
            return ExecuteNoQuery(sCmd, objList);
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sCmd"></param>
        /// <param name="objList"></param>
        /// <returns></returns>
        protected virtual int ExecuteNoQuery(string sCmd, SqlParameter[] objList)
        {
            int iret = 0;
            using (SqlConnection conn = new SqlConnection(AspMngConfiguration.DBConString))
            {
                conn.Open();
                iret = SqlHelper.ExecuteNonQuery(conn, CommandType.Text, sCmd, objList);
                conn.Close();
            }
            return iret;
        }
        protected virtual void AppendCondition(ref string cmd, ref ArrayList sqlparamlist, string paramName, object paramValue, string scondition)
        {
            //判断条件
            if (paramValue.GetType() == typeof(string))
            {
                if (string.IsNullOrEmpty((string)paramValue)) return;
                paramValue = ((string)paramValue).Trim();
            }
            if (paramValue.GetType() == typeof(int))
            {
                if (-1 == (int)paramValue) return;
            }
            if (paramValue.GetType() == typeof(Guid))
            {
                if ((Guid)paramValue == Guid.Empty) return;
            }
            if (paramValue.GetType() == typeof(System.Windows.Forms.CheckState))
            {
                if ((System.Windows.Forms.CheckState)paramValue == System.Windows.Forms.CheckState.Indeterminate) return;
            }

            if (paramValue.GetType() == typeof(DateTime ))
            {
                if ((DateTime)paramValue == DateTime.MaxValue || (DateTime)paramValue == DateTime.MinValue ) return;
            }

            //
            if (sqlparamlist.Count == 0)
            {
                cmd = cmd + " where " + scondition;
            }
            else
            {
                cmd = cmd + " and " + scondition;
            }
            sqlparamlist.Add(new SqlParameter(paramName, paramValue));
        }
        /// <summary>
        /// ？？根据SqlParameter ArrayList 创建数组？
        /// </summary>
        /// <param name="array"></param>
        /// <returns></returns>
        protected virtual SqlParameter[] CreateSqlParameterListByParamArray(ArrayList array)
        {
            SqlParameter []obl = new SqlParameter[array.Count];
            for (int i = 0; i < array.Count; i++)
            {
                obl[i] = new SqlParameter();
                obl[i] = (SqlParameter)array[i];
            }
            return obl;
        }

        /// <summary>
        /// 生成操作数据库DA
        /// </summary>
        /// <param name="cmdText">select * from [tabname??]</param>
        /// <returns></returns>
        static protected  SqlDataAdapter CreateDA(string cmdText )
        {
            SqlConnection conn = new SqlConnection(AspMngConfiguration.DBConString);
            SqlDataAdapter sd = new SqlDataAdapter(cmdText , conn);
            SqlCommandBuilder sb = new SqlCommandBuilder(sd);
            sd.InsertCommand = sb.GetInsertCommand();
            sd.UpdateCommand = sb.GetUpdateCommand();
            sd.DeleteCommand = sb.GetDeleteCommand();
            return sd;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmdText">select * from [tabname??]</param>
        /// <param name="dt"></param>
        /// <param name="rowType"></param>
        /// <returns></returns>
        static protected  bool UpdateCurrentDataTable(string cmdText,DataTable dt, DataRowState rowType)
        {
            bool ret = false;
            SqlDataAdapter sd = CreateDA(cmdText );
            for (int j = dt.Rows.Count - 1; j >= 0; j--)
            {
                DataRow dr = dt.Rows[j];
                if (dr.RowState != DataRowState.Unchanged && (dr.RowState & rowType) == dr.RowState)
                {
                    ret = sd.Update(new DataRow[] { dr }) > 0;
                }
            }
            return ret;
        }
    }
}