datasethelper.cs

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

        public static void SaveDataRowForUpdate(string tableName, DataTable theDataTable)
        {
            DataTable dt = theDataTable.GetChanges(DataRowState.Added);
            if (dt != null)
                Debug.Fail("发现有新添加的行，本方法可完成新加操作，但不能返回自增长字段的值，使用SaveDataRowForNew可达到。");

            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 + "]");

            SqlDataAdapter adp = new SqlDataAdapter(sql.ToString(), AspMngConfiguration.DBConString);
            SqlCommandBuilder cb = new SqlCommandBuilder(adp);
            cb.ConflictOption = ConflictOption.OverwriteChanges;
            adp.Update(theDataTable);
        }



        /// <summary>
        /// 保存更新到指定的表
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="theDataRow"></param>
        public static void SaveDataRowForUpdate(string tableName, DataRow theDataRow)
        {
            SaveDataRowForUpdate(tableName, theDataRow.Table);
        }



        /// <summary>
        /// 删除指定表中的指定行
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="keyFields">主键名</param>
        /// <param name="keyValues">主键值</param>
        public static void DeleteTableRowData<T>(string tableName, string keyField, params T[] keyValues)
        {

            string sql = " Delete From [" + tableName + "] Where  [" + keyField + "] IN ";
            string keys = string.Empty;

            for (int i = 0; i < keyValues.Length; i++)
            //foreach ( object var in keyValues )
            {
                object var = keyValues[i];
                if (string.IsNullOrEmpty(keys))
                {
                    keys = var is string ? "'" + (string)var + "'" : Convert.ToString(var);
                }
                else
                {
                    keys = keys + "," + (var is string ? "'" + (string)var + "'" : Convert.ToString(var));
                }
            }

            sql = sql + "(" + keys + ")";
            System.Diagnostics.Debug.WriteLine(sql);
            SqlHelper.ExecuteNonQuery(AspMngConfiguration.DBConString, CommandType.Text, sql);
        }

        /// <summary>
        /// 删除指定表中的指定行，通过找到自增长列
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="keyField"></param>
        /// <param name="keyValues"></param>
        public static void DeleteTableRowData(string tableName, params int[] keyValues)
        {
            SqlDataAdapter adp = new SqlDataAdapter("select * from [" + tableName + "]", AspMngConfiguration.DBConString);
            DataTable dt = new DataTable();
            adp.FillSchema(dt, SchemaType.Mapped);
            DataColumn identityColumn = FindIdentityColumn(dt.Columns);
            DeleteTableRowData(tableName, identityColumn.ColumnName, keyValues);
        }


        /// <summary>
        /// 删除指定表中的指定行
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="needQuotation">主键字段是否需要引号</param>
        /// <param name="keyFields">主键名</param>
        /// <param name="keyValues">主键值</param>
        public static void DeleteTableRowData(string tableName, bool needQuotation, string keyField, object[] keyValues)
        {

            string sql = " Delete From [" + tableName + "] Where  [" + keyField + "] IN ";
            string keyStr = string.Empty;

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

            sql = sql + "(" + keyStr + ")";
            System.Diagnostics.Debug.WriteLine(sql);
            SqlHelper.ExecuteNonQuery(AspMngConfiguration.DBConString, CommandType.Text, sql);
        }


        /// <summary>
        /// 删除指定表中的指定行，通过找到自增长列
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="keyField"></param>
        /// <param name="keyValues"></param>
        public static void DeleteTableRowData(string tableName, object[] keyValues)
        {
            SqlDataAdapter adp = new SqlDataAdapter("select * from [" + tableName + "]", AspMngConfiguration.DBConString);
            DataTable dt = new DataTable();
            adp.FillSchema(dt, SchemaType.Mapped);
            DataColumn identityColumn = FindIdentityColumn(dt.Columns);
            if (identityColumn == null) throw new ApplicationException("没有找到唯一标识字段");
            bool needQuotation = false;
            if (identityColumn.DataType == typeof(string) || identityColumn.DataType == typeof(Guid)) needQuotation = true;
            DeleteTableRowData(tableName, needQuotation, identityColumn.ColumnName, keyValues);
        }



        /// <summary>
        /// 找到唯一的自增长列，如果没找到或者找到多个，则抱错
        /// </summary>
        /// <param name="columns"></param>
        /// <returns></returns>
        public static DataColumn FindIdentityColumn(DataColumnCollection columns)
        {
            DataColumn identityColumn = null;

            foreach (DataColumn col in columns)
            {
                //if ( col.Unique == true && col.AutoIncrement == true )
                if (col.Unique == true)
                {
                    if (identityColumn != null)
                        throw new NotSupportedException("发现了两个唯一标示列，此方法不能支持！请挥刀自攻！");

                    identityColumn = col;
                    continue;
                }
            }
            //if ( identityColumn == null ) throw new NotSupportedException("未发现自增长字段，此方法不能支持");

            return identityColumn;
        }


        /// <summary>
        /// 检查指定字段指定值，所具有的记录数。如果要检查表的总行数，则字段传入空。
        /// </summary>
        /// <param name="tableName">要检查的表名</param>
        /// <param name="fieldName">字段名</param>
        /// <param name="keyValue">值</param>
        /// <returns></returns>
        public static int GetRowCount(string tableName, string fieldName, object keyValue)
        {
            string sql = string.Empty;
            SqlParameter sp = null;

            if (string.IsNullOrEmpty(fieldName))
                sql = "select count(*) from [" + tableName + "] ";
            else
            {
                sql = "select count(*) from [" + tableName + "] where [" + fieldName + "]=@p";
                sp = new SqlParameter("@p", keyValue);
            }
            System.Diagnostics.Debug.WriteLine(sql);
            object r = SqlHelper.ExecuteScalar(AspMngConfiguration.DBConString, CommandType.Text, sql, sp);
            if (r == null) return 0;
            return Convert.ToInt32(r);
        }
        /// <summary>
        /// 填充表格,执行sql，封装了SqlConnection,自带一个参数
        /// </summary>
        /// <param name="sCmd"></param>
        /// <param name="dt"></param>
        /// <param name="ParnName"></param>
        /// <param name="value"></param>
        public static void FillTable(string sCmd, DataTable dt, string ParnName, object value)
        {
            SqlParameter[] objList = new SqlParameter[] { new SqlParameter(ParnName, value) };
            FillTable(sCmd, dt, objList);
        }



        /// <summary>
        /// 填充表格,执行sql，封装了SqlConnection,自带一个参数
        /// </summary>
        /// <param name="sCmd"></param>
        /// <param name="dt"></param>
        /// <param name="objList"></param>
        public static void FillTable(string sCmd, DataTable dt, params SqlParameter[] objList)
        {
            using (SqlConnection conn = new SqlConnection(AspMngConfiguration.DBConString))
            {
                conn.Open();
                SqlHelper.FillTable(conn, CommandType.Text, sCmd, dt, objList);
                conn.Close();
            }
        }


        /// <summary>
        /// 填充表格,执行sql，封装了SqlConnection,自带一个参数
        /// </summary>
        /// <param name="sCmd"></param>
        /// <param name="dt"></param>
        /// <param name="objList"></param>
        public static void FillTable(string sCmd, DataTable dt)
        {
            using (SqlConnection conn = new SqlConnection(AspMngConfiguration.DBConString))
            {
                conn.Open();
                SqlHelper.FillTable(conn, CommandType.Text, sCmd, dt, null);
                conn.Close();
            }
        }


        /// <summary>
        /// 填充表格,执行sql，封装了SqlConnection
        /// </summary>
        /// <param name="sCmd"></param>
        /// <param name="dt"></param>
        /// <param name="objList">SqlParameter </param>
        public static void FillTable(string sCmd, DataTable dt, ArrayList objList)
        {
            SqlParameter[] mm = CreateSqlParameterListByParamArray(objList);
            FillTable(sCmd, dt, mm);
        }

        ///// <summary>
        ///// 执行sql，封装了SqlConnection,自带一个参数
        ///// </summary>
        ///// <param name="sCmd"></param>
        ///// <param name="objList"></param>
        ///// <returns></returns>
        //public static int ExecuteNoQuery(string sCmd, string ParnName, object value)
        //{
        //    SqlParameter[] objList = new SqlParameter[] { new SqlParameter(ParnName, value) };
        //    return ExecuteNoQuery(sCmd, objList);
        //}
        ///// <summary>
        ///// 执行sql，封装了SqlConnection
        ///// </summary>
        ///// <param name="sCmd"></param>
        ///// <param name="objList"></param>
        ///// <returns></returns>
        //public static 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;
        //}
        /// <summary>
        /// 如
        /// </summary>
        /// <param name="cmd">select * from taba</param>
        /// <param name="sqlparamlist"></param>
        /// <param name="paramName"></param>
        /// <param name="paramValue"></param>
        /// <param name="scondition"></param>
        public static 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(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>
        private static 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>
        /// 根据Select 语句生成操作数据库DA
        /// </summary>
        /// <param name="cmdText">select * from [tabname??]</param>
        /// <returns></returns>
        public static 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>
        public static bool UpdateCurrentDataTable(string cmdText, DataTable dt, DataRowState rowType)
        {
            //由于默认为false，导致如果没有任何记录更新，则提示“保存失败”，现在改为默认为true；
            bool ret = true;
            //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;
        }
    }
}