public int addNewUser(string firstname, string lastname, string companyname, string email, string password, string countryid, string stateid, string mobile, int type, string verify)
        {
            int status = 0;
            try
            {
                Guid guid = Guid.NewGuid();
                Database objDB = new SqlDatabase(connectionStr);
                DbCommand objAdd = new SqlCommand();
                objAdd.CommandType = CommandType.StoredProcedure;
                objAdd.CommandText = "InsertUser";
                objDB.AddInParameter(objAdd, "@FName", DbType.String, firstname);
                objDB.AddInParameter(objAdd, "@LName", DbType.String, lastname);
                objDB.AddInParameter(objAdd, "@Companyname", DbType.String, companyname);
                objDB.AddInParameter(objAdd, "@Email", DbType.String, email);
                objDB.AddInParameter(objAdd, "@Password", DbType.String, password);
                objDB.AddInParameter(objAdd, "@Countryid", DbType.Int32, countryid);
                objDB.AddInParameter(objAdd, "@Stateid", DbType.Int32, stateid);
                objDB.AddInParameter(objAdd, "@Mobile", DbType.String, mobile);
                objDB.AddInParameter(objAdd, "@Type", DbType.Int32, type);
                objDB.AddInParameter(objAdd, "@Verify", DbType.String, verify);
                objDB.AddOutParameter(objAdd, "@Stat", DbType.Int16, 16);
                objDB.ExecuteNonQuery(objAdd);
                status = Convert.ToInt16(objDB.GetParameterValue(objAdd, "@Stat"));

                return status;
            }
            catch (Exception ex)
            {
                objErr.GeneralExceptionHandling(ex, "Website - User Registration", "addNewUser", "GENERAL EXCEPTION");
                return status;
            }
        }

        protected void btnProcessManualBox_Click( object sender, EventArgs e )
        {
            StatGrabber.StatGrabber sg = new StatGrabber.StatGrabber();
            SqlDatabase db = new SqlDatabase( System.Configuration.ConfigurationManager.AppSettings["ConnectionString"] );
            ArrayList problems = new ArrayList();
            try
            {
                ArrayList perfs = sg.GetGamePerformances( tbManualBoxURL.Text, problems );
                tbOutput.Text += "Got " + perfs.Count + " perfs from " + tbManualBoxURL.Text + "\r\n";
                problems.AddRange( sg.SavePerformances( db, perfs, calStatDate.SelectedDate ) );
            }
            catch( StatGrabber.StatGrabberException ex )
            {
                tbOutput.Text += ex.Message;
            }
            if( problems.Count > 0 )
            {
                tbOutput.Text += "Problems:\r\n";
                foreach( StatGrabber.PlayerPerformance p in problems )
                {
                    tbOutput.Text += p.FirstName + " " + p.LastName + " " + p.TeamName + "\r\n";
                }
            }
            else
            {
                tbOutput.Text += "No problems identifying players\r\n";
            }

            tbOutput.Text += sg.UpdateAveragesAndScores( db, calStatDate.SelectedDate );

            Log.AddLogEntry(
                LogEntryTypes.StatsProcessed,
                Page.User.Identity.Name,
                tbOutput.Text );
        }

        public int addInquiry(string name, string email, string subject, string message)
        {
            int status = 0;
            try
            {
                Database objDB = new SqlDatabase(connectionStr);
                DbCommand objAdd = new SqlCommand();
                objAdd.CommandType = CommandType.StoredProcedure;
                objAdd.CommandText = "InsertInquiry";
                objDB.AddInParameter(objAdd, "@Name", DbType.String, name);
                objDB.AddInParameter(objAdd, "@Email", DbType.String, email);
                objDB.AddInParameter(objAdd, "@Subject", DbType.String, subject);
                objDB.AddInParameter(objAdd, "@Message", DbType.String, message);
                objDB.AddOutParameter(objAdd, "@Stat", DbType.Int16, 16);
                objDB.ExecuteNonQuery(objAdd);
                status = Convert.ToInt16(objDB.GetParameterValue(objAdd, "@Stat"));

                return status;
            }
            catch (Exception ex)
            {
                objCommom.LogFile("Contact.aspx", "addInquiry", ex);
                return status;
            }
        }

        public override CustomerList GetCustomer(string vipCode)
        {
            SqlDatabase database = new SqlDatabase(ConnectionString);
            DbCommand command = database.GetStoredProcCommand("rmSP_WSPOS_GetCustomer");
            command.CommandTimeout = 300;
            database.AddInParameter(command, "@VipCode", DbType.String, vipCode);

            List<Customer> customers = new List<Customer>();
            using (IDataReader reader = database.ExecuteReader(command))
            {
                while (reader.Read())
                {
                    Customer customer = new Customer();
                    customer.CustomerId = Convert.ToInt32(reader["VIPCode_id"]);
                    customer.CustomerCode = reader["VipCode"] as string;
                    customer.FirstName = reader["VIPGName"] as string;
                    customer.LastName = reader["VIPName"] as string;
                    customer.Telephone = reader["VIPTel"] as string;
                    if (reader["VIPBDay"] != DBNull.Value)
                        customer.BirthDate = Convert.ToDateTime(reader["VIPBDay"]);

                    customers.Add(customer);
                }
            }

            CustomerList customerList = new CustomerList();
            customerList.Customers = customers;
            customerList.TotalCount = customers.Count;
            return customerList;
        }

 protected string Autosub( string weekId, SqlDatabase db, DateTime selectedDate )
 {
     string result = AutoSub.ProcessAutosubs( weekId );
     StatGrabber.StatGrabber sg = new StatGrabber.StatGrabber();
     result += sg.UpdateAveragesAndScores( db, selectedDate );
     return result;
 }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Database objDB = new SqlDatabase(M_CONSTR);
                if (objDB != null)
                {
                    //所属部门
                    DataSet ds = objDB.ExecuteDataSet(CommandType.Text, "SELECT * FROM Table_Group");
                    ddlTGroup.DataSource = ds.Tables[0];
                    ddlTGroup.DataTextField = "GroupName";
                    ddlTGroup.DataValueField = "GroupID";
                    ddlTGroup.DataBind();
                    ddlTGroup.Items.Insert(0, (new ListItem("--全部--", "0")));
                    ddlTGroup.SelectedIndex = 0;

                    //讲师
                    ds = objDB.ExecuteDataSet(CommandType.Text, "SELECT * FROM Table_TEACHER");
                    ddlTeacher.DataSource = ds.Tables[0];
                    ddlTeacher.DataTextField = "TeacherName";
                    ddlTeacher.DataValueField = "TeacherID";
                    ddlTeacher.DataBind();
                    //ddlTeacher.Items.Insert(0, (new ListItem("--全部--", "0")));
                    //ddlTeacher.SelectedIndex = 0;
                }
            }
        }

		public void DoLotsOfConnectionFailures()
		{
			int numberOfEvents = 50;
			using (WmiEventWatcher eventListener = new WmiEventWatcher(numberOfEvents))
			{
				SqlDatabase db = new SqlDatabase("BadConnectionString");
				DataInstrumentationListener listener = new DataInstrumentationListener("foo", true, true, true);
				DataInstrumentationListenerBinder binder = new DataInstrumentationListenerBinder();
				binder.Bind(db.GetInstrumentationEventProvider(), listener);

				for (int i = 0; i < numberOfEvents; i++)
				{
					try
					{
						db.ExecuteScalar(CommandType.Text, "Select count(*) from Region");
					}
					catch { }
				}

				eventListener.WaitForEvents();
				
				Assert.AreEqual(numberOfEvents, eventListener.EventsReceived.Count);
                Assert.AreEqual("ConnectionFailedEvent", eventListener.EventsReceived[0].ClassPath.ClassName);
				Assert.AreEqual("foo", eventListener.EventsReceived[0].GetPropertyValue("InstanceName"));
				Assert.AreEqual(db.ConnectionStringWithoutCredentials, eventListener.EventsReceived[0].GetPropertyValue("ConnectionString"));
			}
		}

 public static IDataReader Departments()
 {
     SqlDatabase db = new SqlDatabase(connString);
     DbCommand command = db.GetSqlStringCommand("SELECT DeptId, Name FROM AllDepartments D WHERE D.Active=1 AND D.NormBillsTime=1 ORDER BY Name");
     command.CommandType = CommandType.Text;
     return db.ExecuteReader(command);
 }

        public IDataReader ToDataReader(string storedProcedureName, Dictionary<string, string> parameters)
        {
            using (SqlCommand cmd = new SqlCommand(storedProcedureName))
            {
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (string key in parameters.Keys)
                {
                    SqlParameter parameter = new SqlParameter(key, parameters[key]);

                    if (parameter.Value == null)
                        parameter.Value = DBNull.Value;

                    if (parameter.SqlDbType == SqlDbType.DateTime && parameter.Value is DateTime && (DateTime)parameter.Value == default(DateTime))
                        parameter.Value = SqlDateTime.MinValue.Value;

                    cmd.Parameters.Add(parameter);

                }

                SqlDatabase database = new SqlDatabase(ConfigurationManager.ConnectionStrings["MyAppsLocal"].ConnectionString);

                try
                {
                    IDataReader reader = database.ExecuteReader(cmd);
                    return reader;
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                    return null;
                }

            }
        }

        public static DataTable GetResources(int jobId, int deptId)
        {
            SqlDatabase db = new SqlDatabase(connString);
            string sql = @" SELECT	U.UserId, U.FirstName + ' ' + U.LastName AS FullName, 
		                            t.TitleName as Title
                            FROM	AllocableUsers U LEFT JOIN JobTitles AS t ON U.currentTitleID=t.TitleID
                            WHERE	U.Active=1 AND U.UserId NOT IN (
                                    SELECT UserId FROM Assignments WHERE [email protected]_id
                                    AND (EndDate IS NULL OR EndDate>DATEADD(s, 1, CURRENT_TIMESTAMP))) 
                                    AND [email protected]_id AND realPerson='Y'
		                            AND UserId NOT IN (
			                            SELECT	UserId
			                            FROM	timeEntry
			                            WHERE	[email protected]_id AND UserId=U.UserId AND (TimeSpan IS NULL OR TimeSpan > 0)
		                            )
                            ORDER BY FullName";

            DbCommand command = db.GetSqlStringCommand(sql);
            db.AddInParameter(command, "@job_id", DbType.Int32, jobId);
            db.AddInParameter(command, "@dept_id", DbType.Int32, deptId);
            DataTable t = new DataTable();
            t = db.ExecuteDataSet(command).Tables[0].Copy();
            t.TableName = "Resources";
            command.Dispose();
            return t;
        }

 public static IDataReader GetIDataReader(string connectionString, string sqlQuery)
 {
     SqlDatabase sqlServerDB = new SqlDatabase(connectionString);
     DbCommand cmd = sqlServerDB.GetSqlStringCommand(sqlQuery);
     //return an IDataReader.
     return sqlServerDB.ExecuteReader(cmd);
 }

        //fetch record
        public static DataTable GetImportedRecords(int? top = 1000, bool direction = true, string name = "",
             bool? gender = null, int? year = null, long? rank = null)
        {
            Database objDB = new SqlDatabase(ConfigurationManager.ConnectionStrings["DBaseConnectionString"].ConnectionString);
            DataSet _ds = new DataSet();
            using (DbCommand objCMD = objDB.GetStoredProcCommand("PSP_Babies_Get"))
            {
                objDB.AddInParameter(objCMD, "@Top",
                                     DbType.Int32, top??1000000);
                objDB.AddInParameter(objCMD, "@SortingDirection",
                                     DbType.String, direction.ToIndicator());

                objDB.AddInParameter(objCMD, "@Name",
                                     DbType.String, name);
                objDB.AddInParameter(objCMD, "@Gender",
                                     DbType.String, gender.ToIndicator());
                objDB.AddInParameter(objCMD, "@Year",
                                     DbType.Int32, year);
                objDB.AddInParameter(objCMD, "@Rank",
                                     DbType.Int64, rank);

                try
                {
                    _ds = objDB.ExecuteDataSet(objCMD);
                    return _ds != null ? _ds.Tables[0] : new DataTable();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        public static void SaveBaby(string babyName, long position, bool gender, int year, long rank)
        {
            Database objDB = new SqlDatabase(ConfigurationManager.ConnectionStrings["DBaseConnectionString"].ConnectionString);
            using (DbCommand objCMD = objDB.GetStoredProcCommand("PSP_Babies_Save"))
            {
                objDB.AddInParameter(objCMD, "@Name",
                                     DbType.String, babyName);
                objDB.AddInParameter(objCMD, "@Gender",
                                     DbType.String, gender.ToIndicator());
                objDB.AddInParameter(objCMD, "@Position",
                                    DbType.Int64, position);
                objDB.AddInParameter(objCMD, "@Rank",
                                     DbType.Int64, rank);
                objDB.AddInParameter(objCMD, "@Year",
                                    DbType.Int32, year);

                //objDB.AddOutParameter(objCMD, "@strMessage", DbType.String, 255);

                try
                {
                    objDB.ExecuteNonQuery(objCMD);
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        public int addNewProductPost(string userid, int l1id, int l2id, int l3id, string productname, string keywords, string description, decimal quantity, decimal price, string image1,
            string image2, string image3, string image4)
        {
            int status = 0;
            try
            {

                Guid guid = new Guid(userid);
                Database objDB = new SqlDatabase(connectionStr);
                DbCommand objAdd = new SqlCommand();
                objAdd.CommandType = CommandType.StoredProcedure;
                objAdd.CommandText = "InsertProductPost";
                objDB.AddInParameter(objAdd, "@USERID", DbType.Guid, guid);
                objDB.AddInParameter(objAdd, "@L1ID", DbType.Int32, l1id);
                objDB.AddInParameter(objAdd, "@L2ID", DbType.Int32, l2id);
                objDB.AddInParameter(objAdd, "@L3ID", DbType.Int32, l3id);
                objDB.AddInParameter(objAdd, "@PRODUCTNAME", DbType.String, productname);
                objDB.AddInParameter(objAdd, "@DESCRIPTION", DbType.String, description);
                objDB.AddInParameter(objAdd, "@QUANTITY", DbType.Decimal, quantity);
                objDB.AddInParameter(objAdd, "@PRICE", DbType.Decimal, price);
                objDB.AddInParameter(objAdd, "@KEYWORDS", DbType.String, keywords);
                objDB.AddInParameter(objAdd, "@IMAGE1", DbType.String, image1);
                objDB.AddInParameter(objAdd, "@IMAGE2", DbType.String, image2);
                objDB.AddInParameter(objAdd, "@IMAGE3", DbType.String, image3);
                objDB.AddInParameter(objAdd, "@IMAGE4", DbType.String, image4);
                objDB.AddOutParameter(objAdd, "@Stat", DbType.Int16, 16);
                objDB.ExecuteNonQuery(objAdd);
                status = Convert.ToInt16(objDB.GetParameterValue(objAdd, "@Stat"));
            }
            catch (Exception ex)
            {
                objErr.GeneralExceptionHandling(ex, "Website - Post Product", "addNewProductPost", "GENERAL EXCEPTION");
            }
            return status;
        }

        public void NoEventBroadcastIfNoEventRegistered()
        {
            string connectionString = @"server=(local)\sqlexpress;database=northwind;integrated security=true;";
            SqlDatabase db = new SqlDatabase(connectionString);

            db.ExecuteNonQuery(CommandType.Text, "Select count(*) from Region");
        }

        public static bool BookTicketDAL(Models.TicketBooking data)
        {
            SqlDatabase travelMSysDB = new SqlDatabase(ConnString.DBConnectionString);

            SqlCommand insertCmmnd = new SqlCommand("INSERT INTO TICKET_BOOKINGS ([Travel_Request_ID],[Ticket_Details],[Booking_Status]) VALUES (@Travel_Request_ID,@Ticket_Details,@Booking_Status)");
            insertCmmnd.CommandType = CommandType.Text;

            insertCmmnd.Parameters.AddWithValue("@Travel_Request_ID", data.Travel_Request_ID);
            insertCmmnd.Parameters.AddWithValue("@Ticket_Details", data.Ticket_Details);
            insertCmmnd.Parameters.AddWithValue("@Booking_Status", data.Booking_Status);

            int rowsAffected = travelMSysDB.ExecuteNonQuery(insertCmmnd);
            Console.Write("rowsAffected " + rowsAffected);

            SqlCommand updateCmmnd = new SqlCommand("UPDATE TRAVEL_REQUESTS SET [Request_Status]='P' WHERE [email protected]_Request_ID");
            updateCmmnd.CommandType = CommandType.Text;

            updateCmmnd.Parameters.AddWithValue("@Travel_Request_ID", data.Travel_Request_ID);

            int rowsAffectedTReq = travelMSysDB.ExecuteNonQuery(updateCmmnd);

            //two booking records for same travel req issue to be resolved wherever applicable - or just delete the rows when booking cancelled
            if (rowsAffected == 1&&rowsAffectedTReq==1)
                return true;
            return false;
        }

        public void PublicarMensajeSql(string aplicacion, string error, Exception excepcion)
        {
            try
            {
                SqlDatabase baseDedatos = new SqlDatabase(ConfigurationManager.ConnectionStrings["AccesoDual"].ConnectionString);
                DbCommand comando = baseDedatos.GetStoredProcCommand("adm.NlayerSP_RegistrarErrorAplicativo");

                comando.CommandType = CommandType.StoredProcedure;

                string interna = null;

                if (excepcion.InnerException != null)
                {
                    interna = excepcion.InnerException.Message;
                }

                baseDedatos.AddInParameter(comando, "Aplicacion", SqlDbType.NVarChar, aplicacion);
                baseDedatos.AddInParameter(comando, "Error", SqlDbType.NVarChar, error);
                baseDedatos.AddInParameter(comando, "Excepcion", SqlDbType.NText, excepcion.Message);
                baseDedatos.AddInParameter(comando, "Interna", SqlDbType.NText, interna);

                baseDedatos.ExecuteNonQuery(comando);
            }
            catch {}
        }

 /// <summary>
 /// 
 /// </summary>
 /// <param name="jobId"></param>
 /// <param name="userId"></param>
 /// <param name="existing"></param>
 /// <returns>0 if user being unassigned has never billed time to the job, 
 /// 1 if user has billed time to the job</returns>
 public static int Unassign(int jobId, int userId, string existing)
 {
     SqlDatabase db = new SqlDatabase(connString);
     DbCommand cmd = null;
     if (existing == "delete")
     {
         cmd = db.GetSqlStringCommand(@" DELETE FROM Allocations WHERE [email protected]_id AND [email protected]_id");
         db.AddInParameter(cmd, "@user_id", DbType.Int32, userId);
         db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
         db.ExecuteNonQuery(cmd);
     }
     else if (existing == "move")
     {
         MoveAllocations(userId, jobId);
     }
     int retval = 0;
     
     cmd = db.GetStoredProcCommand("ALOC_Unassign");
     db.AddInParameter(cmd, "@job_id", DbType.Int32, jobId);
     db.AddInParameter(cmd, "@user_id", DbType.Int32, userId);
     object result = db.ExecuteScalar(cmd);
     if (result != null && result != DBNull.Value)
         retval = Convert.ToInt32(result);
     cmd.Dispose();
     return retval;
 }

            public static UserAuthResult Authenticate(string userName, string password, string providerKey)
            {
                string Auth_GetUserByCredentials =
                @"SELECT u.ID,u.Name,u.Surname,u.Email,u.Password,u.About,u.BirthDate,u.DateCreated,u.LastLogin,u.DateUpdated,ul.LoginProvider 
                FROM User AS u
                INNER JOIN UserLogin AS ul 
                ON u.ID = ul.UserID
                WHERE  u.Email = '{1}'

                WHERE  ul.Providerkey = '{1}'";

                string connStr = ConfigurationManager.AppSettings["MasterSQLConnection"];
                SqlDatabase db = new SqlDatabase(connStr);
                UserAuthResult result = new UserAuthResult();
                result.AuthSuccess = false;
                User user = new User();
                string dbPassword = string.Empty;
                try
                {
                    string query = String.Format(Auth_GetUserByCredentials, userName);
                    using (DbCommand command = db.GetSqlStringCommand(query))
                    {

                        using (IDataReader reader = db.ExecuteReader(command))
                        {
                            if (reader.Read())
                            {
                                //Users.ID,Users.Name,Surname,IsAdmin,IsSuperAdmin,LoginType,Users.ActiveDirectoryDomain,Password
                                user.ID       = int.Parse(reader["ID"].ToString());
                                user.Password = reader["Password"].ToString();
                                user.Name     = reader["Name"].ToString();
                                user.Surname  = reader["Surname"].ToString();
                            }
                            else
                            {
                                result.AuthSuccess = false;
                                result.ErrorMsg = "Username or password is wrong";
                            }
                        }
                    }
                }
                finally
                {
                }


                if (!string.IsNullOrEmpty(password) && user.ID > 0 && password.Equals(user.Password))
                {
                    result.User = user;
                    result.AuthSuccess = true;
                }
                else
                {
                    result.ErrorMsg = "Username or password is wrong";
                }

                return result;

            }

        public static IDataReader Employees(int deptId)
        {
            SqlDatabase db = new SqlDatabase(connString);
            DbCommand command = db.GetSqlStringCommand("SELECT FirstName + ' ' + LastName AS FullName, UserId FROM AllocableUsers WHERE DeptId=" + deptId + " AND Active=1 ORDER BY FullName");
            command.CommandType = CommandType.Text;

            return db.ExecuteReader(command);
        }