Ejemplo n.º 1
0
        public static void Execute(string connectionString, string commandText, SqlParameter[] procParams,
                                   DataTableMapping[] tableMappings, DataSet DS, MissingSchemaAction schemaAction, int commandTimeout)
        {
            SqlCommand cmd = new SqlCommand();

            try
            {
                using (SqlConnection cn = new SqlConnection(connectionString))
                {
                    // create and prepare command
                    BaseSqlHelper.PrepareCommand(cmd, cn, commandText, procParams, commandTimeout);
                    // create data adapter, add table mappings and set selectcommand property
                    SqlDataAdapter sqlDa = new SqlDataAdapter();
                    foreach (DataTableMapping dMap in tableMappings)
                    {
                        sqlDa.TableMappings.Add(dMap);
                    }
                    sqlDa.SelectCommand       = cmd;
                    sqlDa.MissingSchemaAction = schemaAction;
                    // fill data set and return
                    sqlDa.Fill(DS);
                }
            }
            catch (Exception ex)
            {
                LogException(connectionString, ex, cmd);
            }
        }
Ejemplo n.º 2
0
        /// <summary>
        /// Execute a stored procedure and store the results
        /// </summary>
        /// <param name="connectionString">String containing parameters needed to connect to the database</param>
        /// <param name="commandText">Name of the stored procedure or query</param>
        /// <param name="procParams">array of parameters to pass in to the stored procedure</param>
        /// <param name="table">DataTable to be filled with results of the query</param>
        /// <param name="strictSchema">If true, the table's schema should be held strictly and not be
        /// appended even if the resulting set contains extra data.  If false, any extra columns
        /// returned will be appended to the structure of the table.</param>
        /// <param name="commandTimeout">The command timeout.</param>
        public static void Execute(string connectionString, string commandText, SqlParameter[] procParams,
                                   DataTable table, bool strictSchema, int commandTimeout)
        {
            SqlCommand cmd = new SqlCommand();

            try
            {
                using (SqlConnection cn = new SqlConnection(connectionString))
                {
                    // create and prepare command
                    BaseSqlHelper.PrepareCommand(cmd, cn, commandText, procParams, commandTimeout);
                    // create data adapter, add table mappings and set selectcommand property
                    SqlDataAdapter sqlDa = new SqlDataAdapter();
                    sqlDa.SelectCommand       = cmd;
                    sqlDa.MissingSchemaAction = (strictSchema ? MissingSchemaAction.Ignore : MissingSchemaAction.Add);
                    // fill data set and return
                    sqlDa.Fill(table);
                    return;
                }
            }
            catch (Exception ex)
            {
                LogException(connectionString, ex, cmd);
            }
        }
Ejemplo n.º 3
0
        public static string ExecuteForXml(string connectionString, string commandText, SqlParameter[] procParams, int commandTimeout)
        {
            SqlCommand    cmd = new SqlCommand();
            StringBuilder xml = null;

            try
            {
                using (SqlConnection cn = new SqlConnection(connectionString))
                {
                    // create and prepare command, execute to datareader
                    BaseSqlHelper.PrepareCommand(cmd, cn, commandText, procParams, commandTimeout);
                    using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        // build and return xml string
                        xml = new StringBuilder();
                        while (dr.Read())
                        {
                            xml.Append(dr.GetString(0));
                        }

                        cmd.Parameters.Clear();
                    }
                }
            }
            catch (Exception ex)
            {
                LogException(connectionString, ex, cmd);
            }
            return(xml.ToString());
        }
Ejemplo n.º 4
0
 public static SqlDataReader ExecuteReader(string connectionString, string commandText, params object[] paramValues)
 {
     // get parameters and assign values
     SqlParameter[] procParams = BaseSqlHelper.GetParams(connectionString, commandText);
     BaseSqlHelper.AssignParams(procParams, paramValues);
     return(BaseSqlHelper.ExecuteReader(connectionString, commandText, procParams));
 }
Ejemplo n.º 5
0
 public static string ExecuteForXml(string connectionString, string commandText, params object[] paramValues)
 {
     // get parameters and assign values
     SqlParameter[] procParams = BaseSqlHelper.GetParams(connectionString, commandText);
     BaseSqlHelper.AssignParams(procParams, paramValues);
     // defer execution to overloaded method w/ parameter array
     return(BaseSqlHelper.ExecuteForXml(connectionString, commandText, procParams));
 }
Ejemplo n.º 6
0
        public static void ExecuteNonQuery(string connectionString, string commandText, params object[] paramValues)
        {
            // get parameters and assign values
            SqlParameter[] procParams = BaseSqlHelper.GetParams(connectionString, commandText);
            BaseSqlHelper.AssignParams(procParams, paramValues);
            // propagate execution to overloaded method w/ parameter array
            BaseSqlHelper.ExecuteNonQuery(connectionString, commandText, procParams);

            return;
        }
