private static List<KeyInfo> GetKeysInternal(Config config, String connectionString, String keyType)
        {
            Dictionary<String, KeyInfo> keys = new Dictionary<string, KeyInfo>();

            using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectionString))
            {
                conn.Open();
                System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(String.Format("select s.name, o.name, i.name from sys.objects o join sys.indexes i on i.object_id = o.object_id join sys.schemas s on s.schema_id = o.schema_id where i.type_desc = '{0}' and i.is_primary_key = 1 and o.type = 'U' order by 1", keyType), conn);
                using (System.Data.SqlClient.SqlDataReader r = cmd.ExecuteReader())
                {
                    while (r.Read())
                    {
                        String schemaName = r[0].ToString();
                        String tableName = r[1].ToString();
                        String keyName = r[2].ToString();
                        keys.Add((schemaName + tableName).ToLower(), new KeyInfo() { Name = keyName, SchemaName = schemaName, TableName = tableName });
                    }
                }
            }

            Dictionary<String, List<Entity>> schemas = config.EntitiesBySchema;
            List<KeyInfo> result = new List<KeyInfo>();
            foreach (KeyValuePair<String, KeyInfo> kvp in keys)
            {
                if (schemas.ContainsKey(kvp.Value.SchemaName))
                    result.Add(kvp.Value);
            }

            return result;
        }

        public static blc.Grupo obtenerGrupo(string nombreGrupo, string codigoGrupo)
        {
            blc.Grupo grupo = new blc.Grupo();
            System.Data.SqlClient.SqlCommand Comando;
            System.Data.SqlClient.SqlDataReader DataReader;
            string query = "SELECT * FROM Grupo WHERE Nombre = '" + nombreGrupo + "' OR Codigo = '" + codigoGrupo + "'";

            Comando = new System.Data.SqlClient.SqlCommand(query);
            Comando.Connection = Program.dt.Connecion;
            Comando.Connection.Open();
            DataReader = Comando.ExecuteReader();

            while (DataReader.Read())
            {
                grupo.Codigo = (int)DataReader.GetValue(0);
                grupo.Nombre = DataReader.GetValue(1).ToString();
                grupo.PermisoGestion = DataReader.GetValue(2).ToString();
                grupo.PermisoCasos = DataReader.GetValue(3).ToString();
                grupo.PermisoClientes = DataReader.GetValue(4).ToString();
                grupo.PermisoTestigos = DataReader.GetValue(5).ToString();
                grupo.PermisoInventario = DataReader.GetValue(6).ToString();
                grupo.PermisoEvidencia = DataReader.GetValue(7).ToString();
                grupo.PermisoReportes = DataReader.GetValue(8).ToString();
                grupo.PermisoConfiguracion = DataReader.GetValue(9).ToString();
            }

            DataReader.Close();
            Comando.Connection.Close();
            return grupo;
        }

 public Home()
 {
     this.WebConfig = System.Configuration.ConfigurationManager.AppSettings["Setting"];
     this.Build = System.Configuration.ConfigurationManager.AppSettings["Build"];
     this.Version = System.Reflection.Assembly.GetExecutingAssembly().GetName().Version.ToString(4);
     bool executeDB = false;
     #if AZURE
     this.WebRoleConfig = Microsoft.WindowsAzure.ServiceRuntime.RoleEnvironment.GetConfigurationSettingValue("Environment");
     executeDB = Convert.ToBoolean(Microsoft.WindowsAzure.ServiceRuntime.RoleEnvironment.GetConfigurationSettingValue("executeDB"));
     #else
     executeDB = Convert.ToBoolean(System.Configuration.ConfigurationManager.AppSettings["ExecuteDB"]);
     #endif
     string conn;
     #if AZURE
     conn = Microsoft.WindowsAzure.ServiceRuntime.RoleEnvironment.GetConfigurationSettingValue("DBConn");
     #else
     conn = System.Configuration.ConfigurationManager.ConnectionStrings["DBConn"].ToString(); ;
     #endif
     if (executeDB)
     {
         var cn = new System.Data.SqlClient.SqlConnection(conn);
         cn.Open();
         var cmd = new System.Data.SqlClient.SqlCommand("SELECT TOP 1 Col1 FROM Items", cn);
         this.DatabaseValue = cmd.ExecuteScalar().ToString();
         cn.Close();
     }
 }

    private System.Data.DataRow GetParameter(string IDParametro, int? IDPortal, int? IDSistema, string IDUsuario)
    {
      // Aca se lee la informacion de la base de datos
      // y se preparan los layers
      string connStr = ValidacionSeguridad.Instance.GetSecurityConnectionString();
      System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connStr);
      conn.Open();

      System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand
        ("SELECT * FROM dbo.SF_VALOR_PARAMETRO(@IDParametro, @IDPortal, @IDSistema, @IDUsuario)", conn);

      System.Data.SqlClient.SqlParameter prm = new System.Data.SqlClient.SqlParameter("@IDParametro", System.Data.SqlDbType.VarChar, 100);
      prm.Value = IDParametro;
      cmd.Parameters.Add(prm);

      prm = new System.Data.SqlClient.SqlParameter("@IDPortal", System.Data.SqlDbType.Int);
      if (IDPortal.HasValue)
      {
        prm.Value = IDPortal.Value;
      }
      else
      {
        prm.Value = null;
      }
      cmd.Parameters.Add(prm);

      prm = new System.Data.SqlClient.SqlParameter("@IDSistema", System.Data.SqlDbType.Int);
      if (IDSistema.HasValue)
      {
        prm.Value = IDSistema.Value;
      }
      else
      {
        prm.Value = null;
      }
      cmd.Parameters.Add(prm);

      prm = new System.Data.SqlClient.SqlParameter("@IDUsuario", System.Data.SqlDbType.VarChar);
      if (IDUsuario != null)
      {
        prm.Value = IDUsuario;
      }
      else
      {
        prm.Value = null;
      }
      cmd.Parameters.Add(prm);

      //     IdParametro, Alcance, ValorTexto, ValorEntero, ValorDecimal, ValorLogico, ValorFechaHora
      cmd.CommandType = System.Data.CommandType.Text;
      System.Data.SqlClient.SqlDataAdapter da = new System.Data.SqlClient.SqlDataAdapter(cmd);

      System.Data.DataSet ds = new System.Data.DataSet();
      da.Fill(ds);

      conn.Close();

      return ds.Tables[0].Rows[0];
      //return resultado;
    }

        public static string[] GetGroupUsers(string intranetGroup)
        {
            List<string> result = new List<string>();
            try
            {
                
                using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connString))
                {
                    using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(@"SELECT employee.employee_name FROM groups INNER JOIN employee2group ON 
                                                                                                        groups.group_id=employee2group.group_id INNER JOIN
                                                                                                        employee ON employee2group.employee_id=employee.employee_id
                                                                                                        WHERE [email protected] ORDER BY employee.employee_name", conn))
                    {
                        try
                        {
                            conn.Open();
                            cmd.Parameters.AddWithValue("@name",intranetGroup);
                            System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();
                            while(reader.Read())
                                result.Add(reader.GetString(0));
                        }
                        catch (Exception ex)
                        {

                        }
                    }
                }
                return result.ToArray();
            }
            catch (Exception ex)
            {
                AuthLib.Core.Logging.WriteEntry("Ошибка получения списка пользователей в роли.",ex);
                throw new Exception("Ошибка получения списка пользователей в роли.", ex);
            }
        }

        private void FillDataSet()
        {

            //1. Make a Connection
            System.Data.SqlClient.SqlConnection objCon;
            objCon = new System.Data.SqlClient.SqlConnection();
            objCon.ConnectionString = @"Data Source=(localDB)\v11.0;Initial Catalog = EmployeeProjects; Integrated Security=True;";
            objCon.Open();

            //2. Issue a Command
            System.Data.SqlClient.SqlCommand objCmd;
            objCmd = new System.Data.SqlClient.SqlCommand();
            objCmd.Connection = objCon;
            objCmd.CommandType = CommandType.StoredProcedure;
            objCmd.CommandText = @"pSelEmployeeProjectHours";

            //3. Process the Results
            System.Data.DataSet objDS = new DataSet();
            System.Data.SqlClient.SqlDataAdapter objDA;
            objDA = new System.Data.SqlClient.SqlDataAdapter();
            objDA.SelectCommand = objCmd;
            objDA.Fill(objDS); // objCon.Open() is not needed!
            dataGridView1.DataSource = objDS.Tables[0];

            //4. Clean up code
            objCon.Close();
            dataGridView1.Refresh();
        }

        public static System.Data.SqlClient.SqlCommand ByBuyer(Guid receipt_id, out string display_member, out string value_member)
        {
            display_member = "UserFullName";
            value_member = "Spent";

            System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
            string where = "";
            if (receipt_id != Guid.Empty)
            {
                where = " WHERE rc.ReceiptID = @ReceiptID\n";
                cmd.Parameters.AddWithValue("@ReceiptID", receipt_id);
            }
            string query = "SELECT u.Surname + ' ' + SUBSTRING(u.Name, 1,1) + '. ' +\n" +
                           "       SUBSTRING(u.SecondName, 1,1) + '.' AS UserFullName,\n" +
                           "       SUM(rc.Price * (1.0 - rc.Discount) * rc.Amount) AS Spent\n" +
                           "  FROM Purchases.ReceiptContents AS rc\n" +
                           "  JOIN Persons.Users AS u\n" +
                           "    ON u.UserID = rc.Buyer\n" +
                           where +
                           " GROUP BY u.Surname + ' ' + SUBSTRING(u.Name, 1,1) + '. ' +\n" +
                           "       SUBSTRING(u.SecondName, 1,1) + '.'\n" +
                           " ORDER BY 2 DESC";
            cmd.CommandTimeout = 0;
            cmd.CommandType = System.Data.CommandType.Text;
            cmd.CommandText = query;
            return cmd;
        }

 // Занесение новой записи в БД
 public static bool Insert(System.Data.SqlClient.SqlConnection connection, System.Data.DataRow row, out string message)
 {
     bool done = false;
     message = "";
     try{
         connection.Open();
         System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
         string sQuery = "INSERT INTO " + Maker.Table + "\n" +
                         "       (MakerID, Name, MakerCategory, Vendor, Address, Created, Updated)\n" +
                         "VALUES (@MakerID, @Name, @MakerCategory, @Vendor, @Address, GETDATE(), GETDATE())";
         cmd.Parameters.AddWithValue("@MakerID", row["MakerID"]);
         cmd.Parameters.AddWithValue("@Name", row["Name"]);
         cmd.Parameters.AddWithValue("@MakerCategory", row["MakerCategory"]);
         cmd.Parameters.AddWithValue("@Vendor", row["Vendor"]);
         cmd.Parameters.AddWithValue("@Address", row["Address"]);
         //cmd.Parameters.AddWithValue("@Created", row["Created"]);
         //cmd.Parameters.AddWithValue("@Updated", row["Updated"]);
         cmd.Connection = connection;
         cmd.CommandTimeout = 0;
         cmd.CommandType = System.Data.CommandType.Text;
         cmd.CommandText = sQuery;
         cmd.ExecuteNonQuery();
         connection.Close();
         done = true;
     }catch (System.Exception ex){
         message = ex.Message;
     }finally{
         if (connection.State == System.Data.ConnectionState.Open) connection.Close();
     }
     return done;
 }

 private void CbBackups_DropDown_1(object sender, EventArgs e)
 {
     using (var connection = new System.Data.SqlClient.SqlConnection(string.Format("Server={0};Database={1};Trusted_Connection=True;", TxtLocalBkp.Text, TxtDataBase.Text)))
     {
         connection.Open();
         using (var command = new System.Data.SqlClient.SqlCommand(
             "SELECT physical_device_name FROM msdb.dbo.backupmediafamily " +
             "INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id " +
             "WHERE (msdb.dbo.backupset.database_name LIKE @DatabaseName)", connection))
         {
             command.Parameters.AddWithValue("DatabaseName", TxtDataBase.Text);
             using (var reader = command.ExecuteReader())
             {
                 var table = new DataTable();
                 table.Load(reader);
                 table.Columns.Add("FriendlyName");
                 foreach (DataRow row in table.Rows)
                 {
                     row["FriendlyName"] = System.IO.Path.GetFileName(row["physical_device_name"].ToString());
                 }
                 if (CbBackups.DataSource != null && CbBackups.DataSource is DataTable)
                 {
                     var oldTable = ((DataTable)CbBackups.DataSource);
                     CbBackups.DataSource = null;
                     oldTable.Dispose();
                 }
                 CbBackups.DataSource = table;
                 CbBackups.DisplayMember = "FriendlyName";
                 CbBackups.ValueMember = "physical_device_name";
             }
         }
     }
 }

 private void InitClass(int id, int groupID)
 {
     this.RoleID = id;
     this.GroupID = groupID;
     using (System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[Configuration.ConnectionStringName].ConnectionString))
     {
         using (System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("SELECT * FROM Authorization_RoleToControlGroup WHERE [email protected] AND [email protected]", conn))
         {
             try
             {
                 conn.Open();
                 cmd.Parameters.AddWithValue("@role", id);
                 cmd.Parameters.AddWithValue("@group", groupID);
                 System.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader();
                 while (reader.Read())
                     this.id = reader.GetInt32(0);
             }
             catch (Exception ex)
             {
                 if (Configuration.s_log != null)
                     Configuration.s_log.Error("[Ошибка модуля авторизации] [Инициализация класса роли группы элементов управления] ", ex);
             }
         }
     }
 }

        public void crearRol(string nombre, List<string> permisos)
        {
            StringBuilder getLastRoleId = new StringBuilder().AppendFormat("SELECT R.RoleId FROM TRANSA_SQL.Role R ORDER BY R.RoleId DESC", nombre);
            int roleId = (int)(Conexion.Instance.ejecutarQuery(getLastRoleId.ToString()).Rows[0][0]) + 1;

            StringBuilder sentence1 = new StringBuilder().AppendFormat("INSERT INTO TRANSA_SQL.Role(RoleId, Name, Enabled) VALUES ({0},'{1}',2)",roleId, nombre);
            Conexion.Instance.ejecutarQuery(sentence1.ToString());

            System.Data.SqlClient.SqlCommand comando1 = new System.Data.SqlClient.SqlCommand();
            comando1.CommandType = CommandType.StoredProcedure;

            comando1.Parameters.Add("@RoleId", SqlDbType.Int);
            comando1.Parameters.Add("@PermissionId", SqlDbType.Int);

            comando1.Parameters[0].Value = roleId;

            StringBuilder sentence4;
            comando1.CommandText = "TRANSA_SQL.agregarRolePermission";

            foreach (string permiso in permisos)
            {
                sentence4 = new StringBuilder().AppendFormat("SELECT P.PermissionId FROM TRANSA_SQL.Permission P WHERE P.Name='{0}'", permiso);
                comando1.Parameters[1].Value = (int)Conexion.Instance.ejecutarQuery(sentence4.ToString()).Rows[0][0];
                Conexion.Instance.ejecutarQueryConSP(comando1);
            }
        }

        private void btnBackup_Click(object sender, EventArgs e)
        {
            if (folderBrowserDialog1.ShowDialog() == DialogResult.OK)
            {
                try
                {
                    string path = String.Format("{0}\\{1}PharmaInventory{2:MMMddyyyy}.bak", folderBrowserDialog1.SelectedPath, GeneralInfo.Current.HospitalName, DateTimeHelper.ServerDateTime);

                    string connectionString = readApp.GetValue("dbConnection", typeof(string)).ToString();

                    System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectionString);
                    System.Data.SqlClient.SqlCommand com = new System.Data.SqlClient.SqlCommand();

                    if (!(conn.State == ConnectionState.Open))
                        conn.Open();
                    string dbName = conn.Database;
                    com.CommandText = "BACKUP DATABASE [" + dbName + "] TO  DISK = N'" + path + "' WITH NOFORMAT, NOINIT,  NAME = N'PharmaInventory" + DateTimeHelper.ServerDateTime.ToString("MMMddyyyy") + "-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10";
                    com.Connection = conn;
                    com.ExecuteNonQuery();

                    GeneralInfo.Current.LastBackUp = DateTimeHelper.ServerDateTime;
                    GeneralInfo.Current.Save();
                    MessageBox.Show(@"Backup completed to " + path + @"!", @"Completed", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                catch
                {
                    MessageBox.Show(@"Backup has failed! Please Try Again.",@"Try Again",MessageBoxButtons.OK,MessageBoxIcon.Error);
                }
            }
        }

        protected void ExecutionConfirmed(object sender, EventArgs e)
        {
            panelMain.Visible = true;
            confirmPanel.Visible = false;

            System.ServiceProcess.ServiceController sc = new System.ServiceProcess.ServiceController("EOBProcessing");

            try
            {
                sc.Stop();
                System.Threading.Thread.Sleep(5000);

                dbProcedures db = new dbProcedures();
                System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand("usp_AddOneTimeRunSchedule", new db().SqlConnection);
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;

                sqlCmd.ExecuteNonQuery();
                db.Close();

                sc.Start();
                lblMessage.Text = "EOB System manual execution started.";

            }
            catch (Exception e1)
            {
                lblMessage.Text = "Failed to manually start EOB System service." + e1.Message();
            }
        }

        public static void Simulate车辆作业(车辆作业 车辆作业)
        {
            if (!车辆作业.Track.HasValue)
                System.Console.WriteLine("There is no track.");

            var sql = new System.Data.SqlClient.SqlCommand("DELETE FROM 业务作业_监控状态 WHERE 车辆作业 = @车辆作业");
            sql.Parameters.AddWithValue("@车辆作业", 车辆作业.Id);
            Feng.Data.DbHelper.Instance.ExecuteNonQuery(sql);

            sql = new System.Data.SqlClient.SqlCommand("DELETE FROM 业务作业_作业异常情况 WHERE 车辆作业 = @车辆作业");
            sql.Parameters.AddWithValue("@车辆作业", 车辆作业.Id);
            Feng.Data.DbHelper.Instance.ExecuteNonQuery(sql);

            var trackId = 车辆作业.Track.Value;

            DBDataBuffer.Instance.LoadData();
            //NameValueMappingCollection.Instance.Reload();

            SimulateTrack(trackId, (trackPoint) =>
                {
                    using (IRepository rep = ServiceProvider.GetService<IRepositoryFactory>().GenerateRepository<车辆作业>())
                    {
                        bool b = m_作业监控Dao.更新作业监控状态(rep, 车辆作业, trackPoint);
                    }
                });
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            if (Jenzabar.Portal.Framework.PortalUser.Current.HostID != null)
            {
                //**************************************************
                // if the logged in user has an ID, check for Time
                //**************************************************

                System.Data.SqlClient.SqlCommand sqlcmdSelectRegistrationTime = new System.Data.SqlClient.SqlCommand(
                    "SELECT ADD_BEG_DTE FROM TW_TIME_CTRL WHERE"
                    + " YR_CDE = (SELECT CURR_YR FROM CUST_INTRFC_CNTRL WHERE INTRFC_TYPE = 'REGTIME_PORTLET') AND TRM_CDE = (SELECT CURR_TRM FROM CUST_INTRFC_CNTRL WHERE INTRFC_TYPE = 'REGTIME_PORTLET') AND"
                    + " TEL_WEB_GRP_CDE = (SELECT TEL_WEB_GRP_CDE FROM STUDENT_MASTER WHERE ID_NUM = '" + Jenzabar.Portal.Framework.PortalUser.Current.HostID + "')",
                    new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["JenzabarConnectionString"].ConnectionString));

                try
                {
                    sqlcmdSelectRegistrationTime.Connection.Open();
                    System.Data.SqlClient.SqlDataReader sqlReader = sqlcmdSelectRegistrationTime.ExecuteReader();

                    if (sqlReader.HasRows)
                    {
                        sqlReader.Read();
                        lblMsg.Text = "<b>" + (Convert.ToDateTime(sqlReader["ADD_BEG_DTE"].ToString())).ToString("dddd, MMMM dd @ h:mm tt") + "</b>.";
                        sqlReader.Close();
                        sqlcmdSelectRegistrationTime.Connection.Close();
                    }
                    else
                    {
                        this.ParentPortlet.ShowFeedback(Jenzabar.Portal.Framework.Web.UI.FeedbackType.Message, "Your registration time has not been set. Contact the Office of Registration & Records (830-372-8040) for more details.");
                    }

                    System.Data.SqlClient.SqlCommand sqlcmdSelectRegEndTime = new System.Data.SqlClient.SqlCommand("SELECT last_drop_add_dte FROM year_term_table WHERE YR_CDE = (SELECT CURR_YR FROM CUST_INTRFC_CNTRL WHERE INTRFC_TYPE = 'REGTIME_PORTLET') AND TRM_CDE = (SELECT CURR_TRM FROM CUST_INTRFC_CNTRL WHERE INTRFC_TYPE = 'REGTIME_PORTLET')", new System.Data.SqlClient.SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["JenzabarConnectionString"].ConnectionString));
                    sqlcmdSelectRegEndTime.Connection.Open();
                    sqlReader = sqlcmdSelectRegEndTime.ExecuteReader();

                    if (sqlReader.HasRows)
                    {
                        sqlReader.Read();
                        lblMessage.Text = "You can register at the time below or any time after through the last day to register, <b>" + (Convert.ToDateTime(sqlReader["LAST_DROP_ADD_DTE"].ToString())).ToString("dddd, MMMM dd @ h:mm tt") + "</b>.";
                    }
                }
                catch (Exception critical)
                {
                    lblError.ErrorMessage = "Error: Registration time(s) unavailable. <BR />" + critical.GetBaseException().Message;
                    lblError.Visible = true;
                }
                finally
                {
                    if (sqlcmdSelectRegistrationTime.Connection != null && sqlcmdSelectRegistrationTime.Connection.State == ConnectionState.Open)
                    {
                        sqlcmdSelectRegistrationTime.Connection.Close();
                    }
                }
            }
            else
            {
                ParentPortlet.ShowFeedbackGlobalized(Jenzabar.Portal.Framework.Web.UI.FeedbackType.Error, "MSG_NO_HOST_ID");
            }
            //lblMessage.Text = "If you need to make an change to your existing Spring schedule, the add/drop period will open beginning Monday, January 11th, at 8:00am, and will remain open until Friday, January 15th, at 5:00pm. If you did not preregister for your spring courses, you will need to register in-person in the Office of Registration and Records on the first floor of the Beck Center.";
        }

        public void InsertError(Exception err, int refId, bool sendMail)
        {
            string conn = System.Configuration.ConfigurationManager.ConnectionStrings["MainConnection"].ConnectionString;
            string queryString = "INSERT INTO [MLB].[dbo].[Error] ([RefId],[ErrorMethod],[ErrorText],[ErrorDate]) VALUES (@RefId,@ErrorMethod,@ErrorText,@ErrorDate)";

            using (System.Data.SqlClient.SqlConnection connection = new System.Data.SqlClient.SqlConnection(conn))
            {
                // Create the Command and Parameter objects.
                System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(queryString, connection);
                command.Parameters.AddWithValue("@RefId", refId);
                command.Parameters.AddWithValue("@ErrorMethod", err.Source);
                command.Parameters.AddWithValue("@ErrorText", err.InnerException);
                command.Parameters.AddWithValue("@ErrorDate", DateTime.Now);

                try
                {
                    connection.Open();
                    command.ExecuteNonQuery();
                }
                catch (Exception ex)
                { }
            }

            if (sendMail)
            {
                var email = EmailMessageFactory.GetErrorEmail(err);
                var result = EmailClient.SendEmail(email);
            }
        }

        private void DoallThetracking(string trackkey, string value)
        {
            System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection();
            conn.ConnectionString = "Server=devrystudentsp10.db.6077598.hostedresource.com;User Id=DeVryStudentSP10;Password=OidLZqBv4;";
            conn.Open();
            // Console.WriteLine(conn.State);
            System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand();
            comm.Connection = conn;

            string SQL = "insert into huber_tracker12 (usertrackerid, trackkey, value,trackwhen) values (NEWID(), @trackkey, @trackvalue, @trackwhen)";

            comm.CommandText = SQL;

            comm.Parameters.AddWithValue("@trackkey", trackkey);
            comm.Parameters.AddWithValue("@trackvalue", value);
            comm.Parameters.AddWithValue("@trackwhen", DateTime.Now);

            comm.ExecuteNonQuery();

            SQL = "delete from huber_tracker12 where usertrackerid not in (select top 300 usertrackerid from huber_tracker12 order by trackwhen desc)";
                comm.CommandText = SQL;
            comm.ExecuteNonQuery();

            conn.Close();
        }

        protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
        {
            String sqlstring;
               // sqlstring = "insert into sys_user values ((select max(sysuser_id)+1 from sys_user),'" +
               //     CreateUserWizard1.UserName + "','" + CreateUserWizard1.Password + "','1', GETDATE(), 3);";

            //SQL 2005 version
            sqlstring = "insert into sys_user select max(sysuser_id)+1, '" +
                CreateUserWizard1.UserName + "','" + CreateUserWizard1.Password +
                "', GETDATE(), (Select top 1 sysrole_id from sys_role where sysrole_name='Applicant') from sys_user ";

            // create a connection with sqldatabase
            //System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(
             //            " Data Source=SD;Initial Catalog=SRS;User ID=sa;Password=Ab123456;Connect Timeout=10;TrustServerCertificate=True ");
            System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(
                WebConfigurationManager.ConnectionStrings["SRSDB"].ConnectionString);
            // create a sql command which will user connection string and your select statement string
            System.Data.SqlClient.SqlCommand comm = new System.Data.SqlClient.SqlCommand(sqlstring, con);

            // create a sqldatabase reader which will execute the above command to get the values from sqldatabase
            try
            {
                con.Open();
                if (comm.ExecuteNonQuery() > 0)
                {
                }
            }
            finally
            {
                con.Close();
            }
        }

        public static blc.Caso obtenerCaso(string num)
        {
            blc.Caso caso = new blc.Caso();
            System.Data.SqlClient.SqlCommand Comando;
            System.Data.SqlClient.SqlDataReader DataReader;
            string query = "SELECT * FROM Caso WHERE numAccion = '" + num + "'";

            Comando = new System.Data.SqlClient.SqlCommand(query);
            Comando.Connection = Program.dt.Connecion;
            Comando.Connection.Open();
            DataReader = Comando.ExecuteReader();

            while (DataReader.Read())
            {
                caso.NumAccion = DataReader.GetValue(0).ToString();
                caso.Accion = DataReader.GetValue(1).ToString();
                caso.Materia = DataReader.GetValue(2).ToString();
                caso.Oficina = DataReader.GetValue(3).ToString();
                caso.Cliente.ID = DataReader.GetValue(4).ToString();
                caso.Observaciones = DataReader.GetValue(6).ToString();
            }
            DataReader.Close();
            Comando.Connection.Close();
            return caso;
        }

 public static bool Delete(System.Data.SqlClient.SqlConnection connection,
                           System.Data.SqlClient.SqlTransaction tran,
                           System.Data.DataRow row, out string message)
 {
     bool done = false;
     message = "";
     try
     {
         connection.Open();
         System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
         string sQuery = "DELETE FROM Producer.Products\n" +
                         " WHERE ProductID = @ProductID";
         cmd.Parameters.AddWithValue("@ProductID", row["ProductID"]);
         cmd.Connection = connection;
         if (tran != null) cmd.Transaction = tran;
         cmd.CommandTimeout = 0;
         cmd.CommandType = System.Data.CommandType.Text;
         cmd.CommandText = sQuery;
         cmd.ExecuteNonQuery();
         connection.Close();
         done = true;
     }
     catch (System.Exception ex)
     {
         message = ex.Message;
     }
     finally
     {
         if (connection.State == System.Data.ConnectionState.Open) connection.Close();
     }
     return done;
 }