public User Save(User user)
{
//const string execName = "Proc_InsertUser";
var sqlText = new StringBuilder();
User retUser;
#region sql
sqlText.Append(@" INSERT INTO [dbo].[User]
([UserName]
,[UserPWD]
,[Email]
,[Mobile]
,[CreateDateTime]
,[UpdateDateTime])
OUTPUT INSERTED.*
VALUES(
@UserName
,@UserPWD
,@Email
,@Mobile
,GETDATE()
,GETDATE());
");
#endregion
using (var conn = SqlServerDB.GetSqlConnection())
{
var re = conn.Query <User>(sqlText.ToString(), user);
retUser = re.FirstOrDefault();
}
return(retUser);
}
public Pager <UserInfoAccount> Query(UserInfoQueryParameter para)
{
var dataList = new Lazy <Pager <UserInfoAccount> >();
var sqlText = new StringBuilder();
//var pageSqlText = new StringBuilder();
sqlText.Append(@"SELECT
U.ID
,UM.ACCOUNT as DefaultAccount
,U.PASSWORD
,U.NICKNAME
,U.GENDER
,U.COMPANYNAME
,U.ADDRESS
,U.REMARK
FROM USERINFO AS U
INNER JOIN USERINFOACCOUNT AS UM
ON U.ID = UM.USERINFOID
WHERE 1 = 1
" );
var conditions = para.GenerateByOperate(GenerateOperate.Condition); //string.Empty.GenerateCondition(para);
sqlText.Append(conditions);
sqlText.Append(" ORDER BY Id DESC OFFSET ");
sqlText.Append(((para.Skip) * para.Take).ToString());
sqlText.Append(" ROWS FETCH NEXT ");
sqlText.Append(para.Take.ToString());
sqlText.Append(" ROWS ONLY;");
if (para.IsPage)
{
sqlText.Append(" SELECT COUNT(1) AS Total ,CEILING((COUNT(1)+0.0)/")
.Append(para.Take.ToString())
.Append(") AS Pages FROM USERINFO WHERE 1 =1 ")
.Append(conditions + ";");
//sqlText.Append(pageSqlText);
}
using (var conn = SqlServerDB.GetSqlConnection())
{
using (var grid = conn.QueryMultiple(sqlText.ToString(), para))
{
//dataList.Value.Items = data.ToArray();
dataList.Value.Items = grid.Read <UserInfoAccount>().ToArray();
if (para.IsPage)
{
var pageInfo = grid.Read().FirstOrDefault();
if (pageInfo == null)
{
return(dataList.Value);
}
dataList.Value.Total = (int)pageInfo.Total;
dataList.Value.Pages = (int)pageInfo.Pages;
}
else
{
return(dataList.Value);
}
}
}
return(dataList.Value);
}
/// <summary>
/// 获取查询结果
/// </summary>
/// <param name="para"></param>
/// <returns></returns>
public Pager <User> QueryUsersByParameter(UserQueryParameter para)
{
var dataList = new Lazy <Pager <User> >();
var sqlText = new StringBuilder();
#region sql
sqlText.Append(
@"SELECT [Id]
,[UserName]
,[UserPWD]
,[Email]
,[CreateDateTime]
,[Mobile] FROM [USER] WHERE 1 = 1 ");
var conditions = para.GenerateByOperate(GenerateOperate.Condition); //string.Empty.GenerateCondition(para);
sqlText.Append(conditions);
sqlText.Append(" ORDER BY Id DESC OFFSET ");
sqlText.Append(((para.Skip) * para.Take).ToString());
sqlText.Append(" ROWS FETCH NEXT ");
sqlText.Append(para.Take.ToString());
sqlText.Append(" ROWS ONLY;");
if (para.IsPage)
{
sqlText.Append(" SELECT COUNT(1) AS Total ,CEILING((COUNT(1)+0.0)/")
.Append(para.Take.ToString())
.Append(") AS Pages FROM [USER] WHERE 1 =1 ")
.Append(conditions + ";");
}
#endregion
using (IDbConnection conn = SqlServerDB.GetSqlConnection())
{
var grid = conn.QueryMultiple(sqlText.ToString(), para);
dataList.Value.Items = grid.Read <User>().ToArray();
if (para.IsPage)
{
var pageInfo = grid.Read().FirstOrDefault();
if (pageInfo == null)
{
return(dataList.Value);
}
dataList.Value.Total = (int)pageInfo.Total;
dataList.Value.Pages = (int)pageInfo.Pages;
}
else
{
return(dataList.Value);
}
}
return(dataList.Value);
}
public UserInfoAccount Save(UserInfoAccount userInfoAccount)
{
UserInfoAccount retUser;
#region sqlText
const string sqlText = @"INSERT INTO [DBO].[USERINFOACCOUNT]
VALUES(NEXT VALUE FOR UserDBSequence
,@USERINFOID
,@ACCOUNTTYPE
,@ACCOUNT)";
#endregion
using (var conn = SqlServerDB.GetSqlConnection())
{
var re = conn.Query <UserInfoAccount>(sqlText, userInfoAccount);
retUser = re.FirstOrDefault();
}
return(retUser);
}
public UserInfo Save(UserInfo userInfo)
{
UserInfo retUser = null;
#region sql
#region sqlText
const string sqlText = @" INSERT INTO [dbo].[UserInfo]
(
[ID]
,[PASSWORD]
,[NICKNAME]
,[GENDER]
,[COMPANYNAME]
,[ADDRESS]
,[REMARK])
OUTPUT INSERTED.*
VALUES(
NEXT VALUE FOR UserDBSequence
,@PASSWORD
,@NICKNAME
,@GENDER
,@COMPANYNAME
,@ADDRESS
,@REMARK
); ";
// const string mappingSqlText = @"INSERT INTO [DBO].[USERINFOACCOUNT]
// VALUES(NEXT VALUE FOR UserDBSequence
// ,@USERINFOID
// ,@ACCOUNTTYPE
// ,@ACCOUNT)";
#endregion
#endregion
#region USE DONET TRANSACTION
using (var conn = SqlServerDB.GetSqlConnection())
{
conn.Open();
//var trans = conn.BeginTransaction();
try
{
var re = conn.Query <UserInfo>(sqlText, userInfo);
//var re = conn.Query<UserInfo>(sqlText, userInfo, trans);
retUser = re.FirstOrDefault();
//if (retUser != null)
//{
// foreach (var account in userInfo.Accounts)
// {
// conn.Execute(mappingSqlText,
// new { USERINFOID = retUser.Id, ACCOUNTTYPE = userInfo.AccountType, ACCOUNT = account }, trans);
// }
// trans.Commit();
//}
}
catch (Exception)
{
//trans.Rollback();
}
}
#endregion
#region USE SQL TRANSACTION
#region transactionSqlText
// const string transactionSqlText = @"DECLARE @USERINFO_TEMP TABLE(
// [ID] BIGINT,
// [ACCOUNT] NVARCHAR(50) NOT NULL,
// [PASSWORD] NVARCHAR(50) NOT NULL,
// [NICKNAME] NVARCHAR(30) NULL,
// [GENDER] BIT NULL,
// [COMPANYNAME] NVARCHAR(50) NULL,
// [ADDRESS] NVARCHAR(100) NULL,
// [REMARK] NVARCHAR(100) NULL)
// DECLARE @USERINFO_ID BIGINT;
// BEGIN TRAN
// BEGIN TRY
// INSERT INTO [dbo].[UserInfo]
// ([ACCOUNT]
// ,[PASSWORD]
// ,[NICKNAME]
// ,[GENDER]
// ,[COMPANYNAME]
// ,[ADDRESS]
// ,[REMARK])
// OUTPUT INSERTED.* INTO @USERINFO_TEMP
// VALUES(
// @ACCOUNT
// ,@PASSWORD
// ,@NICKNAME
// ,@GENDER
// ,@COMPANYNAME
// ,@ADDRESS
// ,@REMARK);
// PRINT @USERINFO_ID;
// SELECT @USERINFO_ID =ID FROM @USERINFO_TEMP
// PRINT @USERINFO_ID;
// INSERT INTO [DBO].[UserInfo_AccountType_Mapping] VALUES (@USERINFO_ID,@ACCOUNT_TYPE)
// COMMIT
// SELECT * FROM @USERINFO_TEMP ;
// END TRY
// BEGIN CATCH
// THROW
// ROLLBACK
// RETURN
// END CATCH";
#endregion
#region proc_transaction
//const string insertProc = "PROC_INSERTUSERINFO";
#endregion
//using (var conn = SqlServerDB.GetSqlConnection())
//{
// var re = conn.Query<UserInfo>(insertProc, new
// {
// Account_Type = userInfo.AccountType,
// ACCOUNT = userInfo.Account,
// PASSWORD = userInfo.Password,
// NICKNAME = userInfo.NickName,
// GENDER = userInfo.Gender,
// COMPANYNAME = userInfo.CompanyName,
// ADDRESS = userInfo.Address,
// REMARK = userInfo.Remark
// }, null, false, null, CommandType.StoredProcedure);
// retUser = re.FirstOrDefault();
//}
#endregion
return(retUser);
}