Ejemplo n.º 7
0
        public static SqlParameterCollection ExecuteReturnParam(string connectionString, string commandText, SqlParameter[] procParams)
        {
            SqlCommand cmd = new SqlCommand();

            try
            {
                using (SqlConnection cn = new SqlConnection(connectionString))
                {
                    // create and prepare command
                    BaseSqlHelper.PrepareCommand(cmd, cn, commandText, procParams);
                    // execute proc and return parameter collection
                    cmd.ExecuteNonQuery();
                    //CesTrace.Trace(cmd, sw);
                }
            }
            catch (Exception ex)
            {
                LogException(connectionString, ex, cmd);
            }
            return(cmd.Parameters);
        }
Ejemplo n.º 8
0
        public static XmlReader ExecuteXmlReader(SqlConnection cn, string commandText, SqlParameter[] procParams)
        {
            SqlCommand cmd    = new SqlCommand();
            XmlReader  retVal = null;

            try
            {
                // create and prepare command
                cmd = new SqlCommand(commandText, cn);
                BaseSqlHelper.PrepareCommand(cmd, cn, commandText, procParams);
                // execute procedure and return XmlReader
                retVal = cmd.ExecuteXmlReader();
                cmd.Parameters.Clear();
                //CesTrace.Trace(cmd, sw);
            }
            catch (Exception ex)
            {
                string connectionString = cn.ConnectionString;
                LogException(connectionString, ex, cmd);
            }
            return(retVal);
        }
Ejemplo n.º 9
0
        public static SqlDataReader ExecuteReader(string connectionString, string commandText, SqlParameter[] procParams, int commandTimeout)
        {
            SqlCommand    cmd    = new SqlCommand();
            SqlDataReader retVal = null;

            try
            {
                // create connection and command and prepare command
                SqlConnection cn = new SqlConnection(connectionString);
                //Automatically assign culture value to procedure parameter
                //SetCultureParam(procParams);
                BaseSqlHelper.PrepareCommand(cmd, cn, commandText, procParams, commandTimeout);
                // execute procedure and return data reader
                retVal = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                cmd.Parameters.Clear();
            }
            catch (Exception ex)
            {
                LogException(connectionString, ex, cmd);
            }
            return(retVal);
        }
Ejemplo n.º 10
0
        private static void ExecuteNonQuery(string connectionString, string commandText, SqlParameter[] procParams, bool async, AsyncCallback callback, object stateObject)
        {
            SqlCommand cmd = new SqlCommand();

            try
            {
                using (SqlConnection cn = new SqlConnection(connectionString))
                {
                    //Automatically assign culture value to procedure parameter

                    // create and prepare command
                    BaseSqlHelper.PrepareCommand(cmd, cn, commandText, procParams);

                    // execute proc and return
                    if (async)
                    {
                        if (callback != null)
                        {
                            cmd.BeginExecuteNonQuery(callback, stateObject);
                        }
                        else
                        {
                            cmd.BeginExecuteNonQuery();
                        }
                    }
                    else
                    {
                        int retVal = cmd.ExecuteNonQuery();
                    }
                    cmd.Parameters.Clear();

                    return;
                }
            }
            catch (Exception ex)
            {
                LogException(connectionString, ex, cmd);
            }
        }
Ejemplo n.º 11
0
        public static object ExecuteScalar(string connectionString, string commandText, SqlParameter[] procParams)
        {
            SqlCommand cmd    = new SqlCommand();
            object     retVal = null;

            try
            {
                using (SqlConnection cn = new SqlConnection(connectionString))
                {
                    // create and prepare command
                    BaseSqlHelper.PrepareCommand(cmd, cn, commandText, procParams);
                    // execute proc and return object

                    retVal = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return(retVal);
                }
            }
            catch (Exception ex)
            {
                LogException(connectionString, ex, cmd);
            }
            return(null);
        }
Ejemplo n.º 12
0
        public static int ExecuteReturnValue(string connectionString, string commandText, ref SqlParameter[] procParams)
        {
            SqlCommand cmd = new SqlCommand();

            try
            {
                using (SqlConnection cn = new SqlConnection(connectionString))
                {
                    // create and prepare command
                    BaseSqlHelper.PrepareCommand(cmd, cn, commandText, procParams);
                    // execute proc and return parameter collection
                    SqlParameter returnValue = new SqlParameter("RETURN_VALUE", SqlDbType.Int);
                    returnValue.Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(returnValue);

                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                LogException(connectionString, ex, cmd);
            }
            return(Convert.ToInt32(cmd.Parameters["RETURN_VALUE"].Value));
        }
Ejemplo n.º 13
0
 public static string ExecuteForXml(string connectionString, string commandText)
 {
     return(BaseSqlHelper.ExecuteForXml(connectionString, commandText, (SqlParameter[])null));
 }
Ejemplo n.º 14
0
 public static SqlDataReader ExecuteReader(string connectionString, string commandText)
 {
     return(BaseSqlHelper.ExecuteReader(connectionString, commandText, (SqlParameter[])null));
 }