public UserProfile GetByUsername(string username)
{
var query = "select * from jl_user where username=@username";
var conn = DbConnectionFactory.CreateConnection();
return(conn.Query <UserProfile>(query, new { username = username }).FirstOrDefault());
}
public IEnumerable <Article> GetList()
{
var conn = DbConnectionFactory.CreateConnection();
var sql = "select * from Article";
var data = conn.Query <Article>(sql);
return(data);
}
public Article GetById(int id)
{
var query = "select * from Article where AutoId=@id";
var conn = DbConnectionFactory.CreateConnection();
return(conn.Query <Article>(query, new { id = id }).FirstOrDefault());
}
public UserProfile GetModel(int id)
{
var query = "select * from jl_user where UserId=id";
var conn = DbConnectionFactory.CreateConnection();
return(conn.Query <UserProfile>(query, new { UserId = id }).FirstOrDefault());
}
public Setting GetById(int id)
{
var query = "select * from setting where AutoId=@id";
var conn = DbConnectionFactory.CreateConnection();
return(conn.Query <Setting>(query, new { id = id }).FirstOrDefault());
}
public int Insert(Article model)
{
var connection = DbConnectionFactory.CreateConnection();
var id = connection.Query <int>(@"Insert into Article(Title,Brief,Content,Picture,AddTime,Tags,PageViews,SortIndex,Status)
values (@Title,@Brief,@Content,@Picture,@AddTime,@Tags,@PageViews,@SortIndex,@Status);SELECT LAST_INSERT_ID()",
model).FirstOrDefault();
return(id);
}
public articleRepository(IOptionsSnapshot <DbOption> options)
{
_dbOption = options.Get("CzarCms");
if (_dbOption == null)
{
throw new ArgumentNullException(nameof(DbOption));
}
_dbConnection = DbConnectionFactory.CreateConnection(_dbOption.DbType, _dbOption.ConnectionString);
}
public int Insert(Franchisee model)
{
var connection = DbConnectionFactory.CreateConnection();
var id = connection.Query <int>(@"Insert into Franchisee(Name,Email,Weixin,Phone,Address,Remark,ApplyTime,ProcessTime,Status)
values (@Name,@Email,@Weixin,@Phone,@Address,@Remark,@ApplyTime,@ProcessTime,@Status);SELECT LAST_INSERT_ID()",
model).FirstOrDefault();
return(id);
}
public void Should_be_able_to_create_a_valid_connection()
{
var factory = new DbConnectionFactory();
using (var connection = factory.CreateConnection(DefaultProviderName, DefaultConnectionString))
{
Assert.IsNotNull(connection);
}
}
public int Insert(Banner model)
{
var connection = DbConnectionFactory.CreateConnection();
var id = connection.Query <int>(@"Insert into banner(Title,Desctiption,Picture,BackgroundColor,Status,SortIndex)
values (@Title,@Desctiption,@Picture,@BackgroundColor,@Status,@SortIndex)",
model).FirstOrDefault();
return(id);
}
public int Insert(Setting model)
{
var connection = DbConnectionFactory.CreateConnection();
var id = connection.Query <int>(@"Insert into setting(`Key`,`Value`)
values (@Key,@Value);SELECT LAST_INSERT_ID()",
model).FirstOrDefault();
return(id);
}
public int Insert(Product model)
{
var connection = DbConnectionFactory.CreateConnection();
var id = connection.Query <int>(@"Insert into Product(Name,Alias,BriefIntroduction,Description,Picture,RetailPrice,MarketPrice,PageViews,SortIndex,Status,IsRecommendAsNew)
values (@Name,@Alias,@BriefIntroduction,@Description,@Picture,@RetailPrice,@MarketPrice,@PageViews,@SortIndex,@Status,@IsRecommendAsNew);SELECT LAST_INSERT_ID()",
model).FirstOrDefault();
return(id);
}
public int Insert(UserProfile model)
{
var connection = DbConnectionFactory.CreateConnection();
var id = connection.Query <int>(@"Insert into Users(UserName,Cellphone,IsCellphoneConfirmed,Email,IsEmailConfirmed,RealName,NickName,Telephone,Birthday,Gender,QQ,Address,AddTime,RegUrl)
values (@UserName,@Cellphone,@IsCellphoneConfirmed,@Email,@IsEmailConfirmed,@RealName,@NickName,@Telephone,@Birthday,@Gender,@QQ,@Address,@AddTime,@RegUrl);SELECT LAST_INSERT_ID()",
model).FirstOrDefault();
return(id);
}
public int InsertProductCategory(ProductCategory model)
{
var connection = DbConnectionFactory.CreateConnection();
var id = connection.Query <int>(@"Insert into productcategory(Name,Alias,Picture,Path,Depth,ParentId,PageViews,SortIndex,Status)
values (@Name,@Alias,@Picture,@Path,@Depth,@ParentId,@PageViews,@SortIndex,@Status);SELECT LAST_INSERT_ID()",
model).FirstOrDefault();
return(id);
}
public IEnumerable <Setting> GetList()
{
var sql = "select * from setting";
using (var conn = DbConnectionFactory.CreateConnection())
{
return(conn.Query <Setting>(sql));
}
}
public int Insert(Material model)
{
var connection = DbConnectionFactory.CreateConnection();
var id = connection.Query <int>(@"Insert into Material(Title,Description,Picture,FileName,MaterialType,PageViews,SortIndex,Status,Url,AddTime)
values (@Title,@Description,@Picture,@FileName,@MaterialType,@PageViews,@SortIndex,@Status,@Url,@AddTime);SELECT LAST_INSERT_ID()",
model).FirstOrDefault();
return(id);
}
public User GetByEmail(string emailAddress = null)
{
using (IDbConnection connection = DbConnectionFactory.CreateConnection())
{
return(connection.GetSingle <User>
(
new DbParameter("EmailAddress", emailAddress)
));
}
}
public void Initialize()
{
_connection = DbConnectionFactory.CreateConnection("AppDbContext");
_dbContext = new AppDbContext(_connection);
_entityService.DbContext = _dbContext;
PropertyChangedEventManager.AddHandler(ShellViewModel, ShellViewModelPropertyChanged, "");
ShellViewModel.SaveCommand = _saveCommand;
}
public User Get(string userName)
{
using (IDbConnection connection = DbConnectionFactory.CreateConnection())
{
return(connection.GetSingle <User>
(
new DbParameter("UserName", userName)
));
}
}
public ProductCategory GetProductCategory(int id)
{
var query = "select pc.*,p.* from productcategory pc left join productcategorylink pcl on pc.AutoId = pcl.categoryid left join product p on pcl.productid = p.autoid where AutoId=@id";
var conn = DbConnectionFactory.CreateConnection();
return(conn.Query <ProductCategory, Product, ProductCategory>(query, (pc, p) => {
pc.Products.Add(p);
return pc;
}, new { id = id }).FirstOrDefault());
}
public void DeleteUserCategory(string userName, string categoryName)
{
using (IDbConnection connection = DbConnectionFactory.CreateConnection())
{
UserCategory userCategory = this.GetUserCategory(userName, categoryName, connection);
if (userCategory != null)
{
connection.Delete(userCategory);
}
}
}
public void DeleteTransaction(string userName, string transactionId)
{
using (IDbConnection connection = DbConnectionFactory.CreateConnection())
{
Transaction transaction = this.GetTransaction(userName, new Guid(transactionId), connection);
if (transaction != null)
{
connection.Delete(transaction);
}
}
}
public void DeleteUser(string userName)
{
using (IDbConnection connection = DbConnectionFactory.CreateConnection())
{
User user = this.GetUser(userName, connection);
if (user != null)
{
connection.Delete(user);
}
}
}
static void Main(string[] args)
{
string connString =
"server=localhost;port=5432;user id=postgres;password=root;database=imsdb;timeout=5;commandtimeout=5;";
DbConnectionFactory dbConnFactory = new DbConnectionFactory(connString);
ProductRepository repo = new ProductRepository();
// repo.Insert(new Product(){Name = "Sharpner", Price = 2.3}, dbConnFactory.CreateConnection());
var prodItem = repo.FindById(2, dbConnFactory.CreateConnection());
Console.WriteLine("Id: " + prodItem.Id + ", Name: " + prodItem.Result.Name + ", Price: " + prodItem.Result.Price);
prodItem = repo.FindByName("pant", dbConnFactory.CreateConnection());
Console.WriteLine("Id: " + prodItem.Id + ", Name: " + prodItem.Result.Name + ", Price: " + prodItem.Result.Price);
var list = repo.FindAll(dbConnFactory.CreateConnection()).Result;
foreach (var prod in list)
{
Console.WriteLine("Id: " + prod.Id + ", Name: " + prod.Name + ", Price: " + prod.Price);
}
}
public UserCategory[] GetUserCategories(string userName, string parentCategoryName)
{
using (IDbConnection connection = DbConnectionFactory.CreateConnection())
{
User user = this.GetUser(userName, connection);
return(connection.Get <UserCategory>
(
new DbParameter("UserId", user.UserId),
new DbParameter("ParentName", parentCategoryName)
));
}
}
public IEnumerable <Article> GetLatestArticle(int count)
{
var sqlBuilder = new StringBuilder();
sqlBuilder.Append(" select * from Article ");
sqlBuilder.Append(" where status=" + Consts.ArticleStatus_Published + " ");
sqlBuilder.Append(" order by addtime desc ");
sqlBuilder.Append(" limit " + count);
var conn = DbConnectionFactory.CreateConnection();
return(conn.Query <Article>(sqlBuilder.ToString()));
}
public void Put(string userName, [FromBody] User value)
{
using (IDbConnection connection = DbConnectionFactory.CreateConnection())
{
User user = connection.GetSingle <User>
(
new DbParameter("UserName", userName)
);
user.Put(value);
connection.Put(user);
}
}
public int Insert(Member model)
{
using (var connection = DbConnectionFactory.CreateConnection())
{
connection.Open();
var id = connection.Query <int>(@"Insert into Member(NickName,RealName,Description,Phone,Weixin,QQ,Email,Address,JoinTime,Picture,Words,SortIndex,Status)
values (@NickName,@RealName,@Description,@Phone,@Weixin,@QQ,@Email,@Address,@JoinTime,@Picture,@Words,@SortIndex,@Status);SELECT LAST_INSERT_ID()",
model).FirstOrDefault();
connection.Close();
return(id);
}
}
public IHttpActionResult Get(string userName, Guid transactionId)
{
using (IDbConnection connection = DbConnectionFactory.CreateConnection())
{
Transaction transaction = this.GetById(connection, userName, transactionId);
if (transaction == null)
{
throw new HttpResponseException(HttpStatusCode.NotFound);
}
return(this.Json(transaction));
}
}
public IHttpActionResult Post(string userName, [FromBody] Transaction value)
{
using (IDbConnection connection = DbConnectionFactory.CreateConnection())
{
User user = this.GetApplicationUser(connection, userName);
value.UserId = user.UserId;
connection.Put(value);
return(this.Json(value));
// TODO: see if I can find a way to return 201 (Created) with a Json value
//return this.Created(string.Format("api/users/{0}/transaction/{1}", userName, value.TransactionId), value);
}
}
public static void DefaultFactoryCreatesSqlConnections()
{
var factory = new DbConnectionFactory(LocalDbConnection);
var connection = factory.CreateConnection();
Assert.IsType<SqlConnection>(connection);
}
public static void TemplateFactoryCreatesCorrectTypeOfConnection()
{
var factory = new DbConnectionFactory<OleDbConnection>("Provider=SQLOLEDB;" + LocalDbConnection);
var connection = factory.CreateConnection();
Assert.IsType<OleDbConnection>(connection);
}
