/// <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); }
/// <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); }
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); } }
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; }
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; }
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); } } } }
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; } }
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); }
/// <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; }
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; } } }
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); }
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); } } } }
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; }
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); } } }
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; }
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; }
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; }
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; } }
/// <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; }
/// <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; }
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(); } } }
//=============================================================== // 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(); } }
/// <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; }