Example #1
1
        /// <summary>
        /// SQLのパラメータを追加します。サニタイジング(SQLインジェクション対策)をします
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="direction"></param>
        /// <param name="paramName"></param>
        /// <param name="type"></param>
        /// <param name="value">値がnullの場合はDBNull.Valueを挿入します</param>
        public static void AddSqlParameter(SqlCommand cmd, ParameterDirection direction, string paramName, SqlDbType type, Object value)
        {
            if (cmd == null)
            {
                return;
            }

            if (string.IsNullOrEmpty(paramName) || string.IsNullOrWhiteSpace(paramName))
            {
                return;
            }

            if (value == null)
            {
                value = DBNull.Value;
            }

            SqlParameter param = cmd.CreateParameter();
            param.ParameterName = paramName;
            param.SqlDbType = type;
            param.Direction = direction;
            param.Value = value;

            cmd.Parameters.Add(param);
        }
Example #2
0
 /// <summary>
 /// Creates and adds parameter to SqlCommand
 /// </summary>
 /// <param name="command">SqlCommand</param>
 /// <param name="parameterName">Paramater Name</param>
 /// <param name="value">Paramater Value</param>
 public void CreateParameter(SqlCommand command, string parameterName, object value)
 {
     SqlParameter param = command.CreateParameter();
     param.ParameterName = parameterName;
     param.Value = value;
     command.Parameters.Add(param);
 }
Example #3
0
            public static DataSet ReportAssaysResult()
            {

                DataSet ds = new DataSet("ReportResult");

                using (new TransactionScope(TransactionScopeOption.Suppress))
                {
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
                    //////Get Curves
                    cmd.CommandText = "[X_SP_GetAssaysByWorkflow]";
                    //cmd.CommandText = "[dbo].[X_SP_GetAssays]";
                    cmd.CommandType = CommandType.StoredProcedure;

                    var parm1 = cmd.CreateParameter();
                    parm1.ParameterName = "@assay_group_projectid";
                    parm1.DbType = DbType.Guid;
                    parm1.Value = new Guid("31B4CCEC-A72C-4F30-A13A-32B48762FDD9");
                    //cmd.Parameters.Add(parm1);

                    try
                    {
                        //Let's actually run the queries
                        cmd.Connection.Open();
                        cmd.CommandTimeout = 600; //10 mins
                        var reader = cmd.ExecuteReader();
                        ds.Load(reader, LoadOption.OverwriteChanges, "t");
                    }
                    finally
                    {
                        cmd.Connection.Close();
                    }                    
                    return ds;
                }
            }
        private static void DeletePackageStatistics(string connectionString, int warehouseHighWatermark)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                int iterations = 0;
                int rows;
                do
                {
                    string sql = @"
                      DELETE TOP(50000) [PackageStatistics]
                      WHERE [Key] <= @OriginalKey
                      AND [Key] <= (SELECT DownloadStatsLastAggregatedId FROM GallerySettings)
                      AND [TimeStamp] < DATEADD(day, -7, GETDATE())
                    ";

                    SqlCommand command = new SqlCommand(sql, connection);
                    command.CommandType = CommandType.Text;

                    SqlParameter parameter = command.CreateParameter();
                    parameter.DbType = DbType.Int32;
                    parameter.ParameterName = "@OriginalKey";
                    parameter.Value = warehouseHighWatermark;

                    command.Parameters.Add(parameter);
                    rows = command.ExecuteNonQuery();
                }
                while (rows > 0 && iterations++ < 10);
            }
        }
        public static int GetLastOriginalKey(string connectionString)
        {
            try
            {
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    connection.Open();

                    SqlCommand command = new SqlCommand("GetLastOriginalKey", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandTimeout = 120;

                    SqlParameter resultParam = command.CreateParameter();
                    resultParam.Direction = ParameterDirection.Output;
                    resultParam.DbType = DbType.Int32;
                    resultParam.ParameterName = "@OriginalKey";

                    command.Parameters.Add(resultParam);

                    command.ExecuteNonQuery();

                    if (resultParam.Value is DBNull)
                    {
                        return 0;
                    }

                    return (int)resultParam.Value;
                }
            }
            catch (Exception e)
            {
                string msg = $"Exception in GetLastOriginalKey (warehouse side): {e.Message}";
                throw new ApplicationException(msg, e);
            }
        }
Example #6
0
        public static bool Insert_Callstack(SqlConnection conn, ref SqlTransaction tran, int project_uid, byte[] signature, out int callstack_uid)
        {
            callstack_uid = 0;

            try
            {
                SqlCommand cmd1 = new SqlCommand("usp_insert_callstack", conn, tran);
                cmd1.CommandType = CommandType.StoredProcedure;

                SqlParameter returned = cmd1.CreateParameter();
                returned.Direction = ParameterDirection.ReturnValue;

                cmd1.Parameters.Add(returned);
                cmd1.Parameters.AddWithValue("@project_uid", project_uid);
                cmd1.Parameters.AddWithValue("@signature", signature);

                cmd1.ExecuteNonQuery();

                callstack_uid = (int)returned.Value;
            }
            catch (System.Exception e)
            {
                Console.WriteLine(e.Message);
                return false;
            }

            return true;
        }
Example #7
0
        protected override CustomerCommand CreateCommand(DataCommandConfig commandConfig)
        {
            //1.创建 Connection 对象【需要从连接池中获取连接对象,此处后续优化】
            //string strConn = ConfigurationManager.AppSettings["ConnectionString"];
            var dbConfig = DataManager.DatabaseDictionary[commandConfig.DataSourceID];
            string strConn = dbConfig.ConnectionString;
            strConn = string.Format(strConn, ConfigurationManager.AppSettings["Tests.Environment"].ToLower());
            SqlConnection dbConnection = new SqlConnection(strConn);

            //2.创建 Command 对象
            SqlCommand command = new SqlCommand(commandConfig.CommandText, dbConnection);
            command.CommandType = CommandType.Text;

            //3.填充参数列表
            foreach (ParameterConfig param in commandConfig.Parameters)
            {
                SqlParameter parameter = command.CreateParameter();
                parameter.ParameterName = param.Name;
                parameter.DbType = param.DBType;
                parameter.Size = param.Size == 0 ? 4 : param.Size;
                parameter.Direction = ParameterDirection.Input; //默认是输入参数
                command.Parameters.Add(parameter);
            }
            CustomerCommand customerCmd = new CustomerCommand(command, DataBaseType.SQLServer);
            return customerCmd;
        }
