private static void AlterRecevied(int bus)
{
string ConnectString = @"Data Source=;Initial Catalog=PostLoan;User ID=;Password="******"DECLARE @BID INT={0}
SELECT BillID INTO #Bill FROM dbo.Bill WHERE BusinessID=@BID
INSERT dbo.Received
( BillID ,BillItemID ,Amount ,ReceivedType ,PayID ,ReceivedTime ,CreateTime ,OperatorID ,Explain ,DeductionID ,ToAccountID ,TIMESTAMP)
SELECT BillID,BillItemID,-Amount,102,0,ReceivedTime,GETDATE(),0,'去哪儿',0,24,NULL
FROM dbo.Received WHERE BillID IN (SELECT BillID FROM #Bill)
UPDATE dbo.Bill SET BillStatus=1 WHERE BillID IN (SELECT BillID FROM #Bill)
UPDATE dbo.BillItem SET ReceivedAmt=0 WHERE BillID IN (SELECT BillID FROM #Bill)
UPDATE dbo.Business SET FrozenNo='' WHERE BusinessID=@BID
UPDATE dbo.JQHDeductRecord SET FillStatus=0 WHERE BusinessID=@BID AND FillStatus=4
EXEC dbo.pro_Business_UpdateForBusinessID @BID
DROP TABLE #Bill", bus);
MSSQLDBServer.ExecuteNonQuery(ConnectString, QueryText, null);
}
public static DataTable GetDataTable()
{
string ConnectString = @"Data Source=.;Initial Catalog=PostLoan;Persist Security Info=True;User ID=;Password="******"SELECT * FROM dbo.Business WHERE IsRepayment=0";
DataTable dt = MSSQLDBServer.ExecuteDatatable(ConnectString, QueryText, null);
return(dt);
}
private static List <int> GetBID707()
{
string ConnectString = @"Data Source=;Initial Catalog=PostLoan;Persist Security Info=True;User ID=;Password="******"SELECT *
FROM dbo.Business
WHERE IsRepayment = 1 AND FrozenNo='' AND BusinessID IN
( 5848164, 5849787, 5851272, 5851339, 5854796, 5862310,
5862979, 5863870, 5864904, 5574854, 5749400, 5751300,
5753519, 5761455, 5762181, 5763356, 5764224, 5765592,
5765734, 5384360, 5384646, 5384771, 5384889, 5385104,
5385150, 5385226, 5385854, 5387206, 5397525, 5400993,
5402245, 5404284, 5476402, 5480999, 5484953, 5488909,
5499660, 5683421, 5686256, 5690188, 5694568, 5695191,
5697188, 5704052, 5307769, 5309757, 5312598, 5313584,
5319946, 5323791, 5324574, 5325514, 5326053, 5326916,
5168957, 5246173, 5248952, 5250479, 5250799, 5263707,
5264573, 5265471, 5265503, 5865697, 5866540, 5869463,
5870121, 5872734, 5881020, 5882915, 5885747, 5582599,
5590585, 5782948, 5786495, 5787441, 5794167, 5796044,
5801411, 5803020, 5405618, 5414147, 5415259, 5416516,
5420988, 5426625, 5509261, 5511486, 5518511, 5523324,
5524095, 5714369, 5714602, 5715009, 5715410, 5716635,
5716663, 5717380, 5717693, 5895634, 5895683, 5268510,
5268653, 5272839, 5273599, 5275077, 5275436, 5275749,
5277069, 5277730, 5278412, 5278642, 5279262, 5279352,
5280048, 5281307, 5287630, 5289321, 5331328, 5338381,
5338507, 5340264, 5343037, 5344678, 5345653, 5347527,
5355681, 5606844, 5609509, 5812338, 5813548, 5826469,
5827153, 5827157, 5434749, 5437024, 5442798, 5447212,
5450212, 5455680, 5724530, 5724898, 5724942, 5725851,
5726700, 5727265, 5728690, 5730694, 5733966, 5529756,
5536218, 5537972, 5540926, 5543061, 5546985, 5912189,
5916885, 5920975, 5924739, 5925434, 5291215, 5297284,
5299537, 5299805, 5302471, 5303546, 5306374, 5663359,
5665811, 5667707, 5671465, 5674214, 5676533, 5356034,
5357494, 5359412, 5360270, 5367259, 5367927, 5370005,
5370784, 5375176, 5835720, 5836046, 5837236, 5737736,
5741119, 5463502, 5552942, 5558482, 6631306, 6639863,
6639918, 6651775, 6651788, 6665728, 6665729 ) ";
DataTable dt = MSSQLDBServer.ExecuteDatatable(ConnectString, QueryText, null);
List <int> BidList = new List <int>();
for (int i = 0; i < dt.Rows.Count; i++)
{
int tmp = int.Parse(dt.Rows[i][0].ToString());
BidList.Add(tmp);
}
return(BidList);
}
private static List <int> GetBID()
{
string ConnectString = @"Data Source=;Initial Catalog=PostLoan;Persist Security Info=True;User ID=;Password="******"SELECT BusinessID FROM dbo.Business WHERE LoanKind = 'LOANKIND/JIEQUHUA' AND IsRepayment = 1 AND FrozenNo=''";
DataTable dt = MSSQLDBServer.ExecuteDatatable(ConnectString, QueryText, null);
List <int> BidList = new List <int>();
for (int i = 0; i < dt.Rows.Count; i++)
{
int tmp = int.Parse(dt.Rows[i][0].ToString());
BidList.Add(tmp);
}
return(BidList);
}
public void Test()
{
string ConnectString = @"Data Source=.;Initial Catalog=PostLoan;Persist Security Info=True;User ID=;Password="******"SELECT * FROM dbo.Business";
DataTable dt = MSSQLDBServer.ExecuteDatatable(ConnectString, sqlString, null);
List <Business> businessList = new List <Business>();
try
{
for (int i = 0; i < 10000000; i++)
{
businessList = dt.ConvertToList <Business>();
logger.InfoFormat("循环第{0}次，数组有{1}条数据", i, businessList.Count);
}
}
catch (Exception e)
{
logger.Error(e);
}
}
public void TestSucceed()
{
//1、用户表插入一个员工并返回ID
//2、查询用户表
//3、批量导入10条角色数据
//4、查询角色表并返回条数
//5、查询用户表和角色表
//6、查询单值
//7、删除用户
//8、删除角色
#region 1、用户表插入一个员工并返回ID
SqlParameter[] ParameterStep1 =
{
new SqlParameter("@UserID", SqlDbType.Int),
new SqlParameter("@UserCode", SqlDbType.VarChar, 50),
new SqlParameter("@UserName", SqlDbType.NVarChar, 50)
};
ParameterStep1[1].Value = "D001";
ParameterStep1[2].Value = "管理员";
ParameterStep1[0].Direction = ParameterDirection.Output;
Dictionary <String, Object> OutParametersStep1;
Int32 ResultStep1 = default(Int32);
String OutValueStep1 = String.Empty;
try
{
ResultStep1 = MSSQLDBServer.ExecuteNonQuery(ConnectString, "DemoOutParameterExecuteNonQuery",
out OutParametersStep1, ParameterStep1);
OutValueStep1 = OutParametersStep1["@UserID"].ToString();
}
catch
{
Assert.Fail();
}
#endregion
#region 2、查询用户表
SqlParameter[] ParameterStep2 = { new SqlParameter("@UserID", SqlDbType.VarChar, 50) };
ParameterStep2[0].Value = OutValueStep1;
String QueryTextStep2 = "SELECT * FROM dbo.SYS_User WHERE UserID=@UserID";
DataTable ResultStep2 = default(DataTable);
try
{
ResultStep2 = MSSQLDBServer.ExecuteDatatable(ConnectString, QueryTextStep2, ParameterStep2);
}
catch
{
Assert.Fail();
}
#endregion
#region 3、批量导入10条角色数据
DataTable ImportTable = new DataTable();
ImportTable.Columns.AddRange(new DataColumn[] {
new DataColumn("RoleCode", typeof(String)),
new DataColumn("RoleName", typeof(String)),
new DataColumn("RoleLevel", typeof(Int32)),
new DataColumn("IsEnable", typeof(Boolean))
});
for (Int32 i = 0; i < 10; i++)
{
DataRow Row = ImportTable.NewRow();
Row[0] = String.Format("T{0}", i.ToString());
Row[1] = String.Format("测试{0}", i.ToString());
Row[2] = -1;
Row[3] = false;
ImportTable.Rows.Add(Row);
}
ImportTable.TableName = "dbo.SYS_Role";
Boolean ResultStep3 = default(Boolean);
try
{
ResultStep3 = MSSQLDBServer.SQLBulkCopy(ConnectString, ImportTable);
}
catch
{
Assert.Fail();
}
#endregion
#region 4、查询角色表并返回条数
SqlParameter[] ParameterStep4 =
{
new SqlParameter("@OrderBy", SqlDbType.NVarChar, 50),
new SqlParameter("@OrderType", SqlDbType.NVarChar, 10),
new SqlParameter("@Tabel", SqlDbType.NVarChar, 50),
new SqlParameter("@ColumnName", SqlDbType.NVarChar, 100),
new SqlParameter("@Condition", SqlDbType.NVarChar, 500),
new SqlParameter("@Page", SqlDbType.Int),
new SqlParameter("@Row", SqlDbType.Int),
new SqlParameter("@Count", SqlDbType.Int)
};
ParameterStep4[0].Value = "RoleID";
ParameterStep4[1].Value = "ASC";
ParameterStep4[2].Value = "dbo.SYS_Role";
ParameterStep4[3].Value = "*";
ParameterStep4[4].Value = " AND RoleLevel=-1 ";
ParameterStep4[5].Value = "2";
ParameterStep4[6].Value = "5";
ParameterStep4[7].Direction = ParameterDirection.Output;
Dictionary <String, Object> OutParametersStep4;
DataTable ResultStep4 = default(DataTable);
String OutValueStep4 = String.Empty;
try
{
ResultStep4 = MSSQLDBServer.ExecuteDatatable(ConnectString, "dbo.SP_PageQuery",
out OutParametersStep4, ParameterStep4);
OutValueStep4 = OutParametersStep4["@Count"].ToString();
}
catch
{
Assert.Fail();
}
#endregion
#region 5、查询用户表和角色表
String QueryTextStep5 = @"SELECT * FROM dbo.SYS_User WHERE UserID=@UserID;
SELECT * FROM dbo.SYS_Role WHERE RoleLevel=-1";
SqlParameter[] ParameterStep5 =
{
new SqlParameter("@UserID", SqlDbType.VarChar, 50)
};
ParameterStep5[0].Value = OutValueStep1;
DataSet ResultStep5 = default(DataSet);
try
{
ResultStep5 = MSSQLDBServer.ExecuteDataset(ConnectString, QueryTextStep5, ParameterStep5);
}
catch
{
Assert.Fail();
}
#endregion
#region 6、查询单值
String QueryTextStep6 = @"SELECT RoleName FROM dbo.SYS_Role WHERE RoleCode=@RoleCode ";
SqlParameter[] ParameterStep6 =
{
new SqlParameter("@RoleCode", SqlDbType.VarChar, 50)
};
ParameterStep6[0].Value = "T5";
String ResultStep6 = String.Empty;
try
{
ResultStep6 = MSSQLDBServer.ExecuteScalar(ConnectString, QueryTextStep6, ParameterStep6).ToString();
}
catch
{
Assert.Fail();
}
#endregion
#region 7、删除用户
String QueryTextStep7 = @"DELETE dbo.SYS_User WHERE UserID=@UserID";
SqlParameter[] ParameterStep7 =
{
new SqlParameter("@UserID", SqlDbType.Int)
};
ParameterStep7[0].Value = Int32.Parse(OutValueStep1);
Int32 ResultStep7 = default(Int32);
try
{
ResultStep7 = MSSQLDBServer.ExecuteNonQuery(ConnectString, QueryTextStep7, ParameterStep7);
}
catch
{
Assert.Fail();
}
#endregion
#region 8、删除角色
String QueryTextStep8 = @"DELETE dbo.SYS_Role WHERE RoleLevel='-1'";
Int32 ResultStep8 = default(Int32);
try
{
ResultStep8 = MSSQLDBServer.ExecuteNonQuery(ConnectString, QueryTextStep8);
}
catch
{
Assert.Fail();
}
#endregion
//assert
Assert.AreEqual(1, ResultStep1);
Assert.AreEqual("D001", ResultStep2.Rows[0][1].ToString());
Assert.AreEqual("管理员", ResultStep2.Rows[0][2].ToString());
Assert.AreEqual(true, ResultStep3);
Assert.AreEqual("测试6", ResultStep4.Rows[1][3].ToString());
Assert.AreEqual("10", OutValueStep4);
Assert.AreEqual(1, ResultStep5.Tables[0].Rows.Count);
Assert.AreEqual(1, ResultStep5.Tables[0].Rows.Count);
Assert.AreEqual("测试5", ResultStep6);
Assert.AreEqual(1, ResultStep7);
Assert.AreEqual(10, ResultStep8);
}
public static DataTable GetDataTable1()
{
string ConnectString = ConfigurationManager.ConnectionStrings["VcreditDW"].ToString();
string QueryText = string.Format(@"
SELECT ContractNo AS '合同号' ,
CustomerName AS '客户姓名' ,
CustomerProperty AS '客户性质' ,
ReceiptVersion AS '借款借据版本' ,
LoanCapital AS '贷款金额' ,
ResidualCapital AS '剩余本金' ,
OverdueAmount AS '逾期金额' ,
Capital AS '本金' ,
Interest AS '利息' ,
ManagementFee AS '管理费' ,
CapitalInterestLoss AS '本息扣失' ,
ServiceFee AS '服务费' ,
GuaranteeFee AS '担保管理费' ,
ServiceFeeLoss AS '服务费扣失' ,
Penalty AS '罚息' ,
BillFee AS '账单费用' ,
LoanPeriod AS '贷款期数' ,
RepayPeriod AS '贷款期限内已还期数（全部还款）' ,
CASE WHEN OverdueAmount > 0 THEN '是'
ELSE '否'
END AS '是否欠费' ,
OverdueDate AS '逾期日期' ,
OverdueDays AS '逾期天数' ,
TodayOverdueMark '当日逾期标记' ,
Guarantee AS '担保' ,
GuaranteeDate AS '担保日期' ,
GuaranteeAmount AS '担保金额' ,
OutStatus AS '委外状态' ,
LitigationStatus AS '诉讼状态' ,
CLoanStatus AS '提前清贷状态' ,
SpecialPolicy AS '特殊政策' ,
Product AS '贷款产品种类' ,
ProductType AS '产品类型' ,
LendingSide AS '信托方（放款方）' ,
ServiceSide AS '服务方' ,
LoanTime AS '放款日期' ,
SalesChannels AS '销售渠道' ,
SigningCity AS '城市' ,
SigningShop AS '签约门店' ,
SalesTeam AS '团队' ,
SalesStaff AS '经办人' ,
BusinessID AS '业务号' ,
SecondSales AS '二次加贷' ,
CustomerID AS '客户号' ,
HouseHold AS '户籍' ,
DATEADD(DAY, -1, StatisticsDate) AS '日期' ,
DivisionName AS '分部' ,
DATEPART(DAY, RelativeDate) AS '账单日' ,
Guarantee AS '担保方' ,
CustomerChannel AS '客户渠道' ,
RiskLevel AS '风险等级' ,
SocialMonths AS '社保缴纳月数' ,
AccumMonths AS '公积金缴纳月数' ,
ZhengXinCount AS '征信查询次数' ,
EnterBlackListTime AS '进入黑名单时间' ,
AdminStore AS '管理门店' ,
ChannelOTwoO AS '订单来源' ,
MarketStore AS '营销门店' ,
MarketTeam AS '营销团队' ,
MarketStaff AS '营销人' ,
ROW_NUMBER() OVER ( ORDER BY OverID ) RowID
FROM dbo.OverDueStatic WITH ( NOLOCK )
WHERE StatisticsDate = '{0}'
AND DivisionName <> '网络金融'
AND DivisionName <> '市场部'
ORDER BY ContractNo", DateTime.Now.ToString("yyyy-MM-dd"));
DataTable dt = MSSQLDBServer.ExecuteDatatable(ConnectString, QueryText, null);
return(dt);
}
