C# (CSharp) Класс System.Data.SqlClient. SqlCommand Code Examples

C# (CSharp) System.Data.SqlClient.SqlCommand - 30 примеров найдено. Это лучшие примеры C# (CSharp) кода для класса System.Data.SqlClient.SqlCommand, полученные из open source проектов. Вы можете ставить оценку каждому примеру, чтобы помочь нам улучшить качество примеров.
Наследование: System.Data.Common.DbCommand, ICloneable
        public static DataTable Sehirler()
        {
            try
            {
                using (baglan = new SqlConnection(ConnectionString))
                {
                    baglan.Open();
                    using (komutver = new SqlCommand())
                    {
                        komutver.Connection = baglan;
                        komutver.CommandText = "sehir_Listele";
                        komutver.CommandType = CommandType.StoredProcedure;
                        adp = new SqlDataAdapter(komutver);
                        dt = new DataTable();
                        adp.Fill(dt);
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }

            return dt;
        }
Пример #2
1
        public static DataTable GetAllCidades(int estado_id)
        {
            DataTable retorno = new DataTable();
            StringBuilder SQL = new StringBuilder();
            SQL.Append(@"SELECT CidadeId, Nome FROM Cidade WHERE EstadoId = @ESTADO_ID");

            try
            {
                using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Mendes_varejo"].ConnectionString))
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand(SQL.ToString(), connection);
                    command.Parameters.AddWithValue("@ESTADO_ID", estado_id);
                    command.ExecuteNonQuery();
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    adapter.Fill(retorno);
                }
            }
            catch (Exception erro)
            {
                throw erro;
            }

            return retorno;
        }
Пример #3
0
 public List<Model.Beneficios> Select()
 {
     List<Model.Beneficios> lstBeneficios = new List<Model.Beneficios>();
     SqlConnection conexao = new SqlConnection(strCon);
     string sql = "Select * from Beneficios";
     SqlCommand cmd = new SqlCommand(sql, conexao);
     conexao.Open();
     try
     {
         SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
         while (reader.Read())
         {
             Model.Beneficios beneficios = new Model.Beneficios();
             beneficios.id = Convert.ToInt32(reader["id"].ToString());
             beneficios.descr = Convert.ToString(reader["descr"].ToString());
             lstBeneficios.Add(beneficios);
         }
     }
     catch
     {
         Console.WriteLine("Deu erro na Seleção de Beneficios...");
     }
     finally
     {
         conexao.Close();
     }
     return lstBeneficios;
 }
        private void DataPortal_Fetch(CategoryCriteria criteria)
        {
            bool cancel = false;
            OnFetching(criteria, ref cancel);
            if (cancel) return;

            string commandText = String.Format("SELECT [CategoryId], [Name], [Descn] FROM [dbo].[Category] {0}", ADOHelper.BuildWhereStatement(criteria.StateBag));
            using (var connection = new SqlConnection(ADOHelper.ConnectionString))
            {
                connection.Open();
                using (var command = new SqlCommand(commandText, connection))
                {
                    command.Parameters.AddRange(ADOHelper.SqlParameters(criteria.StateBag));
                    using(var reader = new SafeDataReader(command.ExecuteReader()))
                    {
                        if (reader.Read())
                            Map(reader);
                        else
                            throw new Exception(String.Format("The record was not found in 'dbo.Category' using the following criteria: {0}.", criteria));
                    }
                }
            }

            OnFetched();
        }
Пример #5
0
        public void GetScheduledAppointments()
        {
            var con = new SqlConnection("Data Source=CSDB;Initial Catalog=SEI_Ninja;Persist Security Info=True;UID=sei_timemachine;PWD=z5t9l3x0");



            string sql = @"SELECT e.eventID, e.eventName, e.eventLocation, et.eventDate, et.eventDuration, u.user_first_name + ' ' + u.user_last_name AS name
                             FROM [SEI_Ninja].[dbo].SCHEDULED_USERS su
                                  JOIN [SEI_Ninja].[dbo].EVENT_TIMES et ON (su.eventTimeID = et.eventTimeID)
                                  JOIN [SEI_TimeMachine2].[dbo].[USER] u ON (su.userID = u.user_id)
                                  JOIN [SEI_Ninja].[dbo].EVENT e ON (et.eventID = e.eventID)
                            WHERE e.eventOwner = 'mgeary'
                            ORDER BY e.eventID";
            using (var command = new SqlCommand(sql, con))
            {
                con.Open();
                using (var reader = command.ExecuteReader())
                {
                    var list = new List<ScheduledAppointment>();
                    while (reader.Read())
                        list.Add(new ScheduledAppointment
                        {
                            eventID = reader.GetInt32(0),
                            eventName = reader.GetString(1),
                            eventLocation = reader.GetString(2),
                            eventDate = reader.GetDateTime(3),
                            eventDuration = (float)reader.GetDouble(4),
                            eventUserName = reader.GetString(5)
                        });
                    allAppointments = list.ToArray();
                }
            }
        }
        private void ReadOrderData(string filterPrimkey, string connectionString, int rowIndex)
        {
            var queryString =
              "SELECT numcode, charcode, nominal, name, value FROM dbo.values_history inner join dbo.description on  dbo.description.id = dbo.values_history.id WHERE dbo.values_history.primkey ='" + filterPrimkey + "' ;";

            try
            {
                using (var connection =
                      new SqlConnection(connectionString))
                {
                    var command =
                        new SqlCommand(queryString, connection);
                    connection.Open();

                    var reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        WriteCurrency(reader, rowIndex);
                    }
                    reader.Close();
                }
            }
            catch (Exception ex)
            {
                Log.Text = ex.Message;
            }
        }
        public DataSet GetOne(string empresaid, tb_tipimpto BE)
        {
            using (SqlConnection cnx = new SqlConnection(conex.empConexion(empresaid)))
            {
                using (SqlCommand cmd = new SqlCommand("gspTbTipimpto_SELECT", cnx))
                {
                    DataSet ds = new DataSet();

                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@tipimptoid", SqlDbType.Char, 1).Value = BE.tipimptoid;
                    }

                    try
                    {
                        cnx.Open();
                        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                        {
                            da.Fill(ds);
                        }
                        return ds;
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }
                }
            }
        }
Пример #8
0
        public RootObjectOut GetMessageByUser(UserIn jm)
        {
            RootObjectOut output = new RootObjectOut();
            String jsonString = "";
            try
            {
                String strConnection = ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString;
                SqlConnection Connection = new SqlConnection(strConnection);
                String strSQL = string.Format("SELECT message FROM messages WHERE msgTo = '{0}' AND [msgID] = (SELECT MAX(msgID) FROM messages WHERE msgTo='{1}')", jm.user.ToString(),jm.user.ToString());
                SqlCommand Command = new SqlCommand(strSQL, Connection);
                Connection.Open();
                SqlDataReader Dr;
                Dr = Command.ExecuteReader();
                if (Dr.HasRows)
                {
                    if (Dr.Read())
                    {
                        jsonString = Dr.GetValue(0).ToString();
                    }
                }
                Dr.Close();
                Connection.Close();
            }
            catch (Exception ex)
            {
                output.errorMessage = ex.Message;
            }
            finally
            {
            }
            JavaScriptSerializer ser = new JavaScriptSerializer();
            output = ser.Deserialize<RootObjectOut>(jsonString);

            return output;
        }
Пример #9
0
        static void Main(string[] args)
        {
            Console.Write("Enter some text to search for it in the Products: ");
            var searchedProduct = Console.ReadLine();

            SqlConnection dbCon = new SqlConnection("Server=localhost; " +
                "Database=NORTHWND; Integrated Security=true");
            dbCon.Open();
            using (dbCon)
            {
                SqlCommand cmd = new SqlCommand(
                    "SELECT ProductName FROM Products " +
                    "WHERE CHARINDEX (@searchedProduct, ProductName)>0", dbCon);

                cmd.Parameters.AddWithValue("@searchedProduct", searchedProduct);

                SqlDataReader reader = cmd.ExecuteReader();
                using (reader)
                {
                    while (reader.Read())
                    {
                        string productName = (string)reader["ProductName"];
                        Console.WriteLine(productName);
                    }
                }
            }
        }
 private void repositoryItemButtonEditDelete_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
 {
     if (MessageBox.Show("هل انت متأكد؟", "تحزيــــر", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.No)
         return;
     DataRow Row = (DataRow)gridViewMain.GetFocusedDataRow();
     if (Row["knowID"].ToString() == string.Empty)// Is Unsaved Row?
     {
         LoadData();
         return;
     }
     SqlConnection con = new SqlConnection(MyCL.SqlConStr);
     SqlCommand cmd = new SqlCommand("", con);
     try
     {
         cmd.CommandText = @"Delete From CDknow Where knowID = " + Row["knowID"];
         con.Open();
         cmd.ExecuteNonQuery();
         MyCL.ShowMsg("تم الحذف", false, this);
     }
     catch (SqlException ex)
     {
         MyCL.ShowMsg(MyCL.CheckExp(ex), true, this);
     }
     con.Close();
     LoadData();
 }
 private void repositoryItemButtonEditSave_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
 {
     if (MessageBox.Show("هل انت متأكد؟", "تحزيــــر", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.No)
         return;
     DataRow Row = (DataRow)gridViewMain.GetFocusedDataRow();
     SqlConnection con = new SqlConnection(MyCL.SqlConStr);
     SqlCommand cmd = new SqlCommand("", con);
     try
     {
         if (Row["knowID"].ToString() == string.Empty)// Is Unsaved Row?
         {
             string NewID = MyCL.GetNewID("CDknow", "knowID");
             cmd.CommandText = string.Format(@"Insert Into CDknow (knowID, know) VALUES ({0}, '{1}')",
             NewID, Row["know"]);
         }
         else
         {
             cmd.CommandText = string.Format(@"Update CDknow Set know = '{0}' Where knowID = {1}",
             Row["know"], Row["knowID"]);
         }
         con.Open();
         cmd.ExecuteNonQuery();
         MyCL.ShowMsg("تم الحفظ", false, this);
     }
     catch (SqlException ex)
     {
         MyCL.ShowMsg(MyCL.CheckExp(ex), true, this);
     }
     con.Close();
     LoadData();
 }
Пример #12
0
        public static void Main()
        {
            var connection = new SqlConnection(connectionString: "Server=(local); Database=Northwind; Integrated Security=true;");
            connection.Open();
            var command = new SqlCommand(cmdText: "select c.CategoryName, p.ProductName from Products p inner join Categories c on c.CategoryId = p.CategoryId;", connection: connection);

            var categoriesAndProducts = new Dictionary<string, List<string>>();
            var reader = command.ExecuteReader();
            while (reader.Read())
            {
                var category = (string)reader["CategoryName"];
                var product = (string)reader["ProductName"];
                if(categoriesAndProducts.ContainsKey(category))
                {
                    categoriesAndProducts[category].Add(product);
                }
                else
                {
                    categoriesAndProducts.Add(category, new List<string>());
                }
            }

            connection.Close();

            foreach (var kvp in categoriesAndProducts)
            {
                Console.WriteLine(new string(c: '=', count: 20) + "\n" + kvp.Key + "\n" + new string(c: '=', count: 20));

                foreach (var product in kvp.Value)
                {
                    Console.WriteLine("-- " + product);
                }
            }
        }
Пример #13
0
 public bool FindUserEmailID(int userEmailID)
 {
     string sql = string.Format("SELECT * FROM UserEmails WHERE UserEmailID = '{0}' ", userEmailID);
     using(Connection cn = new Connection())
     {
         using (SqlCommand cmd = new SqlCommand())
         {
             try
             {
                 cmd.Connection = cn.SqlConnection;
                 cmd.CommandText = sql;
                 int recordsAffected = cmd.ExecuteNonQuery();
                 SqlDataReader reader = cmd.ExecuteReader();
                 if (reader.HasRows)
                 {
                     while (reader.Read())
                     {
                         //UserID = (int)reader["UserID"];
                         //loginName = reader["LoginName"].ToString();
                     }
                     return true;
                 }
                 else
                     return false;
             }
             catch (Exception ex)
             {
                 System.Diagnostics.Debug.WriteLine(ex.Message);
                 return false;
             }
         }
     }
     //return true;
 }
 public static DataTable Ilceler(int id)
 {
     try
     {
         using (baglan = new SqlConnection(ConnectionString))
         {
             baglan.Open();
             using (komutver = new SqlCommand())
             {
                 komutver.Connection = baglan;
                 komutver.CommandText = "ilce_Listele";
                 komutver.Parameters.Add("@ilceId", SqlDbType.Int);
                 komutver.Parameters["@ilceId"].Value = Convert.ToInt32(id);
                 komutver.CommandType = CommandType.StoredProcedure;
                 adp = new SqlDataAdapter(komutver);
                 dt = new DataTable();
                 adp.Fill(dt);
             }
         }
     }
     catch (Exception)
     {
         throw;
     }
     return dt;
 }
Пример #15
0
 public void eliminarProyecto(int idProyecDelete,int idEmpleado, ref string oerro)
 {
     try
     {
         int resultado = 0;
         using (SqlConnection _conn = CommonDb.ObtenerConnSql())
         {
             if (!(_conn == null))
             {
                 SqlCommand comando = new SqlCommand();
                 comando.Connection = _conn;
                 comando.CommandType = System.Data.CommandType.StoredProcedure;
                 comando.CommandText = "SP_delete_proyecto";
                 comando.Parameters.AddWithValue("@idProyecto", idProyecDelete);
                 comando.Parameters.AddWithValue("@idEmpleado", idEmpleado);
                 resultado = (int)comando.ExecuteNonQuery();
             }
         }
     }
     catch (Exception ex)
     {
         MessageBox.Show("Ocurrio un error al borrar el proyecto de la base de datos.");
         throw (ex);
     }
 }
Пример #16
0
        public static Usuario GetUsuarioSinPass(String puser)
        {
            Usuario usuario = null;
            var sqlQuery = "SELECT  (c.nombre +' '+ c.apellidos) As nombre_completo, u.id_usuario, u.nombre_usuario, u.id_empleado,  u.id_rol, u.pregunta_seguridad, u.respuesta_seguridad FROM Usuarios u  JOIN Empleados e On u.id_empleado = e.id_empleado JOIN Candidatos c On e.id_candidato = c.id_candidato WHERE u.nombre_usuario  = @puser and u.esta_oculto=0 ";
            SqlCommand cmd = new SqlCommand();
            cmd.Parameters.AddWithValue("@puser", puser);

            var ds = DBAccess.ExecuteSQLWithDS(cmd, sqlQuery);
            if (ds.Tables[0].Rows.Count > 0)
            {
                var dr = ds.Tables[0].Rows[0];
                usuario = new Usuario
                {
                    Id = Convert.ToInt32(dr["id_usuario"]),
                    NombreUsuario = dr["nombre_usuario"].ToString(),
                    IdEmpleado = Convert.ToInt32(dr["id_empleado"]),
                    IdRol = Convert.ToInt32(dr["id_rol"].ToString()),
                    PreguntaSeguridad = dr["pregunta_seguridad"].ToString(),
                    RespuestaSeguridad = dr["respuesta_seguridad"].ToString(),
                    NombreCompleto = dr["nombre_completo"].ToString()
                };
            }

            return usuario;
        }
        public void addJedi(Jedi jedi)
        {
            string commande = "";

            List<Caracteristique> carac = jedi.Caracteristiques;
            if (carac != null)
            {
                foreach (Caracteristique c in carac)
                {
                    commande += "; INSERT INTO dbo.carac_jedi (id_jedi, id_carac) VALUES ((SELECT IDENT_CURRENT('jedi'))," + c.ID + ")";
                }
            }

            using (SqlConnection sqlConnection = new SqlConnection(m_connexionString))
            {

                jedi.Nom = jedi.Nom.Replace("'", "''");

                SqlCommand sqlAddJedi = new SqlCommand("INSERT INTO dbo.jedi (nom, isSith, image) VALUES('" + jedi.Nom + "', '" + jedi.IsSith + "', '" + jedi.Image + "')" + commande, sqlConnection);

                sqlAddJedi.Connection = sqlConnection;
                sqlConnection.Open();
                sqlAddJedi.ExecuteNonQuery();
                sqlConnection.Close();
            }
        }
Пример #18
0
        protected void Page_Load(object sender, EventArgs e)
        {
            System.IO.StreamReader reader = new System.IO.StreamReader(HttpContext.Current.Request.InputStream);
            string requestFromPost = reader.ReadToEnd();

            //loop through
               // string formValue;

            string speed;
            string initialLocation;
            string finalLocation;
            string IMEI;

            if (!string.IsNullOrEmpty(Request.Form["txtSpeed"]))
            {
                //formValue = Request.Form["txtSpeed"];
                //formValue = Request.Form["txtImei"];
                speed = Request.Form["Speed"];
                initialLocation = Request.Form["initialLocation"];
                finalLocation = Request.Form["finalLocation"];
                IMEI = Request.Form["IMEI"];

                string s = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
                SqlConnection cn = new SqlConnection(s);
                cn.Open();
                SqlCommand cmd = new SqlCommand("insert into DataHistory(Speed, initialLocation, finalLocation, IMEI)values('" + speed + "','" + initialLocation + "','" + finalLocation + "','" + IMEI + "')", cn);
                cmd.ExecuteNonQuery();

            }
        }
 public bool Delete(string empresaid, tb_tipimpto BE)
 {
     using (SqlConnection cnx = new SqlConnection(conex.empConexion(empresaid)))
     {
         using (SqlCommand cmd = new SqlCommand("gspTbTipimpto_DELETE", cnx))
         {
             {
                 cmd.CommandType = CommandType.StoredProcedure;
                 cmd.Parameters.Add("@tipimptoid", SqlDbType.Char, 1).Value = BE.tipimptoid;
             }
             try
             {
                 cnx.Open();
                 if (cmd.ExecuteNonQuery() > 0)
                 {
                     return true;
                 }
                 else
                 {
                     return false;
                 }
             }
             catch (Exception ex)
             {
                 throw new Exception(ex.Message);
             }
         }
     }
 }
 public bool Delete(string empresaid, tb_co_tabla01_mediopago BE)
 {
     using (SqlConnection cnx = new SqlConnection(conex.empConexion(empresaid)))
     {
         using (SqlCommand cmd = new SqlCommand("gspTbCoTabla01Mediopago_DELETE", cnx))
         {
             {
                 cmd.CommandTimeout = 0;
                 cmd.CommandType = CommandType.StoredProcedure;
                 cmd.Parameters.Add("@codigoid", SqlDbType.Char, 3).Value = BE.codigoid;
             }
             try
             {
                 cnx.Open();
                 if (cmd.ExecuteNonQuery() > 0)
                 {
                     return true;
                 }
                 else
                 {
                     return false;
                 }
             }
             catch (Exception ex)
             {
                 throw new Exception(ex.Message);
             }
         }
     }
 }
Пример #21
0
        private static ICollection<string> FindProductBy(string partOfName)
        {
            var names = new List<string>();
            string connectionString = "Data Source=.;Initial Catalog=Northwind;Integrated Security=SSPI;";

            var dbCon = new SqlConnection(connectionString);

            dbCon.Open();
            using (dbCon)
            {
                SqlCommand command = new SqlCommand(
                    "SELECT p.ProductName FROM Products p WHERE p.ProductName LIKE @Search", dbCon);

                command.Parameters.AddWithValue("@Search", "%" + partOfName + "%");

                SqlDataReader reader = command.ExecuteReader();

                Console.WriteLine("\nProductNams that contains ({0}):\n", partOfName);
                using (reader)
                {
                    string categoryName = string.Empty;
                    string currentCategoryName = string.Empty;
                    while (reader.Read())
                    {
                        string productName = (string)reader["ProductName"];

                        names.Add(productName);
                    }
                }
            }

            return names;
        }
 public DataSet GetAll(string empresaid, tb_co_tabla01_mediopago BE)
 {
     using (SqlConnection cnx = new SqlConnection(conex.empConexion(empresaid)))
     {
         using (SqlCommand cmd = new SqlCommand("gspTbCoTabla01Mediopago_SEARCH", cnx))
         {
             DataSet ds = new DataSet();
             {
                 cmd.CommandTimeout = 0;
                 cmd.CommandType = CommandType.StoredProcedure;
                 cmd.Parameters.Add("@codigoid", SqlDbType.Char, 3).Value = BE.codigoid;
                 cmd.Parameters.Add("@descripcion", SqlDbType.VarChar, 100).Value = BE.descripcion;
                 cmd.Parameters.Add("@sigla", SqlDbType.Char, 3).Value = BE.sigla;
                 cmd.Parameters.Add("@usuar", SqlDbType.Char, 15).Value = BE.usuar;
             }
             try
             {
                 cnx.Open();
                 using (SqlDataAdapter da = new SqlDataAdapter(cmd))
                 {
                     da.Fill(ds);
                 }
                 return ds;
             }
             catch (Exception ex)
             {
                 throw new Exception(ex.Message);
             }
         }
     }
 }
Пример #23
0
        public string InsertKhachHang(string TenKH, string Gioitinh, string Diachi, string SDT, string LoaiKH, string Ghichu)
        {
            string str = "";
            string sql = "InsertKH";
            //   string sql = "insert NhomSP values (@Manhom, @Tennhom, @NgayCN)";
            SqlConnection con = new SqlConnection(KetNoiDB.getconnect());
            con.Open();
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@TenKH", TenKH);
            cmd.Parameters.AddWithValue("@gt", Gioitinh);
            cmd.Parameters.AddWithValue("@DC", Diachi);
            cmd.Parameters.AddWithValue("@SDT", SDT);
            cmd.Parameters.AddWithValue("@LoaiKH", LoaiKH);
            cmd.Parameters.AddWithValue("@ghichu", Ghichu);
            //cmd.ExecuteNonQuery();

            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dt);
            str = dt.Rows[0].ItemArray[0].ToString();

            cmd.Dispose();
            con.Close();

            return str;
        }
Пример #24
0
	public SqlDataAdapter (SqlCommand selectCommand) 
	{
		DeleteCommand = null;
		InsertCommand = null;
		SelectCommand = selectCommand;
		UpdateCommand = null;
	}
Пример #25
0
        public static int PH_BHTrans_Calc(string strConnDB, string strYearWeek)
        {
            try
            {
                int rtn = 0;
                using (SqlConnection sqlConn = new SqlConnection(strConnDB))
                {
                    if (sqlConn.State == ConnectionState.Closed ) sqlConn.Open();
                    SqlCommand sqlComm = new SqlCommand("usp_BH_Transaction_Calc", sqlConn);
                    sqlComm.CommandText = "usp_BH_Transaction_Calc";
                    sqlComm.CommandType = CommandType.StoredProcedure;
                    sqlComm.CommandTimeout = 0;
                    sqlComm.Parameters.AddRange(new SqlParameter[] { new SqlParameter("@Tesco_Week", strYearWeek) });
                    rtn = sqlComm.ExecuteNonQuery();
                }
                    //int rtn =
                    //SqlTransaction sqlTran = new SqlConnection(strConnDB).BeginTransaction();

                    //SqlHelper.ExecuteNonQuery(strConnDB, CommandType.StoredProcedure, "usp_BH_Transaction_Calc"
                    //            , new SqlParameter[] {new SqlParameter("@Tesco_Week",strYearWeek)
                    //                                 });

                    return rtn;
            }
            catch (Exception ex)
            {
                throw new Exception("PH_BHTrans_Calc >> " + ex.Message);
            }
        }
        public void GetDBBackup(string backupDBName, string backupPath)
        {
            try
            {
                DataBaseUtility db = new DataBaseUtility();

                SqlCommand cm = new SqlCommand();
                string s = DateTime.Now.ToString("MMM/dd/yyyy");

                SqlParameter[] sqlParams = new SqlParameter[]
            {   new SqlParameter("@DBNAME", backupDBName),
                new SqlParameter("@PATH", backupPath),
                new SqlParameter("@BACKUPTYPE", 1),
                new SqlParameter("@MSG", "Genrate Backup"),
                new SqlParameter("@BACKUPFILENAME", s.Replace("/", "_") )};

                db.ExecuteSP("DATABASE_BACKUP", sqlParams);

                log.Info("Backup created success fully with name "+ s.Replace("/", "_"));
            }
            catch (Exception ex)
            {

                log.Info("Exception in  GetDBBackup  ",ex);
            }
        }
        protected override void DataPortal_Insert()
        {
            bool cancel = false;
            OnInserting(ref cancel);
            if (cancel) return;

            const string commandText = "INSERT INTO [dbo].[Category] ([CategoryId], [Name], [Descn]) VALUES (@p_CategoryId, @p_Name, @p_Descn)";
            using (var connection = new SqlConnection(ADOHelper.ConnectionString))
            {
                connection.Open();
                using(var command = new SqlCommand(commandText, connection))
                {
                    command.Parameters.AddWithValue("@p_CategoryId", this.CategoryId);
                command.Parameters.AddWithValue("@p_Name", ADOHelper.NullCheck(this.Name));
                command.Parameters.AddWithValue("@p_Descn", ADOHelper.NullCheck(this.Description));

                    //result: The number of rows changed, inserted, or deleted. -1 for select statements; 0 if no rows were affected, or the statement failed. 
                    int result = command.ExecuteNonQuery();
                    if (result == 0)
                        throw new DBConcurrencyException("The entity is out of date on the client. Please update the entity and try again. This could also be thrown if the sql statement failed to execute.");

                    LoadProperty(_originalCategoryIdProperty, this.CategoryId);
                }

                FieldManager.UpdateChildren(this, connection);
            }

            OnInserted();

        }
Пример #28
0
        //para insertar proyecto
        public int AgregarProyecto(ProyectoE pProyecto, int idEmpleado, ref string oerro)
        {
            int resultado = 0;
            using (SqlConnection _conn = CommonDb.ObtenerConnSql())
            {
                if (!(_conn == null))
                {
                    SqlCommand comando = new SqlCommand();
                    comando.Connection = _conn;
                    comando.CommandType = System.Data.CommandType.StoredProcedure;
                    comando.CommandText = "SP_insertar_Proyectos";
                    comando.Parameters.AddWithValue("@id_empresa", pProyecto.id_empresa);
                    comando.Parameters.AddWithValue("@nombreProyecto", pProyecto.nombre_proyecto);
                    comando.Parameters.AddWithValue("@fecha", pProyecto.fecha_inicio);
                    comando.Parameters.AddWithValue("@idPais", pProyecto.id_pais);
                    comando.Parameters.AddWithValue("@idEstadoProyecto", pProyecto.id_estadoProyecto);
                    comando.Parameters.AddWithValue("@idTipoContrato", pProyecto.id_tipoContrato);
                    comando.Parameters.AddWithValue("@idEmpleado", idEmpleado);
                    resultado = (int)comando.ExecuteScalar();

                }

                else
                    resultado = 0;
                oerro = "";
            }
            return resultado;
        }
Пример #29
0
        public List<ChatMessageModel> GetMessagesLimit(int limit)
        {
            List<ChatMessageModel> msg = new List<ChatMessageModel>();

            if (limit < 0) limit = 0;
            using (SqlConnection conn = SqlConnectionService.GetConnection())
            {
                string query = String.Format("SELECT TOP {0} u.UserId, u.Sex, m.Message, m.Time, u.UserLogin FROM MessageLogs m JOIN Users u on u.UserId = m.UserId ORDER BY m.MessageId DESC ", limit);
                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    conn.Open();

                    cmd.Parameters.AddWithValue("@Limit", limit);

                    var reader = cmd.ExecuteReader();

                    while (reader.Read())
                    {
                        msg.Add(Helper.CreateChatMessage(reader["UserLogin"].ToString(), Convert.ToInt32(reader["UserId"]), reader["Message"].ToString(), Convert.ToInt32(reader["Sex"]), reader["Time"].ToString()));
                    }

                    conn.Close();

                    return msg;
                }
            }
        }
Пример #30
0
        /// <summary>
        /// Use to retrieve a specific lab tests.
        /// </summary>
        /// <returns>the lab test with the given id</returns>
        public static LabTest GetLabTestByID(int id)
        {
            LabTest test = new LabTest();
            try
            {
                using (SqlConnection connection = HealthCareDBConnection.GetConnection())
                {
                    string selectStatement =
                            "SELECT * from LabTest where testID = @TESTID";
                    using (SqlCommand selectCommand = new SqlCommand(selectStatement, connection))
                    {
                        selectCommand.Parameters.AddWithValue("@TESTID", id);
                        connection.Open();

                        using (SqlDataReader reader = selectCommand.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                test.TestId = (int)reader["testID"];
                                test.TestType = reader["testType"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK);
            }
            return test;
        }