Example #8
0
        public override void Select(bool display)
        {
            string testcommand = @"
            select * from Entities
            join @intt 
	            on [@intt].Id = Entities.EntityId
            ";

            using (SqlConnection connection = new SqlConnection(this.ConnectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(testcommand, connection))
                {
                    command.CommandTimeout = 5000;
                    command.CommandType = CommandType.Text;

                    var param = command.CreateParameter();
                    param.SqlDbType = SqlDbType.Structured;
                    param.ParameterName = "@intt";
                    param.TypeName = "udt_inttable";

                    param.Value = this.SelectRecords;

                    command.Parameters.Add(param);

                    using (var reader = command.ExecuteReader())
                    {
                        DisplayReader(display, reader);
                    }
                }
            }
        }
Example #9
0
        public override void Select(bool display)
        {
            string testcommand = @"
            select * from Entities
            where Entities.EntityId in (select data from dbo.split(@p1,','))
            ";

            using (SqlConnection connection = new SqlConnection(this.ConnectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(testcommand, connection))
                {
                    command.CommandTimeout = 5000;
                    command.CommandType = CommandType.Text;


                    var param = command.CreateParameter();
                    param.ParameterName = "@p1";

                    param.Value = string.Join(",", this.SelectRecords.Select(i => i.GetValue(0)).ToArray());

                    command.Parameters.Add(param);

                    using (var reader = command.ExecuteReader())
                    {
                        DisplayReader(display, reader);
                    }
                }
            }
        }
		public void TestBug4689()
		{
			if (ConnectedDataProvider.GetDbType() != DataBaseServer.SQLServer) {
				//All tests in this class are only for MSSQLServer.
				Log(string.Format("All tests in this class are only for MSSQLServer and cannot be tested on {0}", ConnectedDataProvider.GetDbType()));
				return;
			}

			try
			{
				// Every Sub Test must begin with BeginCase
				BeginCase("Test Bug 4689 - Exception when adding System.Data.DbType.Date parameter");
				SqlCommand command = new SqlCommand();
				SqlParameter param = command.CreateParameter();
				param.ParameterName = "@EffectiveDate";
				param.DbType = DbType.Date;
				param.Value = DateTime.Now.Date;
				command.Parameters.Add(param);
				Pass("Addition of parameter didn't throw exception.");
			} 
			catch(Exception ex)
			{
				exp = ex;
			}
			finally
			{
				// Every Sub Test must end with EndCase
				EndCase(exp);
				exp = null;
			}
		}
Example #11
0
 private void AddSqlParameter(SqlCommand sqlCommand, string name, SqlDbType sqlDbType, Object value)
 {
     SqlParameter sqlParameter = sqlCommand.CreateParameter();
     sqlParameter.ParameterName = name;
     sqlParameter.SqlDbType = sqlDbType;
     sqlParameter.Direction = ParameterDirection.Input;
     sqlParameter.Value = value;
     sqlCommand.Parameters.Add(sqlParameter);
 }
Example #12
0
 /// <summary>
 /// Crear parametros para el comando
 /// </summary>
 public static SqlParameter ParameterAdd(SqlCommand cmd, string nombre, SqlDbType tipo, object valor)
 {
     SqlParameter para = new SqlParameter();
     para = cmd.CreateParameter();
     para.ParameterName = nombre;
     para.Value = valor; 
     para.SqlDbType = tipo;
     cmd.Parameters.Add(para);
     return para;
 }
Example #13
0
        internal static void SetSaveParameters(SqlCommand command, BusinessBase businessBase)
        {
            DbParameter idParam = command.CreateParameter();
            idParam.DbType = DbType.Int32;
            idParam.Direction = ParameterDirection.InputOutput;
            idParam.ParameterName = idParamName;
            if (businessBase.Id == 0)
            {
                idParam.Value = DBNull.Value;
            }
            else
            {
                idParam.Value = businessBase.Id;
            }
            command.Parameters.Add(idParam);

            DbParameter returnValue = command.CreateParameter();
            returnValue.Direction = ParameterDirection.ReturnValue;
            command.Parameters.Add(returnValue);
        }
        protected void InsertParameters(SqlCommand cmd)
        {
            foreach (var item in _parameters)
            {
                cmd.Parameters.Add(cmd.CreateParameter()).ParameterName = item.Key;
                cmd.Parameters[item.Key].Value = item.Value.Value;

                if (item.Value.TranslateDataType)
                {
                    cmd.Parameters[item.Key].SqlDbType = item.Value.DbTranslationType;
                }
            }
        }
Example #15
0
		private void AddParameter(SqlCommand dbCmd, string paramName, object paramValue, ParameterDirection direction)
		{
			if (dbCmd == null)
				return;

			SqlParameter param = dbCmd.CreateParameter();
			param.Direction = direction;
			param.ParameterName = paramName;

			if (DateTime.MinValue.Equals(paramValue))
				param.Value = DBNull.Value;
			else
				param.Value = paramValue ?? DBNull.Value;

			dbCmd.Parameters.Add(param);
		}
Example #16
0
        public override void Merge(bool display)
        {
            string testcommand = @"
            merge Entities as target
            using (select * from @upusers) as source (EntityId, Name, Phone, Email, Address, City, Zip, State, Country, BirthDate)
            on (target.EntityId = source.EntityId)
            when matched then
	            update 
		            set Name = source.Name,
		             Phone = source.Phone,
		             Email = source.Email,
		             Address = source.Address,
		             City = source.City,
		             Zip = source.Zip,
		             State = source.State,
		             Country = source.Country,
		             BirthDate = source.Birthdate
            when Not Matched Then
	            insert (Name, Phone, Email, Address, City, Zip, State, Country, Birthdate)
	            values (source.Name, source.Phone, source.Email, source.Address, source.City, source.Zip, source.State, source.Country, source.Birthdate)
            ;
            ";

            using (SqlConnection connection = new SqlConnection(this.ConnectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(testcommand, connection))
                {
                    command.CommandTimeout = 5000;
                    command.CommandType = CommandType.Text;

                    var param = command.CreateParameter();
                    param.SqlDbType = SqlDbType.Structured;
                    param.ParameterName = "@upusers";
                    param.TypeName = "udt_entities";

                    param.Value = this.MergeRecords(this.BulkOperationsCount);

                    command.Parameters.Add(param);

                    using (var reader = command.ExecuteReader())
                    {
                        DisplayReader(display, reader);
                    }
                }
            }
        }
