示例#1
0
        /// <summary>
        /// Runs a non-data returning SQL query
        /// </summary>
        public static int RunNonQuery(string sql, DbParameter[] parameters, CommandType cmdType, bool needNewlyGeneratedKey)
        {
            int result = 0;

            // create provider-independent connection
            using (DbConnection conn = Factory.CreateConnection())
            {
                conn.ConnectionString = ConnectionSetting.ConnectionString;

                // create provider-independent command
                DbCommand cmd = Factory.CreateCommand();
                cmd.Connection  = conn;
                cmd.CommandText = sql;
                cmd.CommandType = cmdType;

                // add parameters
                if (parameters != null)
                {
                    foreach (DbParameter p in parameters)
                    {
                        cmd.Parameters.Add(p);
                    }
                }

                try
                {
                    conn.Open();

                    result = cmd.ExecuteNonQuery();

                    if (needNewlyGeneratedKey)
                    {
                        DbCommand cmd2 = Factory.CreateCommand();
                        cmd2.Connection  = conn;
                        cmd2.CommandText = "SELECT @@IDENTITY";

                        result = Convert.ToInt32(cmd2.ExecuteScalar());
                    }
                }
                catch (Exception ex)
                {
                    // any errors will be handled by our custom exception handler
                    DataAccessExceptionHandler.HandleException("DataHelper", "RunNonQuery", ex.Message);
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                return(result);
            }
        }
示例#2
0
        /// <summary>
        /// Returns a DataTable filled with data specified by name and parameters
        /// </summary>
        public static DataTable GetDataTable(string name, CommandType cmdType, DbParameter[] parameters)
        {
            DataTable dt = null;

            try
            {
                // create provider-independent connection
                DbConnection conn = Factory.CreateConnection();
                conn.ConnectionString = ConnectionSetting.ConnectionString;

                // create provider-independent data adapter
                DbDataAdapter adapter = Factory.CreateDataAdapter();

                DbCommand cmd = Factory.CreateCommand();
                cmd.Connection        = conn;
                cmd.CommandText       = name;
                cmd.CommandType       = cmdType;
                adapter.SelectCommand = cmd;;

                // add parameters
                if (parameters != null)
                {
                    foreach (DbParameter p in parameters)
                    {
                        adapter.SelectCommand.Parameters.Add(p);
                    }
                }

                // create data table
                dt = new DataTable();

                // fill a new data table with data from adapter
                adapter.Fill(dt);
            }
            catch (Exception ex)
            {
                // any errors will be handled by our custom exception handler
                DataAccessExceptionHandler.HandleException("DataHelper", "GetDataTable", ex.Message);
            }

            // Return the DataTable
            return(dt);
        }
示例#3
0
        /// <summary>
        /// Runs a scalar returning SQL query
        /// </summary>
        public static object RunScalar(string sql, DbParameter[] parameters)
        {
            object scalar = null;

            // create provider-independent connection
            using (DbConnection conn = Factory.CreateConnection())
            {
                conn.ConnectionString = ConnectionSetting.ConnectionString;

                // create provider-independent command
                DbCommand cmd = Factory.CreateCommand();
                cmd.Connection  = conn;
                cmd.CommandText = sql;

                // add parameters
                if (parameters != null)
                {
                    foreach (DbParameter p in parameters)
                    {
                        cmd.Parameters.Add(p);
                    }
                }

                try
                {
                    conn.Open();
                    scalar = cmd.ExecuteScalar();
                }
                catch (Exception ex)
                {
                    // any errors will be handled by our custom exception handler
                    DataAccessExceptionHandler.HandleException("DataHelper", "RunScalar", ex.Message);
                }
                finally
                {
                    if (conn != null)
                    {
                        conn.Close();
                    }
                }
                return(scalar);
            }
        }