Exemplo n.º 1
0
        /// <summary>
        /// 获取数据表
        /// </summary>
        public WCDataQuery Execute(WCDataQuery sqlQuery)
        {
            DataTable      dt             = new DataTable(sqlQuery.TableName);
            SqlDataAdapter sqlDataAdapter = null;
            SqlConnection  con            = null;

            try
            {
                //更新语句
                if (sqlQuery.IsUpdate)
                {
                    con = new SqlConnection(this.SqlConnectionString);
                    SqlCommand com = new SqlCommand(sqlQuery.Sql, con);
                    con.Open();
                    if (com.ExecuteNonQuery() > 0)
                    {
                        sqlQuery.ExecuteState = true;
                        com.Dispose();
                    }
                }
                else
                {
                    //普通查询
                    if (sqlQuery.Paging == null)
                    {
                        sqlDataAdapter = new SqlDataAdapter(sqlQuery.Sql, this.SqlConnectionString);
                        if (sqlQuery.ContainsSchema)
                        {
                            sqlDataAdapter.FillSchema(dt, SchemaType.Source);
                        }
                        sqlDataAdapter.Fill(dt);
                        sqlQuery.ExecuteState = true;
                        sqlQuery.DataTable    = dt;
                    }
                    else//分页查询
                    {
                        //组装SQL语句,该语句只支持SQL2005及以上版本
                        string sql  = "SELECT {0} FROM {1} {2} {3}";
                        string sql1 = "SELECT COUNT(*) as RecordCount FROM {0} {1}";
                        string where = "";
                        if (sqlQuery.Where != null && sqlQuery.Where.Trim().Length > 0)
                        {
                            where = " WHERE " + sqlQuery.Where;
                        }
                        Int64 fromRowIndex = sqlQuery.Paging.PageIndex * sqlQuery.Paging.PageSize - sqlQuery.Paging.PageSize + 1;
                        Int64 toRowIndex   = sqlQuery.Paging.PageIndex * sqlQuery.Paging.PageSize;
                        sql  = "SELECT * FROM (SELECT {0}, row_number() OVER (ORDER BY {1}) AS OrderIndex FROM {2} {3}) AS T WHERE T.OrderIndex BETWEEN {4} AND {5}";
                        sql  = string.Format(sql, sqlQuery.Fields, sqlQuery.OrderBy, sqlQuery.TableName, where, fromRowIndex, toRowIndex);
                        sql1 = string.Format(sql1, sqlQuery.TableName, where);

                        //获取记录总条数
                        sqlDataAdapter = new SqlDataAdapter(sql1, this.SqlConnectionString);
                        DataTable dt1 = new DataTable();
                        sqlDataAdapter.Fill(dt1);
                        sqlQuery.Paging.RecordCount = Convert.ToInt64(dt1.Rows[0]["RecordCount"]);
                        //获取分页数据
                        sqlDataAdapter = new SqlDataAdapter(sql, this.SqlConnectionString);
                        if (sqlQuery.ContainsSchema)
                        {
                            sqlDataAdapter.FillSchema(dt, SchemaType.Source);
                        }
                        sqlDataAdapter.Fill(dt);
                        sqlQuery.ExecuteState = true;
                        sqlQuery.DataTable    = dt;
                        if (dt.Columns.Contains("OrderIndex"))
                        {
                            dt.Columns.Remove("OrderIndex");
                        }
                    }
                }
            }
            catch (System.Exception ex)
            {
                sqlQuery.ExecuteState = false;
                sqlQuery.ErrorMessage = ex.Message;
                throw ex;
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
                dt.Dispose();
                if (sqlDataAdapter != null)
                {
                    sqlDataAdapter.Dispose();
                }
            }
            return(sqlQuery);
        }