Example #17
0
        public BELine GetLineabyId(SqlConnection con, int idLine)
        {
            BELine obeLine = new BELine();
            SqlCommand cmd = new SqlCommand("USP_GetLineabyId", con);
            cmd.CommandType = CommandType.StoredProcedure;
           
            //ARGUMENTOS DE PROCEDURE
            DbParameter param = cmd.CreateParameter();
            param.DbType = DbType.Int32;
            param.ParameterName = "idLine";
            param.Value = idLine;
            cmd.Parameters.Add(param);

            SqlDataReader drd = cmd.ExecuteReader(CommandBehavior.SingleResult);
            
            
            if (drd != null)
            {
                while (drd.Read())
                {
                    //obeLine.idLine = drd.GetInt32(0);
                    //obeLine.Titulo = drd.GetString(1);
                    //obeLine.Descripcion = drd.GetString(2);
                    //obeLine.Estado = drd.GetInt32(3);
                    //obeLine.imgPortada =  Convert.ToString(drd["imgPortada"]);
                    //obeLine.Creador = drd.GetString(5);
                    //obeLine.FechaCreacion = drd.GetDateTime(6);
                    //obeLine.Modificador = drd.GetString(7);
                    //if (!drd.IsDBNull(8)) { obeLine.FechaModificacion = drd.GetDateTime(8); };

                    if (!drd.IsDBNull(0)) { obeLine.idLine = drd.GetInt32(0); };
                    if (!drd.IsDBNull(1)) { obeLine.Titulo = drd.GetString(1); };
                    if (!drd.IsDBNull(2)) { obeLine.Descripcion = drd.GetString(2); };
                    if (!drd.IsDBNull(3)) { obeLine.Estado= drd.GetInt32(3); };
                    if (!drd.IsDBNull(4)) { obeLine.imgPortada = drd.GetString(4); };
                    if (!drd.IsDBNull(5)) { obeLine.Creador = drd.GetString(5); };
                    if (!drd.IsDBNull(6)) { obeLine.FechaCreacion = drd.GetDateTime(6); };
                    if (!drd.IsDBNull(7)) { obeLine.Modificador = drd.GetString(7); };
                    if (!drd.IsDBNull(8)) { obeLine.FechaModificacion = drd.GetDateTime(8); };

                     
                }
                drd.Close();
            }

            return obeLine;
        }
Example #18
0
 public static void ParameterSetup(ref SqlCommand parmCmd, params object[] parmInput)
 {
     bool parametersExist = true;
     if (parmInput == null) parametersExist = false;
     else if (parmInput.Length == 0) parametersExist = false;
     if (parametersExist)
     {
         for (int countparams = 0; countparams < parmInput.Length; countparams += 2)
         {
             SqlParameter param = parmCmd.CreateParameter();
             param.ParameterName = parmInput[countparams].ToString();
             param.Direction = ParameterDirection.Input;
             param.Value = parmInput[countparams + 1];
             parmCmd.Parameters.Add(param);
         }
     }
 }
Example #19
0
        public List<BEArticle> GetPostArticlesByLine(SqlConnection con,int idLine)
        {
            List<BEArticle> lbeArticle = null;
            SqlCommand cmd = new SqlCommand("USP_GetArticleListByLine", con);
            cmd.CommandType = CommandType.StoredProcedure;
            //ARGUMENTOS DE PROCEDURE
            DbParameter param = cmd.CreateParameter();
            param.DbType = DbType.Int32;
            param.ParameterName = "idLine";
            param.Value = idLine;
            cmd.Parameters.Add(param);
            SqlDataReader drd = cmd.ExecuteReader(CommandBehavior.SingleResult);

            if (drd != null)
            {
                lbeArticle = new List<BEArticle>();
                BEArticle obeArticle;
                while (drd.Read())
                {
                    obeArticle = new BEArticle();

                    if (!drd.IsDBNull(0)) { obeArticle.idArticle = drd.GetInt32(0); };
                    if (!drd.IsDBNull(1)) { obeArticle.idLine = drd.GetInt32(1); };
                    if (!drd.IsDBNull(2)) { obeArticle.Titulo= drd.GetString(2); };
                    if (!drd.IsDBNull(3)) { obeArticle.SubTitulo= drd.GetString(3); };
                    if (!drd.IsDBNull(4)) { obeArticle.Descripcion = drd.GetString(4); };
                    if (!drd.IsDBNull(5)) { obeArticle.Contenido = drd.GetString(5); };
                    if (!drd.IsDBNull(6)) { obeArticle.imgArticle= drd.GetString(6); };
                    if (!drd.IsDBNull(7)) { obeArticle.Fecha = drd.GetDateTime(7); };
                    if (!drd.IsDBNull(8)) { obeArticle.Creador= drd.GetString(8); };
                    if (!drd.IsDBNull(9)) { obeArticle.FechaCreacion= drd.GetDateTime(9); };
                    if (!drd.IsDBNull(10)) { obeArticle.Modificador= drd.GetString(10); };
                    if (!drd.IsDBNull(11)) { obeArticle.FechaModificacion = drd.GetDateTime(11); };


                    lbeArticle.Add(obeArticle);

                }
                drd.Close();
            }

            return lbeArticle;
        }
Example #20
0
        public List<BEQuestion> GetQuestionbyArticleId(SqlConnection con, int idArticle)
        {
            List<BEQuestion> lbeQuestion = null;
            SqlCommand cmd = new SqlCommand("USP_GetQuestionbyArticleId", con);
            cmd.CommandType = CommandType.StoredProcedure;
            
            //ARGUMENTOS DE PROCEDURE
            DbParameter param = cmd.CreateParameter();
            param.DbType = DbType.Int32;
            param.ParameterName = "idArticle";
            param.Value = idArticle;
            cmd.Parameters.Add(param);

            SqlDataReader drd = cmd.ExecuteReader(CommandBehavior.SingleResult);

            if (drd != null)
            {
                lbeQuestion = new List<BEQuestion>();
                BEQuestion obeQuestion;
                while (drd.Read())
                {
                    obeQuestion = new BEQuestion();

                    if (!drd.IsDBNull(0)) { obeQuestion.idQuestion = drd.GetInt32(0); };
                    if (!drd.IsDBNull(1)) { obeQuestion.idArticle = drd.GetInt32(1); };
                    if (!drd.IsDBNull(2)) { obeQuestion.Alias = drd.GetString(2); };
                    if (!drd.IsDBNull(3)) { obeQuestion.Question = drd.GetString(3); };
                    if (!drd.IsDBNull(4)) { obeQuestion.FechaCreacion = drd.GetDateTime(4); };
                    if (!drd.IsDBNull(5)) { obeQuestion.Creador = drd.GetString(5); };
                    if (!drd.IsDBNull(6)) { obeQuestion.FechaModificacion = drd.GetDateTime(6); };
                    if (!drd.IsDBNull(7)) { obeQuestion.Modificador = drd.GetString(7); };
                    


                    lbeQuestion.Add(obeQuestion);

                }
                drd.Close();
            }

            return lbeQuestion;
        }
