public static SqlServerStoredProcedure SqlServerStoredProcedureCollectionToStoreProcedure(DataRow sqlServerStoredProcedure, DataTable sqlServerParametersColletions)
{
SqlServerStoredProcedure storedProcedure = new SqlServerStoredProcedure();
storedProcedure.Name = sqlServerStoredProcedure["routine_name"].ToString();
foreach (DataRow row in sqlServerParametersColletions.Rows)
{
SqlServerParameter parameter = new SqlServerParameter();
parameter.DBName = row["parameter_name"].ToString();
parameter.Direction = SqlServerTranslatorHelper.GetDirection(row["parameter_mode"].ToString());
SqlServerDbDataType dbDataType = new SqlServerDbDataType();
dbDataType.ProviderType = SqlServerDataTypeConverter.String2DatabaseType(row["data_type"].ToString());
dbDataType.Type = SqlServerDataTypeConverter.DatabaseType2NetType(dbDataType.ProviderType);
dbDataType.Precision = SqlServerTranslatorHelper.GetPrecision(row["numeric_precision"]);
dbDataType.Scale = SqlServerTranslatorHelper.GetScale(row["numeric_scale"]);
dbDataType.Size = SqlServerTranslatorHelper.GetParameterSize(dbDataType.ProviderType, row);
parameter.DbDataType = dbDataType;
storedProcedure.Parameters.Add(parameter);
}
return(storedProcedure);
}
public void ExecuteNonQuery(string query, SqlServerParameter parameter, ExecuteTypeEnum type)
{
using (_con = new SqlConnection(ConnectionString))
{
_con.Open();
_cmd = new SqlCommand
{
Connection = _con,
CommandType = type == ExecuteTypeEnum.StoredProcedure
? CommandType.StoredProcedure
: CommandType.Text,
CommandText = query,
CommandTimeout = ConnectDbTimeOut
};
if (parameter != null)
{
_cmd.Parameters.AddRange(parameter.ToArray());
}
_cmd.ExecuteNonQuery();
_con.Dispose();
if (_con.State == ConnectionState.Open)
{
_con.Close();
}
}
}
public DataTable ExecuteToTable(string query, SqlServerParameter parameter, ExecuteTypeEnum type)
{
using (_con = new SqlConnection(ConnectionString))
{
_con.Open();
_cmd = new SqlCommand
{
Connection = _con
};
_cmd.Parameters.Clear();
_cmd.CommandType = type == ExecuteTypeEnum.StoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
_cmd.CommandText = query;
_cmd.CommandTimeout = ConnectDbTimeOut;
if (parameter != null)
{
_cmd.Parameters.AddRange(parameter.ToArray());
}
_adapter = new SqlDataAdapter(_cmd);
DataTable tbl = new DataTable();
_adapter.Fill(tbl);
_adapter.Dispose();
if (_con.State == ConnectionState.Open)
{
_con.Close();
}
return(tbl);
}
}
public T ExecuteScalarFunction <T>(string query, SqlServerParameter parameter, ExecuteTypeEnum type)
{
using (_con = new SqlConnection(ConnectionString))
{
_cmd = new SqlCommand
{
Connection = _con
};
_cmd.Parameters.Clear();
_cmd.CommandType = type == ExecuteTypeEnum.StoredProcedure ? CommandType.StoredProcedure : CommandType.Text;
_cmd.CommandText = query;
_cmd.CommandTimeout = ConnectDbTimeOut;
if (parameter != null)
{
_cmd.Parameters.AddRange(parameter.ToArray());
}
SqlParameter returnValue = _cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Text);
returnValue.Direction = ParameterDirection.ReturnValue;
_con.Open();
_cmd.ExecuteNonQuery();
if (_con.State == ConnectionState.Open)
{
_con.Close();
}
return((T)returnValue.Value);
}
}
public void TestSetItemByName()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
SqlServerParameterCollection coll = command.Parameters;
SqlServerParameter param = command.CreateParameter();
param.ParameterName = "Param1";
coll.Add(param);
Assert.IsTrue(coll.Contains(param));
Assert.IsTrue(coll.Contains("Param1"));
Assert.AreEqual(param, coll[0]);
Assert.AreEqual(param, coll["Param1"]);
Assert.AreEqual(param, ((IList)coll)[0]);
param = command.CreateParameter();
param.ParameterName = "Param2";
coll["Param1"] = param;
Assert.AreEqual(1, coll.Count);
Assert.IsTrue(coll.Contains(param));
Assert.IsTrue(coll.Contains("Param2"));
Assert.AreEqual(param, coll[0]);
Assert.AreEqual(param, coll["Param2"]);
Assert.AreEqual(param, ((IList)coll)[0]);
}
}
public void TestIListRemove()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
SqlServerParameterCollection coll = command.Parameters;
SqlServerParameter param = command.CreateParameter();
param.ParameterName = "Param1";
coll.Add(param);
Assert.IsTrue(coll.Contains(param));
Assert.IsTrue(coll.Contains("Param1"));
Assert.AreEqual(1, coll.Count);
SqlServerParameter param2 = command.CreateParameter();
param2.ParameterName = "Param2";
coll.Add(param2);
Assert.IsTrue(coll.Contains(param2));
Assert.IsTrue(coll.Contains("Param2"));
Assert.AreEqual(2, coll.Count);
((IList)coll).Remove(param);
Assert.IsTrue(coll.Contains(param2));
Assert.IsTrue(coll.Contains("Param2"));
Assert.IsFalse(coll.Contains(param));
Assert.IsFalse(coll.Contains("Param1"));
Assert.AreEqual(1, coll.Count);
}
}
private List <Parameter> GetProcedureResultSchema(DataTable schema)
{
List <Parameter> parameters = new List <Parameter>();
foreach (DataRow row1 in schema.Rows)
{
Type type1 = (Type)row1["DataType"];
int num1 = -1;
if (type1 == typeof(string))
{
num1 = (int)row1["ColumnSize"];
}
SqlServerParameter parameter = new SqlServerParameter();
parameter.DBName = SqlServerTranslatorHelper.GetQualifiedName((string)row1["ColumnName"]);
parameter.Direction = ParameterDirection.ReturnValue;
SqlServerDbDataType dbDataType = new SqlServerDbDataType();
dbDataType.Size = num1;
dbDataType.ProviderType = SqlServerDataTypeConverter.SqlDbType2DatabaseType(row1["ProviderType"]);
dbDataType.Type = type1;
parameter.DbDataType = dbDataType;
parameters.Add(parameter);
}
return(parameters);
}
public T ExecuteScalar <T>(string query, SqlServerParameter parameter, ExecuteTypeEnum type)
{
using (_con = new SqlConnection(ConnectionString))
{
_con.Open();
_cmd = new SqlCommand
{
Connection = _con,
CommandType = type == ExecuteTypeEnum.StoredProcedure
? CommandType.StoredProcedure
: CommandType.Text,
CommandText = query,
CommandTimeout = ConnectDbTimeOut
};
if (parameter != null)
{
_cmd.Parameters.AddRange(parameter.ToArray());
}
object obj = _cmd.ExecuteScalar();
_con.Close();
return((T)obj);
}
}
public void TestValueDbNull()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
SqlServerParameter param = command.CreateParameter();
param.Value = DBNull.Value;
Assert.IsNull(param.Value);
}
}
public void TestParameterName()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
SqlServerParameter param = command.CreateParameter();
param.ParameterName = "Param1";
Assert.AreEqual("Param1", param.ParameterName);
}
}
public void TestDirection()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
SqlServerParameter param = command.CreateParameter();
param.Direction = ParameterDirection.Output;
Assert.AreEqual(ParameterDirection.Output, param.Direction);
}
}
public void TestDbType()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
SqlServerParameter param = command.CreateParameter();
param.DbType = DbType.String;
Assert.AreEqual(DbType.String, param.DbType);
}
}
public bool Delete(int id)
{
var param = new SqlServerParameter();
param.Add_Parameter("@_ItemId", id);
var data = _database.ExecuteScalar <int>("Article_Delete", param, ExecuteTypeEnum.StoredProcedure);
return(data == 1);
}
public bool SaveIntroduction(Introduction introduction)
{
var param = new SqlServerParameter();
param.Add_Parameter("@_MainContent", introduction.MainContent);
var data = _database.ExecuteScalar <int>("Introduction_Save", param, ExecuteTypeEnum.StoredProcedure);
return(data == 1);
}
public void TestPrecision()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
SqlServerParameter param = command.CreateParameter();
param.DbType = DbType.Decimal;
param.Precision = 5;
Assert.AreEqual(0, param.Precision);
}
}
/// <summary>
/// Ajoute les paramètres d'insertion.
/// </summary>
/// <param name="bean">Bean à insérér.</param>
/// <param name="beanDefinition">Définition du bean.</param>
/// <param name="parameters">Paramètres de la commande SQL.</param>
/// <param name="columnSelector">Selecteur de colonnes à mettre à jour ou à ignorer.</param>
protected void AddInsertParameters(T bean, BeanDefinition beanDefinition, SqlServerParameterCollection parameters, ColumnSelector columnSelector)
{
if (beanDefinition == null)
{
throw new ArgumentNullException("beanDefinition");
}
foreach (BeanPropertyDescriptor property in beanDefinition.Properties)
{
if (property.IsPrimaryKey || property.MemberName == null || (columnSelector != null && !columnSelector.ColumnList.Contains(property.MemberName)))
{
continue;
}
object value = property.GetValue(bean);
if (value != null)
{
ExtendedValue extValue = value as ExtendedValue;
if (extValue != null)
{
value = extValue.Value;
}
}
IStoreRule rule = this.GetStoreRule(property.PropertyName);
ValueRule valueRule = null;
if (rule != null)
{
valueRule = rule.GetInsertValue(value);
}
if (valueRule != null)
{
switch (valueRule.Action)
{
case ActionRule.DoNothing:
continue;
case ActionRule.Update:
value = valueRule.Value;
break;
default:
throw new NotSupportedException();
}
}
// Ajout du paramètre en entrée de la commande.
SqlServerParameter parameter = AddParameter(parameters, property, value);
if (property.PrimitiveType == typeof(byte[]))
{
parameter.DbType = DbType.Binary;
}
}
}
public bool Save(Utility utility)
{
var param = new SqlServerParameter();
param.Add_Parameter("@_Type", (int)utility.Type);
param.Add_Parameter("@_Description", utility.Description);
param.Add_Parameter("@_ItemId", utility.UtilityId);
var data = _database.ExecuteScalar <int>("Utilities_Save", param, ExecuteTypeEnum.StoredProcedure);
return(data == 1);
}
public bool Save(SystemMenu systemMenu)
{
var param = new SqlServerParameter();
param.Add_Parameter("@_Name", systemMenu.MenuItemName);
param.Add_Parameter("@_UrlImage", systemMenu.UrlImage);
param.Add_Parameter("@_IsActive", systemMenu.IsActive);
param.Add_Parameter("@_Id", systemMenu.MenuItemId);
var data = _database.ExecuteScalar <int>("SystemMenu_Save", param, ExecuteTypeEnum.StoredProcedure);
return(data == 1);
}
public void TestAdd()
{
using (TransactionScope tx = new TransactionScope(TransactionScopeOption.Required)) {
SqlServerCommand command = new SqlServerCommand("test", "procTest");
SqlServerParameterCollection coll = command.Parameters;
SqlServerParameter param = command.CreateParameter();
param.ParameterName = "Param1";
coll.Add(param);
Assert.IsTrue(coll.Contains(param));
Assert.IsTrue(coll.Contains("Param1"));
Assert.IsTrue(((IList)coll).Contains(param));
}
}
public bool CreateUser(User user)
{
var param = new SqlServerParameter();
param.Add_Parameter("@_Username", user.Username);
param.Add_Parameter("@_Email", user.Email);
param.Add_Parameter("@_Password", user.Password);
param.Add_Parameter("@_UserType", (int)user.UserType);
var data = _database.ExecuteScalar <int>("User_Create", param, ExecuteTypeEnum.StoredProcedure);
return(data == 1);
}
public bool Save(Contact contact)
{
var param = new SqlServerParameter();
param.Add_Parameter("@_Name", contact.Name);
param.Add_Parameter("@_Phone", contact.Phone);
param.Add_Parameter("@_Email", contact.Email);
param.Add_Parameter("@_Message", contact.Message);
var data = _database.ExecuteScalar <int>("Contact_Save", param, ExecuteTypeEnum.StoredProcedure);
return(data == 1);
}
public IEnumerable <Reviews> FetchTopCustomer(int top)
{
var sqlParam = new SqlServerParameter();
sqlParam.Add_Parameter("@top", top);
var result = _database.ExecuteToTable("SELECT TOP (@top) * FROM Reviews WHERE IsActive =1", sqlParam, ExecuteTypeEnum.SqlQuery);
if (result != null && result.Rows.Count > 0)
{
return(SqlMapper <Reviews> .Map(result));
}
return(null);
}
public News GetById(int id)
{
var param = new SqlServerParameter();
param.Add_Parameter("@_Id", id);
var data = _database.ExecuteToTable($"SELECT * FROM News WHERE NewId = @_Id", param, ExecuteTypeEnum.SqlQuery);
if (data != null && data.Rows.Count > 0)
{
return(SqlMapper <News> .Map(data).FirstOrDefault());
}
return(new News());
}
public Order GetOrderById(int id)
{
var param = new SqlServerParameter();
param.Add_Parameter("@_Id", id);
var data = _database.ExecuteToTable($"SELECT * FROM [Order] WHERE OrderId=@_Id", param, ExecuteTypeEnum.SqlQuery);
if (data != null && data.Rows.Count > 0)
{
return(SqlMapper <Order> .Map(data.Rows[0]));
}
return(new Order());
}
public bool Save(Article article)
{
var param = new SqlServerParameter();
param.Add_Parameter("@_ArticleId", article.ArticleId);
param.Add_Parameter("@_ArticleTitle", article.ArticleTitle);
param.Add_Parameter("@_Sapo", article.Sapo);
param.Add_Parameter("@_Avatar", article.Avatar);
param.Add_Parameter("@_Content", article.Content);
param.Add_Parameter("@_IsPolicy", article.IsPolicy);
var data = _database.ExecuteScalar <int>("Article_Save", param, ExecuteTypeEnum.StoredProcedure);
return(data == 1);
}
private User GetUser(User user)
{
var param = new SqlServerParameter();
param.Add_Parameter("@_UsernameOrEmail", user.Username);
var data = _database.ExecuteToTable("User_Get", param, ExecuteTypeEnum.StoredProcedure);
if (data != null && data.Rows.Count > 0)
{
return(SqlMapper <User> .Map(data)[0]);
}
return(null);
}
public bool SaveSlide(SystemSlide systemSlide)
{
var param = new SqlServerParameter();
param.Add_Parameter("@_SlideId", systemSlide.SlideId);
param.Add_Parameter("@_SlideName", systemSlide.SlideName);
param.Add_Parameter("@_LinkUrl", systemSlide.LinkUrl);
param.Add_Parameter("@_UrlImage", systemSlide.UrlImage);
param.Add_Parameter("@_IsActive", systemSlide.IsActive);
param.Add_Parameter("@_OrderItem", systemSlide.OrderItem);
var data = _database.ExecuteScalar <int>("SystemSlide_Save", param, ExecuteTypeEnum.StoredProcedure);
return(data == 1);
}
public SystemSlide GetSlideById(int id)
{
var param = new SqlServerParameter();
param.Add_Parameter("@_SlideId", id);
var data = _database.ExecuteToTable($"SELECT * FROM SystemSlide WHERE SlideId = @_SlideId", param, ExecuteTypeEnum.SqlQuery);
if (data != null && data.Rows.Count > 0)
{
return(SqlMapper <SystemSlide> .Map(data).FirstOrDefault());
}
return(new SystemSlide());
}
public bool Save(RoomType roomType)
{
var param = new SqlServerParameter();
param.Add_Parameter("@_Name", roomType.TypeName);
param.Add_Parameter("@_UrlImage", roomType.UrlImage);
param.Add_Parameter("@_IsActive", roomType.IsActive);
param.Add_Parameter("@_Id", roomType.RoomTypeId);
param.Add_Parameter("@_Price", roomType.Price);
param.Add_Parameter("@_IsHot", roomType.IsHot);
param.Add_Parameter("@_Description", roomType.Description);
var data = _database.ExecuteScalar <int>("RoomType_Save", param, ExecuteTypeEnum.StoredProcedure);
return(data == 1);
}
public Article GetById(int id)
{
var param = new SqlServerParameter();
var result = new Article();
param.Add_Parameter("@_ItemId", id);
var data = _database.ExecuteToTable("Article_Get", param, ExecuteTypeEnum.StoredProcedure);
if (data != null && data.Rows.Count > 0)
{
result = SqlMapper <Article> .Map(data).FirstOrDefault();
}
return(result);
}
public static SqlServerStoredProcedure SqlServerStoredProcedureCollectionToStoreProcedure(DataRow sqlServerStoredProcedure, DataTable sqlServerParametersColletions)
{
SqlServerStoredProcedure storedProcedure = new SqlServerStoredProcedure();
storedProcedure.Name = sqlServerStoredProcedure["routine_name"].ToString();
foreach(DataRow row in sqlServerParametersColletions.Rows)
{
SqlServerParameter parameter = new SqlServerParameter();
parameter.DBName = row["parameter_name"].ToString();
parameter.Direction = SqlServerTranslatorHelper.GetDirection(row["parameter_mode"].ToString());
SqlServerDbDataType dbDataType = new SqlServerDbDataType();
dbDataType.ProviderType = SqlServerDataTypeConverter.String2DatabaseType(row["data_type"].ToString());
dbDataType.Type = SqlServerDataTypeConverter.DatabaseType2NetType(dbDataType.ProviderType);
dbDataType.Precision = SqlServerTranslatorHelper.GetPrecision(row["numeric_precision"]);
dbDataType.Scale = SqlServerTranslatorHelper.GetScale(row["numeric_scale"]);
dbDataType.Size = SqlServerTranslatorHelper.GetParameterSize(dbDataType.ProviderType, row);
parameter.DbDataType = dbDataType;
storedProcedure.Parameters.Add(parameter);
}
return storedProcedure;
}
