C# (CSharp) Class System.Data.SqlClient. SqlConnection Code Examples

C# (CSharp) System.Data.SqlClient.SqlConnection - 30 examples found. These are the top rated real world C# (CSharp) examples of class System.Data.SqlClient.SqlConnection extracted from open source projects. You can rate examples to help us improve the quality of examples
Inheritance: System.Data.Common.DbConnection, 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;
        }
        public static void Main()
        {
            using (SqlConnection dbcon = new SqlConnection(ImageExtracter.ConnectionString))
            {
                dbcon.Open();

                SqlCommand command = new SqlCommand("SELECT Picture FROM Categories", dbcon);
                var reader = command.ExecuteReader();
                var counter = 0;

                while (reader.Read())
                {
                    byte[] imageByteArray = (byte[])reader["Picture"];

                    const int oleMetaPictStartPosition = 78;
                    var memoryStream =
                        new MemoryStream(imageByteArray, oleMetaPictStartPosition,
                            imageByteArray.Length - oleMetaPictStartPosition);

                    using (memoryStream)
                    {
                        using (var image = Image.FromStream(memoryStream, true, true))
                        {
                            image.Save("../../picture" + (++counter) + ".jpg");
                        }
                    }
                }
            }

            Console.WriteLine("Images exported successfully!");
            Console.WriteLine("See project folder for results!");
        }
        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();

        }
Example #4
0
        public IEnumerable<UserInfoShort> EnumerateClients(string fio, string login, int pageNumber, int countPerPage)
        {
            using (var connection = new SqlConnection(_configurationService.DatabaseConnectionString))
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "sp_EnumerateClients";
                    command.Parameters.AddWithValue("@FIO", fio ?? (object)DBNull.Value).SqlDbType = SqlDbType.NVarChar;
                    command.Parameters.AddWithValue("@Login",
                        login != null ? login.ToLower() : (object)DBNull.Value).SqlDbType = SqlDbType.NVarChar;
                    command.Parameters.AddWithValue("@PageNumber", pageNumber).SqlDbType = SqlDbType.Int;
                    command.Parameters.AddWithValue("@CountPerPage", countPerPage).SqlDbType = SqlDbType.Int;
                    connection.Open();
                    using (var reader = command.ExecuteReader())
                    {
                        var lst = new List<UserInfoShort>();
                        while (reader.Read())
                        {
                            lst.Add(new UserInfoShort
                                {
                                    UserId = (Guid)reader["UserId"],
                                    CounOfCardAccounts = (int)reader["CounOfCardAccounts"],
                                    FIO = (string)reader["FIO"],
                                    Login = (string)reader["Login"],
                                    RegistrationDate = (DateTime)reader["RegistrationDate"]
                                });
                        }

                        return lst;
                    }
                }
            }
        }
Example #5
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);
            }
        }
Example #6
0
 private DataTable GetTiposVenda()
 {
     DataTable dataTable = new DataTable();
     this.con = new SqlConnection(Security.GetCnn());
     string selectCommandText = "";
     if (this._dev != "D")
     {
         selectCommandText = "select tipo_venta_id, descripcion, devolucion from Tipo_venta where devolucion='N'";
     }
     else
     {
         selectCommandText = "select tipo_venta_id, descripcion, devolucion from Tipo_venta where devolucion='S'";
     }
     if ((this.con != null) && (this.con.State == ConnectionState.Open))
     {
         this.con.Close();
     }
     this.con.Open();
     try
     {
         new SqlDataAdapter(selectCommandText, this.con).Fill(dataTable);
         this.con.Close();
     }
     catch (Exception)
     {
     }
     return dataTable;
 }
        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 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);
                    }
                }
            }
        }
 public List<BootcampTechnology> GetAllBootcampTechnologies()
 {
     using (SqlConnection connection = new SqlConnection(Settings.GetConnectionString()))
     {
         return connection.Query<BootcampTechnology>("BootcampTechnologyGetAll", commandType: CommandType.StoredProcedure).ToList();
     }
 }
 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();
 }
 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();
 }
Example #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);
                }
            }
        }
        protected void load_price()
        {
            string price_name = null;
            string article = null;
            price_name = Request.QueryString["price_name"];
            article = Request.QueryString["article"];

            if (article != null)
            {
                if (price_name != null)
                {

                    SqlConnection con = new SqlConnection(str);
                    string sql = "SELECT id, article, ROUND([price]*(SELECT course FROM Course_USD WHERE id = 1),2) AS [price] FROM " + price_name + " WHERE article=" + article + ";";
                    SqlDataAdapter da = new SqlDataAdapter(sql, con);
                    DataSet ds = new DataSet();
                    da.Fill(ds, price_name);
                    DataRow dr;
                    dr = ds.Tables[price_name].Rows[0];

                    //Заполняем стоимость товара:
                    lblprice.Text = dr["price"].ToString();
                }
                else {Response.Redirect("Error.aspx");}

            }
            else {Response.Redirect("Error.aspx"); }
        }
 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;
 }
        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;
            }
        }