Example #21
0
        public override void Select(bool display)
        {
            string testcommand = @"
            select * from Entities
            where EntityId in (
	            SELECT T.val.value('(@Id)','int') as Id
	            FROM @xmlval.nodes('/root/item') T(val)
            )
            ";

            using (SqlConnection connection = new SqlConnection(this.ConnectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(testcommand, connection))
                {
                    command.CommandTimeout = 5000;
                    command.CommandType = CommandType.Text;

                    var param = command.CreateParameter();
                    param.SqlDbType = SqlDbType.Xml;
                    param.ParameterName = "@xmlval";

                    XElement root = new XElement("root");
                    foreach (var r in this.SelectRecords)
                    {
                        root.Add(new XElement("item",
                            new XAttribute("Id", r[0])
                            ));
                    }

                    param.Value = root.ToString();

                    command.Parameters.Add(param);

                    using (var reader = command.ExecuteReader())
                    {
                        DisplayReader(display, reader);
                    }
                }
            }
        }
        public static int Insert(ServiceProvider serviceProvider)
        {
            int result = 0;
            using (SqlConnection myConnection = new SqlConnection(ConfigurationManager.ConnectionStrings[Connection.ConnectionName].ConnectionString))
            {
                string insertstmt = "INSERT INTO [dbo].[ServiceProvider] ([name], [code], [companyid], [sptype], [description], [installationcharges], [servicecharges]) VALUES (@name, @code, @companyid, @sptype, @description, @installationcharges, @servicecharges)";

                SqlCommand command = new SqlCommand(insertstmt, myConnection);

                command.CommandType = CommandType.Text;

                command.Parameters.Add("@name", SqlDbType.VarChar, 50).Value = serviceProvider.Name;

                command.Parameters.Add("@code", SqlDbType.VarChar, 3).Value = serviceProvider.Code;

                command.Parameters.Add("@companyid", SqlDbType.Int).Value = serviceProvider.CompanyId;

                command.Parameters.Add("@sptype", SqlDbType.Int).Value = serviceProvider.ProviderType;

                if (serviceProvider.Description == null)
                    command.Parameters.Add("@description", SqlDbType.VarChar, 200).Value = DBNull.Value;
                else
                    command.Parameters.Add("@description", SqlDbType.VarChar, 200).Value = serviceProvider.Description;

                command.Parameters.Add("@installationcharges", SqlDbType.Money).Value = serviceProvider.InstallationCharges;
                command.Parameters.Add("@servicecharges", SqlDbType.Money).Value = serviceProvider.ServiceCharges;

                DbParameter returnValue;
                returnValue = command.CreateParameter();
                returnValue.Direction = ParameterDirection.ReturnValue;
                command.Parameters.Add(returnValue);

                myConnection.Open();
                command.ExecuteNonQuery();
                result = Convert.ToInt32(returnValue.Value);
                myConnection.Close();
            }
            return result;
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            var path = Server.MapPath(@"db\qqq.mdf");
            string dbLocation = System.IO.Path.GetFullPath(path);
            SqlConnection connection1 = new SqlConnection
               (
            "Data Source=.\\SQLEXPRESS;AttachDbFilename=\"" + dbLocation + "\";Integrated Security=True;Connect Timeout=30;User Instance=True"
               );
            connection1.Open();
              // Формируем запрос к базе данных
            string xxx = TextBox1.Text;
            string sql = "insert into Table1 (Text) Values(@text)";
            SqlCommand command1 = new SqlCommand(sql, connection1);
            var param = command1.CreateParameter();
            param.ParameterName = "@text";
            param.Value = xxx;
            command1.Parameters.Add(param);

            SqlDataReader dataReader1 = command1.ExecuteReader();
            dataReader1.Close();
            connection1.Close();
        }
        // function to get users name to personalize
        public string getmypersonalName(string membername)
        {
            string result = membername;

            using (SqlConnection Userloggin_Conn = new SqlConnection(forgotconnStr))
            {

                using (SqlCommand Userloggin_cmd = new SqlCommand("dbo.sp_RFP_GetNameofUserID", Userloggin_Conn))
                {
                    Userloggin_cmd.CommandText = "dbo.sp_RFP_GetNameofUserID";
                    Userloggin_cmd.CommandType = CommandType.StoredProcedure;
                    Userloggin_cmd.CommandTimeout = 0;

                    SqlParameter Userloggin_Param = Userloggin_cmd.CreateParameter();
                    Userloggin_Param.ParameterName = "@szRegisterEmail";
                    Userloggin_Param.Direction = ParameterDirection.Input;
                    Userloggin_Param.SqlDbType = SqlDbType.NVarChar;
                    Userloggin_Param.Value = membername;

                    Userloggin_cmd.Parameters.Add(Userloggin_Param);

                    Userloggin_Conn.Open();

                    using (SqlDataReader Userloggin_Reader = Userloggin_cmd.ExecuteReader())
                    {
                        if (Userloggin_Reader.Read())
                        {
                            result = Userloggin_Reader["szRegisterName"].ToString();

                        }
                    }
                }
            }

            return result;
        }
Example #25
0
 public bool Action_ExecuteForDataReader(Data_SqlSPEntry activeSPEntry, out SqlDataReader sqlDataReader)
 {
     sqlDataReader = null;
     try
     {
         if (activeSPEntry == null)
             return false;
         else
         {
             if (activeconnection != null)
             {
                 SqlCommand cmd = new SqlCommand();
                 cmd.CommandText = activeSPEntry.SPName;
                 cmd.CommandType = CommandType.StoredProcedure;
                 foreach (SqlParameter activeParameter in activeSPEntry.ActiveParameters)
                 {
                     SqlParameter newParameter = cmd.CreateParameter();
                     newParameter.ParameterName = activeParameter.ParameterName;
                     newParameter.DbType = activeParameter.DbType;
                     newParameter.Direction = activeParameter.Direction;
                     newParameter.Value = activeParameter.Value;
                     cmd.Parameters.Add(newParameter);
                 }
                 cmd.Connection = activeconnection;
                 sqlDataReader = cmd.ExecuteReader();
                 return true;
             }
             else
                 return false;
         }
     }
     catch
     {
         return false;
     }
 }
