1. Quick use

1.1. ISmartSqlMapper Common (Partial) Interface Overview

Function Description
Execute IDbCommand.ExecuteNonQuery, execution returns the number of affected rows
ExecuteScalar IDbCommand.ExecuteScalar, execute and return the first column of the first row in the ReultSet returned by the query
Query Execution Returns Entity List
QuerySingle Execution returns a single entity
GetDataTable Execute returns DataTable
GetDataSet Execute returns DataSet
BeginTransaction Open transaction
CommitTransaction Submit a transaction
RollbackTransaction Rollback Transaction

1.2. Insert

1.2.1. Statement

    <Statement Id="Insert">
      INSERT INTO T_User
      (UserName
      ,Password
      ,Status
      ,LastLoginTime
      ,CreationTime)
      VALUES
      (?UserName
      ,?Password
      ,?Status
      ,?LastLoginTime
      ,?CreationTime)
      ;Select Last_Insert_Id();
    </Statement>

1.2.2. Return primary key


            ISmartSqlMapper SqlMapper = MapperContainer.Instance.GetSqlMapper();
            long userId = _smartSqlMapper.ExecuteScalar<long>(new RequestContext
            {
                Scope = "User",
                SqlId = "Insert",
                Request = new User
                {
                    UserName = request.UserName,
                    Pwd = request.Pwd,
                    Status = Entitiy.UserStatus.Ok,
                    CreationTime = DateTime.Now,
                }
            });

1.2.3. Return number of affected rows

            SqlMapper.Execute(new RequestContext
            {
                Scope = "User",
                SqlId = "Insert",
                Request = new User
                {
                    UserName = request.UserName,
                    Pwd = request.Pwd,
                    Status = Entitiy.UserStatus.Ok,
                    CreationTime = DateTime.Now,
                }
            });

1.3. Delete

    <Statement Id="Delete">
      Delete FROM  T_User
      Where Id=?Id
    </Statement>
            SqlMapper.Execute(new RequestContext
            {
                Scope = "User",
                SqlId = "Delete",
                Request = new { Id = 3 }
            });

1.4. Update

1.4.1. Statement.Update

<Statement Id="Update">
      UPDATE T_User
      <Set>
        <IsProperty Prepend="," Property="UserName">
          UserName = ?UserName
        </IsProperty>
        <IsProperty Prepend="," Property="Password">
          Password = ?Password
        </IsProperty>
        <IsProperty Prepend="," Property="Status">
          Status = ?Status
        </IsProperty>
        <IsProperty Prepend="," Property="LastLoginTime">
          LastLoginTime = ?LastLoginTime
        </IsProperty>
        <IsProperty Prepend="," Property="CreationTime">
          CreationTime = ?CreationTime
        </IsProperty>
      </Set>
      Where Id=?Id
    </Statement>

1.4.2. Full update

            SqlMapper.Execute(new RequestContext
            {
                Scope = "User",
                SqlId = "Update",
                Request = new User
                {
                    Id=1,
                    UserName = request.UserName,
                    Pwd = request.Pwd,
                    Status = Entitiy.UserStatus.Ok,
                    CreationTime = DateTime.Now,
                }
            });

1.4.3. Partial update

            SqlMapper.Execute(new RequestContext
            {
                Scope = "User",
                SqlId = "Update",
                Request = new { Id=1 , Pwd = "SmartSql" }
            });

1.5. Query return List

<Statement Id="Query">
      SELECT T.* From T_User T
      <Where>
        <IsNotEmpty Prepend="And" Property="EqUserName">
          T.UserName=$EqUserName
        </IsNotEmpty>
        <IsNotEmpty Prepend="And" Property="UserName">
          T.UserName Like Concat('%',$UserName,'%')
        </IsNotEmpty>
      </Where>
      <Switch Prepend="Order By" Property="OrderBy">
        <Default>
          T.Id Desc
        </Default>
      </Switch>
      <IsNotEmpty Prepend="Limit" Property="Taken">?Taken</IsNotEmpty>
    </Statement>
            var list = SqlMapper.Query<User>(new RequestContext
            {
                Scope = "User",
                SqlId = "Query",
                Request = new
                {
                    Taken = 10
                }
            });

1.6. Query returns a single entity

    <Statement Id="GetEntity">
      Select T.* From T_User T
      <Where>
        <IsNotEmpty Prepend="And" Property="Id">
          T.Id=?Id
        </IsNotEmpty>
      </Where>
      Limit 1
    </Statement>
            var user = SqlMapper.QuerySingle<User>(new RequestContext
            {
                Scope = "User",
                SqlId = "GetEntity",
                Request = new { Id = 1 }
            });

1.7. Transaction

            try
            {
                SqlMapper.BeginTransaction();
                //BizCode();
                SqlMapper.CommitTransaction();
            }
            catch (Exception ex)
            {
                SqlMapper.RollbackTransaction();
                throw ex;
            }

1.8. Stored procedure

            DbParameterCollection dbParameterCollection = new DbParameterCollection();
            dbParameterCollection.Add(new DbParameter
            {
                Name = "Total",
                DbType = System.Data.DbType.Int32,
                Direction = System.Data.ParameterDirection.Output
            });
            RequestContext context = new RequestContext
            {
                CommandType = System.Data.CommandType.StoredProcedure,
                RealSql = "SP_QueryByPage",
                Request = dbParameterCollection
            };
            var list = SqlMapper.Query<User>(context);
            var total = dbParameterCollection.GetValue<int>("Total");