Example #16
0
 public static SqlConnection createConnection()
 {
     SqlConnection conn = new SqlConnection("Data Source=(LocalDB)\\MSSQLLocalDB;" +
         "AttachDbFilename=C:\\Users\\Edson\\Downloads\\Solution\\Solution\\WebApp\\WebApp\\App_Data\\Database.mdf;" +
         "Integrated Security=True;Connect Timeout=30");
     return conn;
 }
 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);
             }
         }
     }
 }
Example #18
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;
        }
        public static DataTable GetHandoverLogByHandoverLogId(int handoverLogId)
        {
            string connectionString = ConnectionStringFactory.GetNXJCConnectionString();
            DataSet ds = new DataSet();

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                SqlCommand command = connection.CreateCommand();
                command.CommandText = @"SELECT  WorkingTeamShiftLog.*,
                                                StaffInfo.Name AS ChargeManName, ProductLine.Name AS ProductLineName,
                                                WorkingTeam.Name AS WorkingTeamName
                                        FROM    WorkingTeam INNER JOIN
                                                StaffInfo INNER JOIN
                                                WorkingTeamShiftLog ON StaffInfo.ID = WorkingTeamShiftLog.ChargeManID INNER JOIN
                                                ProductLine ON WorkingTeamShiftLog.ProductLineID = ProductLine.ID ON
                                                WorkingTeam.ID = WorkingTeamShiftLog.WorkingTeamID
                                        WHERE   (WorkingTeamShiftLog.ID = @workingTeamShiftLogID)";

                command.Parameters.Add(new SqlParameter("workingTeamShiftLogID", handoverLogId));
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(ds);
            }

            return ds.Tables[0];
        }
Example #20
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);
                    }
                }
            }
        }
Example #21
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();
                }
            }
        }
        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();

            }
        }
Example #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;
        }
 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);
             }
         }
     }
 }
Example #25
0
 public bool CreateUser(UserInfo userInfo)
 {
     var salt = _helper.GenerateSalt();
     var pas = _helper.EncodePassword(userInfo.Password, salt);
     using (var connection = new SqlConnection(_configurationService.DatabaseConnectionString))
     {
         using (var command = connection.CreateCommand())
         {
             command.CommandType = CommandType.StoredProcedure;
             command.CommandText = "sp_CreateUser";
             command.Parameters.AddWithValue("@Login", userInfo.Login).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Password", pas).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@PasswordSalt", salt).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Email", userInfo.Email).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Fio", userInfo.Fio).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Address", userInfo.Address).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Phone", userInfo.Phone).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Mobile", userInfo.Mobile).SqlDbType = SqlDbType.NVarChar;
             command.Parameters.AddWithValue("@Country", userInfo.Country).SqlDbType = SqlDbType.Char;
             command.Parameters.AddWithValue("@Zip", userInfo.Zip).SqlDbType = SqlDbType.NVarChar;
             var retParam = command.Parameters.AddWithValue("@Return", SqlDbType.Int);
             retParam.Direction = ParameterDirection.ReturnValue;
             connection.Open();
             command.ExecuteNonQuery();
             return (int)retParam.Value == 1;
         }
     }
 }
 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);
             }
         }
     }
 }
        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();
        }
        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();
            }
        }
Example #29
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;
 }
Example #30
0
        /// <summary>
        /// Delete values from Item by primary key(s).
        /// </summary>
        /// <param name="sqlConnection">Sql connection or null.</param>
        /// <param name="sqlTransaction">Sql transaction or null.</param>
        /// <param name="connectionKeyName">Connection key name located in config file.</param>
        /// <param name="itemID"></param>
        /// <returns>true if successful otherwise false.</returns>
        public bool ItemDeleteAuto(
            SqlConnection sqlConnection,
            SqlTransaction sqlTransaction,
            string connectionKeyName,
            int itemID)
        {
            SqlConnection connection = CustomSqlHelper.CreateConnection(CustomSqlHelper.GetConnectionStringFromConnectionStrings(connectionKeyName), sqlConnection);
            SqlTransaction transaction = sqlTransaction;

            using (SqlCommand command = CustomSqlHelper.CreateCommand("ItemDeleteAuto", connection, transaction,
                CustomSqlHelper.CreateInputParameter("ItemID", SqlDbType.Int, null, false, itemID),
                    CustomSqlHelper.CreateReturnValueParameter("ReturnCode", SqlDbType.Int, null, false)))
            {
                int returnCode = CustomSqlHelper.ExecuteNonQuery(command, "ReturnCode");

                if (transaction == null)
                {
                    CustomSqlHelper.CloseConnection(connection);
                }

                if (returnCode == 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }