/// <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;
}