Пример #1
0
        /// <summary>
        /// Execute by store with input and outupt params
        /// </summary>
        /// <param name="ExecuteStoreProcedureTransaction">Store name</param>
        /// <param name="commandTimeOut">Input params</param>
        /// <param name="inputParameters">Input params</param>
        /// <param name="outputParameters">Output params</param>
        public static void ExecuteStoreProcedureTransaction(SqlConnection connection, SqlTransaction tran, string storeProcedureName, int commandTimeOut, SqlDBParameter[] inputParameters, params SqlDBParameter[] outputParameters)
        {
            try
            {
                SqlCommand command = new SqlCommand(storeProcedureName, connection, tran);
                command.CommandType    = CommandType.StoredProcedure;
                command.CommandTimeout = commandTimeOut;

                if (inputParameters != null && inputParameters.Length > 0)
                {
                    foreach (SqlDBParameter param in inputParameters)
                    {
                        if (param != null)
                        {
                            SqlDataParameter.AddParameter(command, param.ParameterName, param.SqlDbType, param.Value);
                        }
                    }
                }

                // Output Parameters
                if (outputParameters != null && outputParameters.Length > 0)
                {
                    foreach (SqlDBParameter param in outputParameters)
                    {
                        if (param != null)
                        {
                            SqlDataParameter.AddParameter(command, ParameterDirection.Output, param.ParameterName, param.SqlDbType, param.Size, param.Value);
                        }
                    }
                }

                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }

                command.ExecuteNonQuery();

                if (outputParameters != null && outputParameters.Length > 0)
                {
                    foreach (SqlDBParameter outParam in outputParameters)
                    {
                        if (outParam != null)
                        {
                            SqlParameter param = command.Parameters[outParam.ParameterName];
                            outParam.Value     = param.Value;
                            outParam.SqlDbType = param.SqlDbType;
                            outParam.Size      = param.Size;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
Пример #2
0
        /// <summary>
        /// Search by comment text with condition search, and return dataset
        /// </summary>
        /// <param name="commandText">Comand text to search</param>
        /// <param name="inputParameters">Condition search</param>
        /// <returns>Data set</returns>
        public static DataSet SearchCommandText(string commandText, int commandTimeout, params SqlDBParameter[] inputParameters)
        {
            SqlCommand cmm = new SqlCommand(commandText);

            cmm.Connection     = new SqlConnection(connectionString);
            cmm.CommandType    = CommandType.Text;
            cmm.CommandTimeout = commandTimeout;
            SqlDataAdapter adp = new SqlDataAdapter();

            adp.SelectCommand = cmm;

            if (inputParameters != null && inputParameters.Length > 0)
            {
                foreach (SqlDBParameter param in inputParameters)
                {
                    if (param != null)
                    {
                        SqlDataParameter.AddParameter(cmm, param.ParameterName, param.SqlDbType, param.Value);
                    }
                }
            }
            DataSet result = new DataSet();

            try
            {
                if (cmm.Connection.State != ConnectionState.Open)
                {
                    cmm.Connection.Open();
                }
                adp.Fill(result);
            }
            catch (Exception ex)
            {
                result = null;
            }
            finally
            {
                if (cmm.Connection.State != ConnectionState.Closed)
                {
                    cmm.Connection.Close();
                }
                cmm.Connection.Dispose();
                cmm.Dispose();
            }

            return(result);
        }
Пример #3
0
        /// Executes the query, and returns the first column of the first row in the
        /// result set returned by the query. Additional columns or rows are ignored.
        /// </summary>
        /// <param name="commantText">Command Text</param>
        /// <param name="inputParameters">Input params</param>
        /// <returns>object</returns>
        public static object ExecuteScalarCommandText(string commantText, params SqlDBParameter[] inputParameters)
        {
            SqlCommand cmm = new SqlCommand(commantText);

            cmm.Connection  = new SqlConnection(connectionString);
            cmm.CommandType = CommandType.Text;

            if (inputParameters != null && inputParameters.Length > 0)
            {
                foreach (SqlDBParameter param in inputParameters)
                {
                    if (param != null)
                    {
                        SqlDataParameter.AddParameter(cmm, param.ParameterName, param.SqlDbType, param.Value);
                    }
                }
            }
            object result = null;

            try
            {
                if (cmm.Connection.State != ConnectionState.Open)
                {
                    cmm.Connection.Open();
                }
                result = cmm.ExecuteScalar();
            }
            catch (Exception ex)
            {
                result = null;
            }
            finally
            {
                if (cmm.Connection.State != ConnectionState.Closed)
                {
                    cmm.Connection.Close();
                }
                cmm.Connection.Dispose();
                cmm.Dispose();
            }

            return(result);
        }
Пример #4
0
        public static bool UpdateDataTable(DataRow[] rows, string commandText, params SqlDBParameter[] parameters)
        {
            SqlCommand command = new SqlCommand();

            command.Connection  = new SqlConnection(connectionString);
            command.CommandType = CommandType.Text;
            command.CommandText = commandText;
            if (parameters != null && parameters.Length > 0)
            {
                foreach (SqlDBParameter param in parameters)
                {
                    if (param != null)
                    {
                        SqlDataParameter.AddParameter(command, param.ParameterName, param.SqlDbType, param.Value);
                    }
                }
            }
            SqlDataAdapter adapter = new SqlDataAdapter(command);

            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
                adapter.Update(rows);
                return(true);
            }
            catch (Exception ex)
            {
                return(false);
            }
            finally
            {
                if (command.Connection.State != ConnectionState.Closed)
                {
                    command.Connection.Close();
                }
                command.Connection.Dispose();
                command.Dispose();
            }
        }
Пример #5
0
        /// <summary>
        /// Execute comand text with input params, and return bool
        /// </summary>
        /// <param name="commantText">Comand text</param>
        /// <param name="inputParameters">Inpurt param</param>
        /// <returns>True if success, False if error</returns>
        public static bool ExecuteCommandText(string commantText, params SqlDBParameter[] inputParameters)
        {
            SqlCommand cmm = new SqlCommand(commantText);

            cmm.Connection  = new SqlConnection(connectionString);
            cmm.CommandType = CommandType.Text;

            if (inputParameters != null && inputParameters.Length > 0)
            {
                foreach (SqlDBParameter param in inputParameters)
                {
                    if (param != null)
                    {
                        SqlDataParameter.AddParameter(cmm, param.ParameterName, param.SqlDbType, param.Value);
                    }
                }
            }
            bool result = false;

            try
            {
                if (cmm.Connection.State != ConnectionState.Open)
                {
                    cmm.Connection.Open();
                }
                cmm.ExecuteNonQuery();
                result = true;
            }
            finally
            {
                if (cmm.Connection.State != ConnectionState.Closed)
                {
                    cmm.Connection.Close();
                }
                cmm.Connection.Dispose();
                cmm.Dispose();
            }

            return(result);
        }
Пример #6
0
        /// <summary>
        /// Execute by store with input and outupt params
        /// </summary>
        /// <param name="storeProcedureName">Store name</param>
        /// <param name="inputParameters">Input params</param>
        /// <param name="outputParameters">Output params</param>
        public static void ExecuteStoreProcedure(string storeProcedureName, int commandTimeOut, SqlDBParameter[] inputParameters, params SqlDBParameter[] outputParameters)
        {
            SqlCommand cmm = new SqlCommand(storeProcedureName);

            cmm.Connection     = new SqlConnection(connectionString);
            cmm.CommandType    = CommandType.StoredProcedure;
            cmm.CommandTimeout = commandTimeOut;

            if (inputParameters != null && inputParameters.Length > 0)
            {
                foreach (SqlDBParameter param in inputParameters)
                {
                    if (param != null)
                    {
                        SqlDataParameter.AddParameter(cmm, param.ParameterName, param.SqlDbType, param.Value);
                    }
                }
            }
            // Output Parameters
            if (outputParameters != null && outputParameters.Length > 0)
            {
                foreach (SqlDBParameter param in outputParameters)
                {
                    if (param != null)
                    {
                        SqlDataParameter.AddParameter(cmm, ParameterDirection.Output, param.ParameterName, param.SqlDbType, param.Size, param.Value);
                    }
                }
            }
            try
            {
                if (cmm.Connection.State != ConnectionState.Open)
                {
                    cmm.Connection.Open();
                }
                cmm.ExecuteNonQuery();

                if (outputParameters != null && outputParameters.Length > 0)
                {
                    foreach (SqlDBParameter outParam in outputParameters)
                    {
                        if (outParam != null)
                        {
                            SqlParameter param = cmm.Parameters[outParam.ParameterName];
                            outParam.Value     = param.Value;
                            outParam.SqlDbType = param.SqlDbType;
                            outParam.Size      = param.Size;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                string a = ex.Message;
            }
            finally
            {
                if (cmm.Connection.State != ConnectionState.Closed)
                {
                    cmm.Connection.Close();
                }
                cmm.Connection.Dispose();
                cmm.Dispose();
            }
        }
Пример #7
0
        /// <summary>
        /// Search by command text with condiction search and return List data base on type of T
        /// </summary>
        /// <typeparam name="T">The type of element in the list</typeparam>
        /// <param name="commandText">Comand text to search</param>
        /// <param name="inputParameters">Condition search</param>
        /// <returns>List data with type is T</returns>
        public static List <T> SearchStoreProcedure <T>(string storeProcedureName, params SqlDBParameter[] inputParameters)
        {
            List <T> result = new List <T>();
            Type     myType = typeof(T);

            if (myType != null)
            {
                SqlCommand cmm = new SqlCommand(storeProcedureName);
                cmm.Connection  = new SqlConnection(connectionString);
                cmm.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter adp = new SqlDataAdapter();
                adp.SelectCommand = cmm;

                if (inputParameters != null && inputParameters.Length > 0)
                {
                    foreach (SqlDBParameter param in inputParameters)
                    {
                        if (param != null)
                        {
                            SqlDataParameter.AddParameter(cmm, param.ParameterName, param.SqlDbType, param.Value);
                        }
                    }
                }

                try
                {
                    if (cmm.Connection.State != ConnectionState.Open)
                    {
                        cmm.Connection.Open();
                    }

                    IDataReader dre = cmm.ExecuteReader();

                    FieldInfo[] myFieldInfo;

                    // Get the type and fields of FieldInfoClass.
                    myFieldInfo = myType.GetFields(BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public);

                    if (myFieldInfo.Length <= dre.FieldCount)
                    {
                        while (dre.Read())
                        {
                            object objNew = System.Activator.CreateInstance(typeof(T), true);
                            SetValueForObject(dre, myFieldInfo, objNew);
                            result.Add((T)objNew);
                        }
                    }
                    else
                    {
                        while (dre.Read())
                        {
                            object objNew = System.Activator.CreateInstance(typeof(T), true);
                            SetValueForObject(dre, myType, objNew);
                            result.Add((T)objNew);
                        }
                    }
                }
                catch (Exception ex)
                {
                    result = null;
                }
                finally
                {
                    if (cmm.Connection.State != ConnectionState.Closed)
                    {
                        cmm.Connection.Close();
                    }
                    cmm.Connection.Dispose();
                    cmm.Dispose();
                }
            }
            return(result);
        }
Пример #8
0
        /// <summary>
        /// Châu thêm vào để lấy schema của du liệu tra ra
        /// </summary>
        /// <param name="SchemaTable"></param>
        /// <param name="storeProcedureName"></param>
        /// <param name="inputParameters"></param>
        /// <returns></returns>
        public static DataTable SearchStoreProcedureDataTable(DataTable SchemaTable, string storeProcedureName, params SqlDBParameter[] inputParameters)
        {
            //DataSet ds = SearchStoreProcedure(storeProcedureName, ConstantClass.timeout, inputParameters);
            //if (ds != null && ds.Tables.Count > 0)
            //{
            //    return ds.Tables[0];

            //}
            //return null;
            SqlCommand cmm = new SqlCommand(storeProcedureName);

            cmm.Connection     = new SqlConnection(connectionString);
            cmm.CommandType    = CommandType.StoredProcedure;
            cmm.CommandTimeout = ConstantClass.timeout;
            SqlDataAdapter adp = new SqlDataAdapter();

            adp.SelectCommand = cmm;

            if (inputParameters != null && inputParameters.Length > 0)
            {
                foreach (SqlDBParameter param in inputParameters)
                {
                    if (param != null)
                    {
                        SqlDataParameter.AddParameter(cmm, param.ParameterName, param.SqlDbType, param.Value);
                    }
                }
            }
            DataSet   dataS  = new DataSet();
            DataTable Result = new DataTable();

            System.Data.Common.DataTableMapping mapping = new System.Data.Common.DataTableMapping();
            try
            {
                if (cmm.Connection.State != ConnectionState.Open)
                {
                    cmm.Connection.Open();
                }
                adp.Fill(dataS);

                //adp.Fill(dataS);
                Result = dataS.Tables[0];
                adp.FillSchema(dataS, SchemaType.Mapped, "#HRDMaster");
                //mapping = adp.TableMappings.Add("Table", "table0");
                //foreach (DataColumn col in Result.Columns)
                //{
                //    mapping.ColumnMappings.Add(col.ColumnName.ToString(), col.ColumnName.ToString());
                //}
                //adp.FillSchema(dataS, SchemaType.Mapped);
                SchemaTable = dataS.Tables[1];
            }
            catch (Exception ex)
            {
                string str = ex.Message.ToString();
                //if (Result==)
                //Result = null;
            }
            finally
            {
                if (cmm.Connection.State != ConnectionState.Closed)
                {
                    cmm.Connection.Close();
                }
                cmm.Connection.Dispose();
                cmm.Dispose();
            }
            return(Result);
        }
Пример #9
0
        /// <summary>
        /// Search by store with condition search, and return dataset
        /// </summary>
        /// <param name="storeProcedureName">Store name</param>
        /// <param name="inputParameters">Condition search</param>
        /// <param name="outputParameters">List output paramaters</param>
        /// <returns>Data set</returns>
        public static DataSet SearchStoreProcedure(string storeProcedureName, int commandTimeout, SqlDBParameter[] inputParameters, params SqlDBParameter[] outputParameters)
        {
            SqlCommand cmm = new SqlCommand(storeProcedureName);

            cmm.Connection     = new SqlConnection(connectionString);
            cmm.CommandType    = CommandType.StoredProcedure;
            cmm.CommandTimeout = commandTimeout;
            SqlDataAdapter adp = new SqlDataAdapter();

            adp.SelectCommand = cmm;

            // Input Parameters
            if (inputParameters != null && inputParameters.Length > 0)
            {
                foreach (SqlDBParameter param in inputParameters)
                {
                    if (param != null)
                    {
                        SqlDataParameter.AddParameter(cmm, param.ParameterName, param.SqlDbType, param.Value);
                    }
                }
            }
            // Output Parameters
            if (outputParameters != null && outputParameters.Length > 0)
            {
                foreach (SqlDBParameter param in outputParameters)
                {
                    if (param != null)
                    {
                        SqlDataParameter.AddParameter(cmm, ParameterDirection.Output, param.ParameterName, param.SqlDbType, param.Size, param.Value);
                    }
                }
            }
            DataSet result = new DataSet();

            try
            {
                if (cmm.Connection.State != ConnectionState.Open)
                {
                    cmm.Connection.Open();
                }
                adp.Fill(result);

                // return out parameters.
                if (outputParameters != null)
                {
                    foreach (SqlDBParameter outParam in outputParameters)
                    {
                        SqlParameter param = cmm.Parameters[outParam.ParameterName];
                        outParam.Value     = param.Value;
                        outParam.SqlDbType = param.SqlDbType;
                        outParam.Size      = param.Size;
                    }
                }
            }
            catch (Exception ex)
            {
                result = null;
            }
            finally
            {
                if (cmm.Connection.State != ConnectionState.Closed)
                {
                    cmm.Connection.Close();
                }
                cmm.Connection.Dispose();
                cmm.Dispose();
            }
            return(result);
        }