Exemplo n.º 2
0
        /// <summary>
        /// 批量更新,支持SQL语句,DataTable类型
        /// </summary>
        //public WCDataUpdate Execute(WCDataUpdate sqlDataUpdate)
        //{
        //    SqlTransaction sqlTransaction = null;
        //    SqlConnection sqlConnection = new SqlConnection(this.SqlConnectionString);
        //    try
        //    {
        //        sqlConnection.Open();
        //        if (sqlDataUpdate.UseTransaction)
        //            sqlTransaction = sqlConnection.BeginTransaction();

        //    }
        //    catch (Exception ex)
        //    {
        //        sqlDataUpdate.ErrorMessage = "[CONNECT ERROR] " + ex.Message;
        //        sqlDataUpdate.ExecuteState = false;
        //        return sqlDataUpdate;
        //    }

        //    try
        //    {

        //        foreach (object sqlDataObj in sqlDataUpdate.DataObjects)
        //        {
        //            if (sqlDataObj is String)
        //            {
        //                SqlCommand sqlCommand = new SqlCommand(sqlDataObj.ToString(), sqlConnection);
        //                sqlCommand.CommandType = CommandType.Text;
        //                if (sqlDataUpdate.UseTransaction)
        //                    sqlCommand.Transaction = sqlTransaction;
        //                sqlCommand.ExecuteNonQuery();
        //            }
        //            else if (sqlDataObj is DataTable)
        //            {
        //                DataTable dt = sqlDataObj as DataTable;
        //                string sql = "SELECT TOP 0 * FROM [" + dt.TableName + "]";
        //                SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sql, sqlConnection);
        //                if (sqlDataUpdate.UseTransaction)
        //                    sqlDataAdapter.SelectCommand.Transaction = sqlTransaction;
        //                SqlCommandBuilder builder = new SqlCommandBuilder(sqlDataAdapter);
        //                builder.ConflictOption = ConflictOption.CompareRowVersion;
        //                builder.SetAllValues = true;
        //                sqlDataAdapter.InsertCommand = builder.GetInsertCommand(true);
        //                sqlDataAdapter.UpdateCommand = builder.GetUpdateCommand(true);
        //                sqlDataAdapter.DeleteCommand = builder.GetDeleteCommand(true);
        //                if (sqlDataUpdate.UseTransaction)
        //                {
        //                    sqlDataAdapter.DeleteCommand.Transaction = sqlTransaction;
        //                    sqlDataAdapter.UpdateCommand.Transaction = sqlTransaction;
        //                    sqlDataAdapter.InsertCommand.Transaction = sqlTransaction;
        //                }
        //                sqlDataAdapter.Update(dt);
        //            }
        //            else if (sqlDataObj is WCDataStoreProcedures)
        //            {
        //                this.Execute((sqlDataObj as WCDataStoreProcedures));
        //            }
        //        }

        //        if (sqlDataUpdate.UseTransaction)
        //            sqlTransaction.Commit();
        //        sqlConnection.Close();
        //        sqlDataUpdate.ExecuteState = true;
        //    }
        //    catch (System.Exception ex)
        //    {
        //        sqlDataUpdate.ExecuteState = false;
        //        sqlDataUpdate.ErrorMessage = ex.Message;
        //        if (sqlDataUpdate.UseTransaction && sqlTransaction != null)
        //            sqlTransaction.Rollback();
        //        sqlConnection.Close();
        //        throw ex;
        //    }
        //    finally
        //    {
        //        if (sqlTransaction != null) sqlTransaction.Dispose();
        //        if (sqlConnection.State == ConnectionState.Open)
        //            sqlConnection.Close();
        //        sqlConnection.Dispose();
        //    }

        //    return sqlDataUpdate;
        //}

        /// <summary>
        /// 执行存储过程
        /// </summary>
        public WCDataStoreProcedures Execute(WCDataStoreProcedures sp)
        {
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(sp.SpName, this.SqlConnectionString);

            sqlDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;

            //加入输入参数
            if (sp.InputPars != null)
            {
                for (int i = 0; i <= sp.InputPars.Count - 1; i++)
                {
                    string inKey   = sp.InputPars.Keys[i];
                    object inValue = sp.InputPars.Values[i];
                    if (inValue == null || inValue.ToString().Trim().Length == 0)
                    {
                        inValue = System.DBNull.Value;
                    }
                    WCDataHelper.SetInputParameter(sqlDataAdapter, inKey, inValue);
                }
            }
            //加入分页
            if (sp.DataPaging != null)
            {
                WCDataHelper.SetInputParameter(sqlDataAdapter, "@PageIndex", sp.DataPaging.PageIndex);
                WCDataHelper.SetInputParameter(sqlDataAdapter, "@PageSize", sp.DataPaging.PageSize);
            }
            //加入默认动作参数
            if (sp.DataAction != WCDataAction.None && !sp.InputPars.ContainsKey(sp.DataAction.ToString()))
            {
                WCDataHelper.SetInputParameter(sqlDataAdapter, "@ActionType", sp.DataAction.ToString());
            }

            //获取输出参数
            string      sql = "SELECT b.name AS column_name,c.name AS data_type, b.length FROM sysobjects a INNER JOIN syscolumns b ON a.id = b.id INNER JOIN systypes c ON b.xusertype = c.xusertype WHERE (a.name = '" + sp.SpName + "') AND (b.isoutparam = 1)";
            WCDataQuery gdc = new WCDataQuery();

            gdc.Sql = sql;
            DataTable dt = new DataTable();

            if (this.Execute(gdc).ExecuteState)
            {
                dt = gdc.DataTable;
            }

            //加入输出参数
            for (int n = 0; n <= dt.Rows.Count - 1; n++)
            {
                SqlParameter SqlParOutput = new SqlParameter();
                SqlParOutput.ParameterName = dt.Rows[n]["column_name"].ToString();
                SqlParOutput.SqlDbType     = WCDataHelper.GetFieldDataType(dt.Rows[n]["data_type"].ToString());
                SqlParOutput.Size          = WCDataHelper.GetFieldDataSize(SqlParOutput.SqlDbType, dt.Rows[n]["length"].ToString());
                SqlParOutput.Direction     = ParameterDirection.Output;
                if (!sp.OutputPars.ContainsKey(dt.Rows[n]["column_name"].ToString()))
                {
                    sp.OutputPars.Add(dt.Rows[n]["column_name"].ToString(), "");
                }
                sqlDataAdapter.SelectCommand.Parameters.Add(SqlParOutput);
                sqlDataAdapter.SelectCommand.CommandTimeout = 180;
            }

            try
            {
                //执行存储过程并获取返回记录和输出参数
                sqlDataAdapter.Fill(sp.OutputDataSet);
                foreach (SqlParameter par1 in sqlDataAdapter.SelectCommand.Parameters)
                {
                    if (par1.Direction == ParameterDirection.Output &&
                        par1.Value != System.DBNull.Value)
                    {
                        sp.OutputPars[par1.ParameterName] = par1.Value;
                        if (par1.ParameterName == "@RecordCount" && sp.DataPaging != null)
                        {
                            sp.DataPaging.RecordCount = (Int64)par1.Value;
                        }
                    }
                }

                sp.ExecuteState = true;
            }
            catch (System.Exception ex)
            {
                sp.ExecuteState = false;
                sp.ErrorMessage = ex.Message;
                throw ex;
            }
            finally
            {
                dt.Dispose();
                sqlDataAdapter.Dispose();
            }

            return(sp);
        }