Example #26
0
        /// <summary>
        /// カラム定義を取得する
        /// </summary>
        /// <param name="tableName">カラム定義を取得したいテーブルの名称</param>
        /// <returns></returns>
        public List<ColumnDefinition> GetDefinitions(string tableName)
        {
            List<ColumnDefinition> definitions = new List<ColumnDefinition> { };

            // SQL文を生成する
            StringBuilder query = new StringBuilder();
            query.Append("SELECT c.name AS COLUMN_NAME,tp.name AS COLUMN_TYPE, c.max_length AS COLUMN_MAX_LENGTH, c.max_length AS COLUMN_MAX_LENGTH, c.is_identity AS IS_IDENTITY, c.is_nullable AS IS_NULLABLE");
            query.Append(" FROM sys.tables t, sys.columns c, sys.types tp");
            query.Append(" WHERE t.name = @table_name AND t.object_id = c.object_id AND c.user_type_id = tp.user_type_id");

            // DBと接続
            using (SqlCommand cmd = new SqlCommand(query.ToString(), this.SqlConnection))
            {
                SqlParameter param = cmd.CreateParameter();
                param.ParameterName = "@table_name";
                param.SqlDbType = SqlDbType.Char;
                param.Direction = ParameterDirection.Input;
                param.Value = tableName;
                cmd.Parameters.Add(param);

                // SQL文の実行
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int index = 0;

                        ColumnDefinition definition = new ColumnDefinition();
                        if (!reader.IsDBNull(index))
                        {
                            definition.ColumnName = reader.GetString(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.TypeName = reader.GetString(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.Length = reader.GetInt16(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.IsIdentity = reader.GetInt16(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.IsNullableBool = reader.GetBoolean(index);
                        }
                        index++;

                        if (definition.ColumnName.Length > 0)
                        {
                            definitions.Add(definition);
                        }
                    }
                }
            }

            return definitions;
        }
Example #27
0
        /// <summary>
        /// カラム定義を取得する
        /// </summary>
        /// <param name="tableName">カラム定義を取得したいテーブルの名称</param>
        /// <returns></returns>
        private List<ColumnDefinition> _GetDefinitions(string tableName)
        {
            List<ColumnDefinition> definitions = new List<ColumnDefinition> { };

            // SQL文を生成する
            StringBuilder query = new StringBuilder();
            query.Append("EXEC sp_columns @table_name");

            // DBと接続
            using (SqlCommand cmd = new SqlCommand(query.ToString(), this.SqlConnection))
            {
                SqlParameter param = cmd.CreateParameter();
                param.ParameterName = "@table_name";
                param.SqlDbType = SqlDbType.Char;
                param.Direction = ParameterDirection.Input;
                param.Value = tableName;
                cmd.Parameters.Add(param);

                // SQL文の実行
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int index = 0;

                        ColumnDefinition definition = new ColumnDefinition();
                        if (!reader.IsDBNull(index))
                        {
                            definition.TableQualifier = reader.GetString(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.TableOwner = reader.GetString(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.TableName = reader.GetString(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.ColumnName = reader.GetString(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.DataType = reader.GetInt16(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.TypeName = reader.GetString(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.Precision = reader.GetInt32(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.Length = reader.GetInt32(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.Scale = reader.GetInt16(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.Radix = reader.GetInt16(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.Nullable = reader.GetInt16(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.Remarks = reader.GetString(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.ColumnDef = reader.GetString(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.SqlDataType = reader.GetInt16(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.SqlDateTimeSub = reader.GetInt16(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.CharOctetLength = reader.GetInt32(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.OriginalPosition = reader.GetInt32(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.IsNullable = reader.GetString(index);
                        }
                        index++;

                        if (!reader.IsDBNull(index))
                        {
                            definition.SsDataType = reader.GetByte(index);
                        }
                        index++;

                        if (definition.TableName.Length > 0)
                        {
                            definitions.Add(definition);
                        }
                    }
                }
            }

            return definitions;
        }
Example #28
0
        protected void UpdateHotelInfo()
        {
            using (SqlConnection HotelInfoOne_Conn = new SqlConnection(RFPDBconnStr))
            {
                using (SqlCommand HotelInfoOne_cmd = new SqlCommand("dbo.sp_RFP_HotelPatialInfoOne", HotelInfoOne_Conn))
                {
                    HotelInfoOne_cmd.CommandText = "dbo.sp_RFP_HotelPatialInfoOne";
                    HotelInfoOne_cmd.CommandType = CommandType.StoredProcedure;
                    HotelInfoOne_cmd.CommandTimeout = 0;

                    // first param
                    SqlParameter HotelInfoOne_Param = HotelInfoOne_cmd.CreateParameter();
                    HotelInfoOne_Param.ParameterName = "@LRFPID";
                    HotelInfoOne_Param.Direction = ParameterDirection.Input;
                    HotelInfoOne_Param.SqlDbType = SqlDbType.Int;
                    HotelInfoOne_Param.Value = Decrypt(HttpContext.Current.Request.QueryString["ORFP"].ToString(), "trappOvation");

                    HotelInfoOne_cmd.Parameters.Add(HotelInfoOne_Param);

                    // 7th param
                    HotelInfoOne_Param = HotelInfoOne_cmd.CreateParameter();
                    HotelInfoOne_Param.ParameterName = "@szAddress";
                    HotelInfoOne_Param.Direction = ParameterDirection.Input;
                    HotelInfoOne_Param.SqlDbType = SqlDbType.NVarChar;
                    HotelInfoOne_Param.Value = Hotel_Addr_One.Text.ToString();

                    HotelInfoOne_cmd.Parameters.Add(HotelInfoOne_Param);

                    // 7a th param
                    HotelInfoOne_Param = HotelInfoOne_cmd.CreateParameter();
                    HotelInfoOne_Param.ParameterName = "@szAddressTwo";
                    HotelInfoOne_Param.Direction = ParameterDirection.Input;
                    HotelInfoOne_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfoOne_Param.Value = Hotel_Addr_Two.Text.ToString();

                    HotelInfoOne_cmd.Parameters.Add(HotelInfoOne_Param);

                    // 8th param
                    HotelInfoOne_Param = HotelInfoOne_cmd.CreateParameter();
                    HotelInfoOne_Param.ParameterName = "@szCity";
                    HotelInfoOne_Param.Direction = ParameterDirection.Input;
                    HotelInfoOne_Param.SqlDbType = SqlDbType.NVarChar;
                    HotelInfoOne_Param.Value = Hotel_city.Text.ToString();

                    HotelInfoOne_cmd.Parameters.Add(HotelInfoOne_Param);

                    // 9th param
                    HotelInfoOne_Param = HotelInfoOne_cmd.CreateParameter();
                    HotelInfoOne_Param.ParameterName = "@szState";
                    HotelInfoOne_Param.Direction = ParameterDirection.Input;
                    HotelInfoOne_Param.SqlDbType = SqlDbType.NVarChar;
                    HotelInfoOne_Param.Value = HotelInfo_State.SelectedValue.ToString();

                    HotelInfoOne_cmd.Parameters.Add(HotelInfoOne_Param);

                    // 10th param
                    HotelInfoOne_Param = HotelInfoOne_cmd.CreateParameter();
                    HotelInfoOne_Param.ParameterName = "@szZipCode";
                    HotelInfoOne_Param.Direction = ParameterDirection.Input;
                    HotelInfoOne_Param.SqlDbType = SqlDbType.NVarChar;
                    HotelInfoOne_Param.Value = Hotel_zip_post_code.Text.ToString();

                    HotelInfoOne_cmd.Parameters.Add(HotelInfoOne_Param);

                    // 11th param
                    HotelInfoOne_Param = HotelInfoOne_cmd.CreateParameter();
                    HotelInfoOne_Param.ParameterName = "@szCountry";
                    HotelInfoOne_Param.Direction = ParameterDirection.Input;
                    HotelInfoOne_Param.SqlDbType = SqlDbType.NVarChar;
                    HotelInfoOne_Param.Value = Hotelcountry.SelectedValue.ToString();

                    HotelInfoOne_cmd.Parameters.Add(HotelInfoOne_Param);

                    // 2nd param
                    HotelInfoOne_Param = HotelInfoOne_cmd.CreateParameter();
                    HotelInfoOne_Param.ParameterName = "@szSabrePropertyNo";
                    HotelInfoOne_Param.Direction = ParameterDirection.Input;
                    HotelInfoOne_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfoOne_Param.Value = Sabre_PCode.Text.ToString();

                    HotelInfoOne_cmd.Parameters.Add(HotelInfoOne_Param);

                    // 3rd param
                    HotelInfoOne_Param = HotelInfoOne_cmd.CreateParameter();
                    HotelInfoOne_Param.ParameterName = "@szApolloPropertyNo";
                    HotelInfoOne_Param.Direction = ParameterDirection.Input;
                    HotelInfoOne_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfoOne_Param.Value = Apollo_Pcode.Text.ToString();

                    HotelInfoOne_cmd.Parameters.Add(HotelInfoOne_Param);

                    // 4th param
                    HotelInfoOne_Param = HotelInfoOne_cmd.CreateParameter();
                    HotelInfoOne_Param.ParameterName = "@szSabreChainCode";
                    HotelInfoOne_Param.Direction = ParameterDirection.Input;
                    HotelInfoOne_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfoOne_Param.Value = Sabre_Chain_Code.Text.ToString();

                    HotelInfoOne_cmd.Parameters.Add(HotelInfoOne_Param);

                    // 5th param
                    HotelInfoOne_Param = HotelInfoOne_cmd.CreateParameter();
                    HotelInfoOne_Param.ParameterName = "@szApolloChainCode";
                    HotelInfoOne_Param.Direction = ParameterDirection.Input;
                    HotelInfoOne_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfoOne_Param.Value = Apollo_Chain_Code.Text.ToString();

                    HotelInfoOne_cmd.Parameters.Add(HotelInfoOne_Param);

                    HotelInfoOne_Conn.Open();

                    // execute entry
                    HotelInfoOne_cmd.ExecuteNonQuery();
                }
            }

            // update table
            using (SqlConnection HotelInfo_Contacts_Conn = new SqlConnection(RFPDBconnStr))
            {
                using (SqlCommand HotelInfo_Contacts_cmd = new SqlCommand("dbo.sp_RFP_insertHotelInfo_Contacts", HotelInfo_Contacts_Conn))
                {
                    HotelInfo_Contacts_cmd.CommandText = "dbo.sp_RFP_insertHotelInfo_Contacts";
                    HotelInfo_Contacts_cmd.CommandType = CommandType.StoredProcedure;
                    HotelInfo_Contacts_cmd.CommandTimeout = 0;

                    // 1st param
                    SqlParameter HotelInfo_Contacts_Param = HotelInfo_Contacts_cmd.CreateParameter();
                    HotelInfo_Contacts_Param.ParameterName = "@LRFPID";
                    HotelInfo_Contacts_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Contacts_Param.SqlDbType = SqlDbType.Int;
                    HotelInfo_Contacts_Param.Value = Decrypt(HttpContext.Current.Request.QueryString["ORFP"].ToString(), "trappOvation");

                    HotelInfo_Contacts_cmd.Parameters.Add(HotelInfo_Contacts_Param);

                    // 2nd param
                    HotelInfo_Contacts_Param = HotelInfo_Contacts_cmd.CreateParameter();
                    HotelInfo_Contacts_Param.ParameterName = "@szName";
                    HotelInfo_Contacts_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Contacts_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfo_Contacts_Param.Value = Sales_Contact_Name.Text.ToString();

                    HotelInfo_Contacts_cmd.Parameters.Add(HotelInfo_Contacts_Param);

                    // 3rd param
                    HotelInfo_Contacts_Param = HotelInfo_Contacts_cmd.CreateParameter();
                    HotelInfo_Contacts_Param.ParameterName = "@szTitle";
                    HotelInfo_Contacts_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Contacts_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfo_Contacts_Param.Value = Sales_Contact_Title.Text.ToString();

                    HotelInfo_Contacts_cmd.Parameters.Add(HotelInfo_Contacts_Param);

                    // 4th param
                    HotelInfo_Contacts_Param = HotelInfo_Contacts_cmd.CreateParameter();
                    HotelInfo_Contacts_Param.ParameterName = "@szHotelPhone";
                    HotelInfo_Contacts_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Contacts_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfo_Contacts_Param.Value = Hotel_Main_phone.Text.ToString();

                    HotelInfo_Contacts_cmd.Parameters.Add(HotelInfo_Contacts_Param);

                    // 5th param
                    HotelInfo_Contacts_Param = HotelInfo_Contacts_cmd.CreateParameter();
                    HotelInfo_Contacts_Param.ParameterName = "@szPhone";
                    HotelInfo_Contacts_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Contacts_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfo_Contacts_Param.Value = Sales_Contact_Telephone.Text.ToString();

                    HotelInfo_Contacts_cmd.Parameters.Add(HotelInfo_Contacts_Param);

                    // 6th param
                    HotelInfo_Contacts_Param = HotelInfo_Contacts_cmd.CreateParameter();
                    HotelInfo_Contacts_Param.ParameterName = "@szFax";
                    HotelInfo_Contacts_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Contacts_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfo_Contacts_Param.Value = Sales_Contact_Fax.Text.ToString();

                    HotelInfo_Contacts_cmd.Parameters.Add(HotelInfo_Contacts_Param);

                    // 7th param
                    HotelInfo_Contacts_Param = HotelInfo_Contacts_cmd.CreateParameter();
                    HotelInfo_Contacts_Param.ParameterName = "@szEmail";
                    HotelInfo_Contacts_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Contacts_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfo_Contacts_Param.Value = Sales_Contact_Email.Text.ToString();

                    HotelInfo_Contacts_cmd.Parameters.Add(HotelInfo_Contacts_Param);

                    // 8th param
                    HotelInfo_Contacts_Param = HotelInfo_Contacts_cmd.CreateParameter();
                    HotelInfo_Contacts_Param.ParameterName = "@szDirector";
                    HotelInfo_Contacts_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Contacts_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfo_Contacts_Param.Value = Director_of_Sales.Text.ToString();

                    HotelInfo_Contacts_cmd.Parameters.Add(HotelInfo_Contacts_Param);

                    HotelInfo_Contacts_Conn.Open();

                    // execute entry
                    HotelInfo_Contacts_cmd.ExecuteNonQuery();
                }
            }

            // update table
            using (SqlConnection HotelInfo_Property_Conn = new SqlConnection(RFPDBconnStr))
            {
                using (SqlCommand HotelInfo_Property_cmd = new SqlCommand("dbo.sp_RFP_insertHotelInfo_Property", HotelInfo_Property_Conn))
                {
                    HotelInfo_Property_cmd.CommandText = "dbo.sp_RFP_insertHotelInfo_Property";
                    HotelInfo_Property_cmd.CommandType = CommandType.StoredProcedure;
                    HotelInfo_Property_cmd.CommandTimeout = 0;

                    // 1st param
                    SqlParameter HotelInfo_Property_Param = HotelInfo_Property_cmd.CreateParameter();
                    HotelInfo_Property_Param.ParameterName = "@LRFPID";
                    HotelInfo_Property_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Property_Param.SqlDbType = SqlDbType.Int;
                    HotelInfo_Property_Param.Value = Decrypt(HttpContext.Current.Request.QueryString["ORFP"].ToString(), "trappOvation");

                    HotelInfo_Property_cmd.Parameters.Add(HotelInfo_Property_Param);

                    // 2nd param
                    HotelInfo_Property_Param = HotelInfo_Property_cmd.CreateParameter();
                    HotelInfo_Property_Param.ParameterName = "@szPrefHotelName";
                    HotelInfo_Property_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Property_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfo_Property_Param.Value = Hotel_Name.Text.ToString();

                    HotelInfo_Property_cmd.Parameters.Add(HotelInfo_Property_Param);

                    // 2and param
                    HotelInfo_Property_Param = HotelInfo_Property_cmd.CreateParameter();
                    HotelInfo_Property_Param.ParameterName = "@szGeneralMgr";
                    HotelInfo_Property_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Property_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfo_Property_Param.Value = Hotel_general_mgr.Text.ToString();

                    HotelInfo_Property_cmd.Parameters.Add(HotelInfo_Property_Param);

                    // 3rd param
                    HotelInfo_Property_Param = HotelInfo_Property_cmd.CreateParameter();
                    HotelInfo_Property_Param.ParameterName = "@szWebSite";
                    HotelInfo_Property_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Property_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfo_Property_Param.Value = Hotel_Web_Site.Text.ToString();

                    HotelInfo_Property_cmd.Parameters.Add(HotelInfo_Property_Param);

                    // 4th param
                    HotelInfo_Property_Param = HotelInfo_Property_cmd.CreateParameter();
                    HotelInfo_Property_Param.ParameterName = "@szDiamondRating";
                    HotelInfo_Property_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Property_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfo_Property_Param.Value = AAAating.SelectedValue.ToString();

                    HotelInfo_Property_cmd.Parameters.Add(HotelInfo_Property_Param);

                    // 5th param
                    HotelInfo_Property_Param = HotelInfo_Property_cmd.CreateParameter();
                    HotelInfo_Property_Param.ParameterName = "@szStarRating";
                    HotelInfo_Property_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Property_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfo_Property_Param.Value = MobileStaRate.SelectedValue.ToString();

                    HotelInfo_Property_cmd.Parameters.Add(HotelInfo_Property_Param);

                    // 5th param
                    HotelInfo_Property_Param = HotelInfo_Property_cmd.CreateParameter();
                    HotelInfo_Property_Param.ParameterName = "@szHotelDesc";
                    HotelInfo_Property_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Property_Param.SqlDbType = SqlDbType.NVarChar;
                    HotelInfo_Property_Param.Value = Hotel_brief_descipt.Text.ToString();

                    HotelInfo_Property_cmd.Parameters.Add(HotelInfo_Property_Param);

                    // 6th param
                    HotelInfo_Property_Param = HotelInfo_Property_cmd.CreateParameter();
                    HotelInfo_Property_Param.ParameterName = "@szEnvCertPrg";
                    HotelInfo_Property_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Property_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfo_Property_Param.Value = Enviroment_Program.Text.ToString();

                    HotelInfo_Property_cmd.Parameters.Add(HotelInfo_Property_Param);

                    // 7th param
                    HotelInfo_Property_Param = HotelInfo_Property_cmd.CreateParameter();
                    HotelInfo_Property_Param.ParameterName = "@szRecyclingPrg";
                    HotelInfo_Property_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Property_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfo_Property_Param.Value = RadioButtonList_avtiveRecycle.SelectedValue.ToString();

                    HotelInfo_Property_cmd.Parameters.Add(HotelInfo_Property_Param);

                    // 7th param
                    HotelInfo_Property_Param = HotelInfo_Property_cmd.CreateParameter();
                    HotelInfo_Property_Param.ParameterName = "@szUtilEnvRespCleaners";
                    HotelInfo_Property_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Property_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfo_Property_Param.Value = RadioButtonList_Property_Responsible_Cleaners.SelectedValue.ToString();

                    HotelInfo_Property_cmd.Parameters.Add(HotelInfo_Property_Param);

                    // 8th param
                    HotelInfo_Property_Param = HotelInfo_Property_cmd.CreateParameter();
                    HotelInfo_Property_Param.ParameterName = "@szActiveWaterCnsvPrg";
                    HotelInfo_Property_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Property_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfo_Property_Param.Value = RadioButtonList_Property_WaterConserve.SelectedValue.ToString();

                    HotelInfo_Property_cmd.Parameters.Add(HotelInfo_Property_Param);

                    // 8th param
                    HotelInfo_Property_Param = HotelInfo_Property_cmd.CreateParameter();
                    HotelInfo_Property_Param.ParameterName = "@szDirectorOfSales";
                    HotelInfo_Property_Param.Direction = ParameterDirection.Input;
                    HotelInfo_Property_Param.SqlDbType = SqlDbType.VarChar;
                    HotelInfo_Property_Param.Value = Director_of_Sales.Text.ToString();

                    HotelInfo_Property_cmd.Parameters.Add(HotelInfo_Property_Param);

                    HotelInfo_Property_Conn.Open();

                    // execute entry
                    HotelInfo_Property_cmd.ExecuteNonQuery();

                }
            }
        }
Example #29
0
        //===============================================================
        // Function: Add
        //===============================================================
        public void Add()
        {
            m_GUID = System.Guid.NewGuid().ToString();

            SqlConnection conn = new SqlConnection(GlobalSettings.connectionString);
            try
            {
                conn.Open();

                SqlCommand cmd = new SqlCommand("spAddUser", conn);
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add("@GUID", SqlDbType.NVarChar, 50).Value = m_GUID;
                cmd.Parameters.Add("@EmailAddress", SqlDbType.NVarChar, 200).Value = m_emailAddress;
                cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar, 200).Value = m_firstName;
                cmd.Parameters.Add("@LastName", SqlDbType.NVarChar, 200).Value = m_lastName;
                cmd.Parameters.Add("@HomeTown", SqlDbType.NVarChar, 200).Value = m_homeTown;
                if (m_birthday == DateTime.MinValue)
                {
                    cmd.Parameters.Add("@Birthday", SqlDbType.DateTime).Value = DBNull.Value;
                }
                else
                {
                    cmd.Parameters.Add("@Birthday", SqlDbType.DateTime).Value = m_birthday;
                }
                cmd.Parameters.Add("@Gender", SqlDbType.NChar, 1).Value = m_gender;
                cmd.Parameters.Add("@CountryID", SqlDbType.Int).Value = m_countryID;
                cmd.Parameters.Add("@LanguageID", SqlDbType.Int).Value = m_languageID;
                cmd.Parameters.Add("@TimezoneID", SqlDbType.Int).Value = m_timezoneID;
                cmd.Parameters.Add("@AvatarNumber", SqlDbType.Int).Value = m_avatarNumber;
                cmd.Parameters.Add("@ProfileText", SqlDbType.NVarChar, 200).Value = m_profileText;
                cmd.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = DateTime.Now;
                cmd.Parameters.Add("@CreatedByFullName", SqlDbType.NVarChar, 200).Value = m_loggedInUser;
                cmd.Parameters.Add("@LastUpdatedDate", SqlDbType.DateTime).Value = DateTime.Now;
                cmd.Parameters.Add("@LastUpdatedByFullName", SqlDbType.NVarChar, 200).Value = m_loggedInUser;
                cmd.Parameters.Add("@FacebookUserID", SqlDbType.BigInt).Value = (m_facebookUserID == -1 ? (object)DBNull.Value : (object)m_facebookUserID);

                SqlParameter paramUserID = cmd.CreateParameter();
                paramUserID.ParameterName = "@UserID";
                paramUserID.SqlDbType = SqlDbType.Int;
                paramUserID.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(paramUserID);

                cmd.ExecuteNonQuery();
                m_userID = (int)paramUserID.Value;

                //ReadUserDetails();
            }
            catch (Exception ex)
            {
                ErrorLog errorLog = new ErrorLog();
                errorLog.WriteLog("SedogoUser", "Add", ex.Message, logMessageLevel.errorMessage);
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
Example #30
0
        /// <summary>
        /// Saves a Instructor in the database.
        /// </summary>
        /// <param name="myInstructor">The Instructor to store.</param>
        /// <returns>The new ID if the Instructor is new in the database or the existing ID when an record was updated.</returns>
        public static int Save(Instructor myInstructor)
        {
            int result = 0;
            using (SqlConnection myConnection = new SqlConnection(AppSettings.ConnectionString))
            {
                SqlCommand myCommand = new SqlCommand("spSaveInstructor", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;
                myCommand.Parameters.AddWithValue("@IID", myInstructor.IId);
                myCommand.Parameters.AddWithValue("@Password", myInstructor.Password);
                myCommand.Parameters.AddWithValue("@FName", myInstructor.FName);
                if (String.IsNullOrEmpty(myInstructor.MI))
                {
                    myCommand.Parameters.AddWithValue("@MI", DBNull.Value);
                }
                else
                {
                    myCommand.Parameters.AddWithValue("@MI", myInstructor.MI);
                }
                myCommand.Parameters.AddWithValue("@LName", myInstructor.LName);
                myCommand.Parameters.AddWithValue("@Street", myInstructor.Street);
                myCommand.Parameters.AddWithValue("@City", myInstructor.City);
                myCommand.Parameters.AddWithValue("@State", myInstructor.State);
                myCommand.Parameters.AddWithValue("@Zipcode", myInstructor.Zipcode);

                DbParameter retValue = myCommand.CreateParameter();
                retValue.Direction = ParameterDirection.ReturnValue;
                myCommand.Parameters.Add(retValue);

                myConnection.Open();
                myCommand.ExecuteNonQuery();
                result = Convert.ToInt32(retValue.Value);
                myConnection.Close();
            }
            return result;
        }