public void QueryWithPhoto()
{
DalBase<NModel.Product> dalProduct = new DalBase<NModel.Product>();
string query = "select p from Product p where p.ProductImageUrls.size>0";
var pl= dalProduct.GetList(query);
Console.WriteLine(pl.Count);
}
internal object saveStaff(Staff Obj, SqlTransaction trans)
{
try
{
object ID = DalBase <StaffEntity> .Save(Obj.Fields, trans, Obj.IsNew);
Obj.IsNew = false;
return(ID);
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public int InsertMea(Model.Measures measure)
{
measure.MeDate = DateTime.Now;
int id = -1;
if (DalBase.Insert(measure) > 0)
{
Model.CustomLosts c = new Model.CustomLosts()
{
CLState = 2,
CLID = measure.CLID,
};
id = UpdLost(c);
}
return(id);
}
/// <summary>
/// 绑定二级菜单
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void Menu_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
//判断里层repeater处于外层repeater的哪个位置（ AlternatingItemTemplate，FooterTemplate，
//HeaderTemplate，，ItemTemplate，SeparatorTemplate）
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
Repeater rep = e.Item.FindControl("SubMenu") as Repeater;//找到里层的repeater对象
DataRowView rowv = (DataRowView)e.Item.DataItem;//找到分类Repeater关联的数据项
string mid = rowv["mid"].ToString(); //获取填充子类的id
DataTable dt = DalBase.Util_GetList("select * from T_Member_Menu where pid='" + mid + "' and [target]='newgzs'").Tables[0];
rep.DataSource = dt;
rep.DataBind();
}
}
public List <Model.View_Users> GetUsers()
{
string UserLName = Session["UserLName"].ToString();
//当前登录人角色id
string sql1 = string.Format(@"select RoleID from Users where UserLName=@UserLName");
SqlParameter[] sp1 = new SqlParameter[] {
new SqlParameter("@UserLName", UserLName)
};
int rid = DalBase.SelectObj(sql1, sp1);
if (rid == 1)
{
return(DalBase.SelectAll <Model.View_Users>());
}
return(null);
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
string type = ((Button)sender).CommandArgument;
if (type == "0")
{
string id = txtid.Text.Trim();
string pw = txtpw.Text.Trim();
DataTable dt = DalBase.Util_GetList("select * from N_Stamp where StampNo='" + id + "' and Password='******'").Tables[0];
if (dt.Rows.Count > 0)
{
lituse.Text = dt.Rows[0]["IsUsed"].ToString() == "0" ? "未使用" : "已使用";
litgqsj.Text = Convert.ToDateTime(dt.Rows[0]["StampOutTime"].ToString()).ToString("yyyy-MM-dd");
litje.Text = (float.Parse(dt.Rows[0]["StampMoney"].ToString()) / 100).ToString();
Panel1.Visible = true;
Panel2.Visible = false;
txtid.ReadOnly = false;
txtpw.ReadOnly = false;
txtid.Style.Add("border", "none");
txtpw.Style.Add("border", "none");
btnSubmit.Text = "确定";
btnSubmit.OnClientClick = "return CheckForm()";
btnSubmit.CommandArgument = "1";
bindhy();
bindtc();
}
else
{
Panel1.Visible = false;
Panel2.Visible = true;
}
}
else if (type == "1")
{
string[] arry = RadioButtonList1.SelectedValue.Split('$');
if (DalBase.Util_UpdateBat("memberLevel='" + arry[2] + "',TrustNumber=" + txttg.Text.Trim() + ",DownloadNumber=" + txtxz.Text.Trim() + ",levelServiceStartTime='" + txtStart.Value + "',levelServiceEndTime='" + txtEnd.Value + "'", "memberId=" + memberid, "T_Member_Info") > 0)
{
DalBase.Util_UpdateBat("IsUsed=1", "StampNo='" + txtid.Text.Trim() + "' and Password='******'", "N_Stamp");
ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script>alert('设置成功！');layer_close();</script>");
}
else
{
ClientScript.RegisterStartupScript(this.GetType(), "alert", "<script>alert('设置失败，请重试！');</script>");
}
}
}
private static DBInfo GetSchemaDic(string conn)
{
DBInfo info = new DBInfo();
using (DalBase dal = DalCreate.CreateDal(conn))
{
info.ConnName = dal.ConnObj.Master.ConnName;
info.ConnString = dal.ConnObj.Master.ConnString;
info.DataBaseName = dal.DataBaseName;
info.DataBaseType = dal.DataBaseType;
info.DataBaseVersion = dal.Version;
Dictionary <string, string> tables = dal.GetTables();
if (tables != null && tables.Count > 0)
{
Dictionary <int, TableInfo> dic = new Dictionary <int, TableInfo>();
foreach (KeyValuePair <string, string> item in tables)
{
dic.Add(TableInfo.GetHashCode(item.Key), new TableInfo(item.Key, "U", item.Value, info));
}
info.Tables = dic;
}
Dictionary <string, string> views = dal.GetViews();
if (views != null && views.Count > 0)
{
Dictionary <int, TableInfo> dic = new Dictionary <int, TableInfo>();
foreach (KeyValuePair <string, string> item in views)
{
dic.Add(TableInfo.GetHashCode(item.Key), new TableInfo(item.Key, "V", item.Value, info));
}
info.Views = dic;
}
Dictionary <string, string> procs = dal.GetProcs();
if (procs != null && procs.Count > 0)
{
Dictionary <int, TableInfo> dic = new Dictionary <int, TableInfo>();
foreach (KeyValuePair <string, string> item in procs)
{
dic.Add(TableInfo.GetHashCode(item.Key), new TableInfo(item.Key, "P", item.Value, info));
}
info.Procs = dic;
}
}
return(info);
}
private string GetMarketInfo(string marketid)
{
try
{
string info = string.Empty;
DataTable dt = DalBase.Util_GetList("select Market_name,Area_id,(select area_name from T_Base_Area where area_id=m.Area_id) as area_name from dbo.T_Base_Market m where Market_id='" + marketid + "'").Tables[0];
if (dt.Rows.Count > 0)
{
info += dt.Rows[0]["area_name"].ToString() + "-" + dt.Rows[0]["Market_name"].ToString();
}
return(info);
}
catch
{
return("");
}
}
private static MDataColumn GetViewColumns(string sqlText, ref DalBase helper)
{
MDataColumn mdc = null;
helper.OpenCon(null, AllowConnLevel.MaterBackupSlave);
helper.Com.CommandText = sqlText;
DbDataReader sdr = helper.Com.ExecuteReader(CommandBehavior.KeyInfo);
DataTable keyDt = null;
if (sdr != null)
{
keyDt = sdr.GetSchemaTable();
mdc = GetColumnByTable(keyDt, sdr, true);
mdc.DataBaseType = helper.DataBaseType;
}
return(mdc);
}
/// <summary>
/// 获取所有表（表名+表说明）【链接错误时，抛异常】
/// </summary>
/// <param name="conn">数据库链接</param>
/// <param name="dbName">返回指定链接的数据库名称</param>
/// <param name="errInfo">链接错误时的信息信息</param>
public static Dictionary <string, string> GetTables(string conn, out string dbName, out string errInfo)
{
errInfo = string.Empty;
using (DalBase helper = DalCreate.CreateDal(conn))
{
dbName = helper.DataBase;
if (!helper.TestConn(AllowConnLevel.MaterBackupSlave))
{
errInfo = helper.DebugInfo.ToString();
if (string.IsNullOrEmpty(errInfo))
{
errInfo = helper.DataBaseType + " Open database fail : " + dbName;
}
return(null);
}
}
return(TableSchema.GetTables(conn));//内部有缓存
}
public void Warning()
{
string sql = string.Format("select * from (select *, datediff(month, OrderDate, getdate()) as c from Orders) as rel where rel.c > 6");
List <Orders> orders = DalBase.SelectsByWhere <Orders>(sql, null);
if (orders != null)
{
foreach (var item in orders)
{
string sql1 = string.Format(@"insert into CustomLosts (CusID,CLOrderDate,CLDate,CLState)values(@CusID,@CLOrderDate,getdate(),1);");
SqlParameter[] sp = new SqlParameter[] {
new SqlParameter("@CusID", item.CusID),
new SqlParameter("@CLOrderDate", item.OrderDate),
};
DalBase.Insert(sql1, sp);
}
}
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="name">文件名称</param>
private void CreateExcel(string name)
{
//DataTable thisTable = DalBase.Util_GetList("select * from T_Member_Info where exists(select levelId from T_Member_Level where levelId=memberLevel and moneyType='Year')").Tables[0];
//if (thisTable != null)
//{
// StringWriter sw = new StringWriter();
// sw.WriteLine("用户名\t公司名称\t可托管标准数\t可下载标准数\t有效时间\t会员状态");
// foreach (DataRow dr in thisTable.Rows)
// {
// sw.WriteLine(dr["memberName"] + "\t" + dr["memberCompanyName"].ToString() + "\t"
// + dr["TrustNumber"].ToString() + "\t" + dr["DownloadNumber"] + "\t" + string.Format("{0:yyyy-MM-dd}", dr["levelServiceStartTime"]) + "至" + string.Format("{0:yyyy-MM-dd}", dr["levelServiceEndTime"]) + "\t"
// + (dr["memberStatus"].ToString() == "ZC" ? "正常" : "禁用"));
// }
// sw.Close();
// Response.Charset = "GB2312";
// string filename = name;
// Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename) + ".xls");
// Response.ContentType = "application/ms-excel";
// Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
// Response.Write(sw);
// Response.End();
//}
DataTable thisTable = DalBase.Util_GetList("select * from T_Member_Info " + SetFilter()).Tables[0];
if (thisTable != null)
{
StringWriter sw = new StringWriter();
sw.WriteLine("会员名称\t性别\t真实姓名\t会员类型\t单位名称\t联系电话\t会员状态\t注册时间\t现金账户\t赠送的账户");
foreach (DataRow dr in thisTable.Rows)
{
sw.WriteLine(dr["memberName"] + "\t" + dr["sex"].ToString() + "\t" + dr["memberTrueName"].ToString() + "\t" + GetMemberType(dr["memberType"].ToString()) + "\t" + dr["memberCompanyName"].ToString() + "\t" + dr["tel"].ToString() + "\t" + GetMemberStatus(dr["memberStatus"].ToString()) + "\t" + string.Format("{0:yyyy-MM-dd}", dr["regTime"]) + "\t" + (dr["buyMoneyAccount"].ToString()) + dr["freeMoenyAccount"].ToString());
}
sw.Close();
Response.Charset = "GB2312";
string filename = name;
Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename) + ".xls");
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.Write(sw);
Response.End();
}
}
public int DelUsersById(int id)
{
//根据当前登录name查询id
//判断是否相等
//当前登录人id
string UserLName = Session["UserLName"].ToString();
string sql1 = string.Format(@"select UserID from Users where UserLName=@UserLName");
SqlParameter[] sp1 = new SqlParameter[] {
new SqlParameter("@UserLName", UserLName)
};
int uid = DalBase.SelectObj(sql1, sp1);
if (id != uid)
{
return(DalBase.Delete <Model.Users>(id));
}
return(-1);
}
public List <Model.V_Customers> GetVsBy(string CusName, string CusID)
{
string sql = string.Format(@"select * from V_Customers where 1=1");
if (CusName != null && CusName != "")
{
sql += " and CusName like @CusName";
}
if (CusID != null && CusID != "")
{
sql += " and CusID=@CusID";
}
SqlParameter[] sp = new SqlParameter[] {
new SqlParameter("@CusName", "%" + CusName + "%"),
new SqlParameter("@CusID", CusID)
};
return(DalBase.SelectsByWhere <Model.V_Customers>(sql, sp));
}
public List <Model.V_Chances> GetV_Chances()
{
//当前登录人id
string UserLName = Session["UserLName"].ToString();
string sql1 = string.Format(@"select UserID from Users where UserLName=@UserLName");
SqlParameter[] sp1 = new SqlParameter[] {
new SqlParameter("@UserLName", UserLName)
};
int uid = DalBase.SelectObj(sql1, sp1);
//当前登录人角色id
string sql2 = string.Format(@"select RoleID from Users where UserLName=@UserLName");
SqlParameter[] sp2 = new SqlParameter[] {
new SqlParameter("@UserLName", UserLName)
};
int rid = DalBase.SelectObj(sql2, sp2);
string sql = string.Format(@"select * from V_Chances order by ChanID desc");
if (rid <= 2)
{
return(DalBase.SelectsByWhere <Model.V_Chances>(sql, null));
}
else if (rid == 3)
{
string sql3 = string.Format(@"select * from V_Chances where ChanCreateMan=@ChanCreateMan order by ChanID desc");
SqlParameter[] sp3 = new SqlParameter[] {
new SqlParameter("@ChanCreateMan", uid)
};
return(DalBase.SelectsByWhere <Model.V_Chances>(sql3, sp3));
}
else if (rid > 3)
{
return(null);
}
return(DalBase.SelectsByWhere <Model.V_Chances>(sql, null));
}
public int UsersLogin(string UserLName, string UserLPWD)
{
string sql = string.Format(@"select count(*) from Users where UserLName=@UserLName and UserLPWD=@UserLPWD");
SqlParameter[] sp = new SqlParameter[] {
new SqlParameter("@UserLName", UserLName),
new SqlParameter("@UserLPWD", UserLPWD),
};
int count = DalBase.SelectObj(sql, sp);
if (count > 0)
{
Session["UserLName"] = UserLName;
return(1);
}
else
{
return(-1);
}
}
private void LoadTXPlatfrom()
{
this.ddlTxPlatform.DataSource = getddltree();
ddlTxPlatform.DataTextField = "title";
ddlTxPlatform.DataValueField = "stdcategoryid";
ddlTxPlatform.DataBind();
ddlTxPlatform.Items.Insert(0, new ListItem("无", "0"));
if (id != "")
{
DataTable dt = DalBase.Util_GetList("select TXPTplatform from T_News where newsid=" + id).Tables[0];
if (dt.Rows.Count > 0)
{
if (dt.Rows[0]["TXPTplatform"] != null && dt.Rows[0]["TXPTplatform"].ToString() != "")
{
this.ddlTxPlatform.SelectedValue = dt.Rows[0]["TXPTplatform"].ToString();
}
}
}
}
public List <Model.V_CustomServices> GetV_CustomServicesBy(string CusName, string STID)
{
if (GetRid() <= 3)
{
string sql = string.Format(@"select * from v_CustomServices where CSState=4");
if (CusName != null && CusName != "")
{
sql += " and CusName like @CusName";
}
if (STID != null && STID != "")
{
sql += " and STID=@STID";
}
SqlParameter[] sp = new SqlParameter[] {
new SqlParameter("@CusName", CusName),
new SqlParameter("@STID", STID)
};
return(DalBase.SelectsByWhere <Model.V_CustomServices>(sql, sp));
}
return(null);
}
public List <Model.V_CustomServices> GetServicesBy(int CSState)
{
//当前登录人id
string UserLName = Session["UserLName"].ToString();
string sql1 = string.Format(@"select UserID from Users where UserLName=@UserLName");
SqlParameter[] sp1 = new SqlParameter[] {
new SqlParameter("@UserLName", UserLName)
};
int uid = DalBase.SelectObj(sql1, sp1);
int rid = GetRid();
if (rid < 3 || CSState == 3)
{
string sql = string.Format(@"select * from [dbo].[v_CustomServices] where CSState = @CSState order by CSID desc");
SqlParameter[] sp = new SqlParameter[] {
new SqlParameter("@CSState", CSState)
};
return(DalBase.SelectsByWhere <Model.V_CustomServices>(sql, sp));
}
else if (rid == 3 && CSState == 2)
{
string sql3 = string.Format(@"select * from [dbo].[v_CustomServices] where CSState = 2 and CSDueID=@CSDueID order by CSID desc");
SqlParameter[] sp3 = new SqlParameter[] {
new SqlParameter("@CSDueID", uid)
};
return(DalBase.SelectsByWhere <Model.V_CustomServices>(sql3, sp3));
}
else if (rid == 3 && CSState == 4)
{
string sql4 = string.Format(@"select * from [dbo].[v_CustomServices] where CSState = 4 and CSDueID=@CSDueID order by CSID desc");
SqlParameter[] sp4 = new SqlParameter[] {
new SqlParameter("@CSDueID", uid)
};
return(DalBase.SelectsByWhere <Model.V_CustomServices>(sql4, sp4));
}
return(null);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="name">文件名称</param>
private void CreateExcel(string name)
{
DataTable thisTable = DalBase.Util_GetList("select *,(select levelName from T_Member_Level where levelId= T_Member_Info.memberLevel) as levelName from T_Member_Info " + SetFilter()).Tables[0];
if (thisTable != null)
{
StringWriter sw = new StringWriter();
sw.WriteLine("会员名称\t性别\t真实姓名\t会员类型\t会员级别\t单位名称\t联系电话\t会员状态\t注册时间\t现金账户\t赠送的账户");
foreach (DataRow dr in thisTable.Rows)
{
sw.WriteLine(dr["memberName"] + "\t" + dr["sex"].ToString() + "\t" + dr["memberTrueName"].ToString() + "\t" + GetMemberType(dr["memberType"].ToString()) + "\t" + dr["levelName"].ToString() + "\t" + dr["memberCompanyName"].ToString() + "\t" + dr["tel"].ToString() + "\t" + GetMemberStatus(dr["memberStatus"].ToString()) + "\t" + string.Format("{0:yyyy-MM-dd}", dr["regTime"]) + "\t" + (dr["buyMoneyAccount"].ToString()) + dr["freeMoenyAccount"].ToString());
}
sw.Close();
Response.Charset = "GB2312";
string filename = name;
Response.AddHeader("Content-Disposition", "attachment;filename=" + filename + ".xls");
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.Write(sw);
Response.End();
}
}
/// <summary>
/// 获得文章详细信息
/// </summary>
public void LoadTopicInfo()
{
DataTable dt = DalBase.Util_GetList("select * from T_StandardTopic where ID=" + id).Tables[0];
if (dt.Rows.Count > 0)
{
this.txtTitle.Text = dt.Rows[0]["Title"].ToString();
this.txtContent.Text = dt.Rows[0]["TopicContent"].ToString();
if (dt.Rows[0]["Image"].ToString() != "")
{
Session["img"] = dt.Rows[0]["Image"].ToString();
this.topicImg.Src = mainPath + dt.Rows[0]["Image"].ToString();
if (dt.Rows[0]["Image"].ToString().StartsWith("/") || dt.Rows[0]["Image"].ToString().ToLower().StartsWith("http:"))
{
this.topicImg.Src = dt.Rows[0]["Image"].ToString();
}
}
else
{
Session["img"] = "";
}
this.txtUrl.Text = dt.Rows[0]["Url"].ToString();
this.txtSort.Text = dt.Rows[0]["Sort"].ToString();
if (dt.Rows[0]["IsNew"].ToString() == "1")
{
this.rbtnYes.Checked = true;
this.rbtnNo.Checked = false;
}
else
{
this.rbtnYes.Checked = false;
this.rbtnNo.Checked = true;
}
this.txtKeyWord.Text = dt.Rows[0]["newskeyword"].ToString();
this.ddlTxPlatform.SelectedValue = dt.Rows[0]["txptplatform"].ToString();
SelectPlatFormShare(dt.Rows[0]["ShareToPlatform"].ToString());
}
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="name">文件名称</param>
private void CreateExcel(string name)
{
DataTable thisTable = DalBase.Util_GetList("select * from T_Member_Info where exists(select levelId from T_Member_Level where levelId=memberLevel and moneyType='Year')").Tables[0];
if (thisTable != null)
{
StringWriter sw = new StringWriter();
sw.WriteLine("用户名\t公司名称\t可托管标准数\t可下载标准数\t有效时间\t会员状态");
foreach (DataRow dr in thisTable.Rows)
{
sw.WriteLine(dr["memberName"] + "\t" + dr["memberCompanyName"].ToString() + "\t"
+ dr["TrustNumber"].ToString() + "\t" + dr["DownloadNumber"] + "\t" + string.Format("{0:yyyy-MM-dd}", dr["levelServiceStartTime"]) + "至" + string.Format("{0:yyyy-MM-dd}", dr["levelServiceEndTime"]) + "\t"
+ (dr["memberStatus"].ToString() == "ZC" ? "正常" : "禁用"));
}
sw.Close();
Response.Charset = "GB2312";
string filename = name;
Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(filename) + ".xls");
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
Response.Write(sw);
Response.End();
}
}
public String GetAllMenu(int rid)
{
JavaScriptSerializer js = new JavaScriptSerializer();
Dictionary <string, object> dic = new Dictionary <string, object>();
dic.Add("all", DalBase.SelectAll <Model.Menu>());
string sql1 = string.Format(@"select * from Menu where ID in(
select MenuID from Power where RoleID=@RoleID)");
SqlParameter[] sp1 = new SqlParameter[] {
new SqlParameter("@RoleID", rid)
};
dic.Add("has", DalBase.SelectsByWhere <Model.Menu>(sql1, sp1));
string sql2 = string.Format(@"select * from Menu where ID not in(
select MenuID from Power where RoleID=@RoleID)");
SqlParameter[] sp2 = new SqlParameter[] {
new SqlParameter("@RoleID", rid)
};
dic.Add("no", DalBase.SelectsByWhere <Model.Menu>(sql2, sp2));
return(js.Serialize(dic));
}
public static bool TestConn(string conn, out string msg)
{
bool result = false;
try
{
DalBase helper = DalCreate.CreateDal(conn);
result = helper.TestConn(AllowConnLevel.Master);
if (result)
{
msg = helper.Version;
}
else
{
msg = helper.DebugInfo.ToString();
}
helper.Dispose();
}
catch (Exception err)
{
msg = err.Message;
}
return(result);
}
public void QueryOrderList()
{
DalBase<NModel.Product> dalProduct = new DalBase<NModel.Product>();
string query = @"select p from Product p where p.NTSCode in('02.001.0007700492',
'02.001.0007700439',
'02.001.0007700929',
'02.001.0007700684',
'02.001.0007700685',
'02.001.0007700688',
'02.001.0007700689',
'02.001.0007701004',
'02.001.0007700888',
'02.001.0007700890',
'02.001.0007701005',
'02.001.0007700951',
'02.001.0007701008',
'02.001.0007701040',
'02.001.0007701014',
'02.001.0007700993',
'02.001.0007700940',
'02.001.0007701026',
'02.001.0007700097',
'02.001.0007701029',
'02.001.0007701030',
'02.001.0007701031',
'02.001.0007700083',
'02.001.0007701035',
'02.001.0007700930',
'02.001.0007701037')
order by field(NTSCode,'02.001.0007700492',
'02.001.0007700439',
'02.001.0007700929',
'02.001.0007700684',
'02.001.0007700685',
'02.001.0007700688',
'02.001.0007700689',
'02.001.0007701004',
'02.001.0007700888',
'02.001.0007700890',
'02.001.0007701005',
'02.001.0007700951',
'02.001.0007701008',
'02.001.0007701040',
'02.001.0007701014',
'02.001.0007700993',
'02.001.0007700940',
'02.001.0007701026',
'02.001.0007700097',
'02.001.0007701029',
'02.001.0007701030',
'02.001.0007701031',
'02.001.0007700083',
'02.001.0007701035',
'02.001.0007700930',
'02.001.0007701037')
";
var pl = dalProduct.GetList(query);
Assert.AreEqual(pl[0].NTSCode, "02.001.0007700492");
Assert.AreEqual(pl[24].NTSCode, "02.001.0007700930");
Assert.AreEqual(pl[10].NTSCode, "02.001.0007701005");
Assert.AreEqual(pl[4].NTSCode,"02.001.0007700685");
Console.WriteLine(pl.Count);
}
public TourRoleProvider(DalBase<Role> roleDal)
{
iRole =(DALRole)roleDal;
}
public static MDataColumn GetColumns(string tableName, string conn)
{
string key = GetSchemaKey(tableName, conn);
#region 缓存检测
if (_ColumnCache.ContainsKey(key))
{
return(_ColumnCache[key].Clone());
}
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
string fullPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath + key + ".ts";
if (System.IO.File.Exists(fullPath))
{
MDataColumn columns = MDataColumn.CreateFrom(fullPath);
if (columns.Count > 0)
{
CacheManage.LocalInstance.Set(key, columns.Clone(), 1440);
return(columns);
}
}
}
#endregion
string fixName;
conn = CrossDB.GetConn(tableName, out fixName, conn ?? AppConfig.DB.DefaultConn);
tableName = fixName;
if (conn == null)
{
return(null);
}
MDataColumn mdcs = null;
using (DalBase dbHelper = DalCreate.CreateDal(conn))
{
DataBaseType dalType = dbHelper.DataBaseType;
if (dalType == DataBaseType.Txt || dalType == DataBaseType.Xml)
{
#region 文本数据库处理。
if (!tableName.Contains(" ")) // || tableName.IndexOfAny(Path.GetInvalidPathChars()) == -1
{
tableName = SqlFormat.NotKeyword(tableName); //处理database..tableName;
tableName = Path.GetFileNameWithoutExtension(tableName); //视图表，带“.“的，会出问题
string fileName = dbHelper.Con.DataSource + tableName + (dalType == DataBaseType.Txt ? ".txt" : ".xml");
mdcs = MDataColumn.CreateFrom(fileName);
mdcs.DataBaseType = dalType;
mdcs.Conn = conn;
}
#endregion
}
else
{
#region 其它数据库
mdcs = new MDataColumn();
mdcs.Conn = conn;
mdcs.TableName = tableName;
mdcs.DataBaseType = dalType;
tableName = SqlFormat.Keyword(tableName, dbHelper.DataBaseType);//加上关键字：引号
//如果table和helper不在同一个库
DalBase helper = dbHelper.ResetDalBase(tableName);
helper.IsRecordDebugInfo = false || AppDebug.IsContainSysSql;//内部系统，不记录SQL表结构语句。
try
{
bool isView = tableName.Contains(" ");//是否视图。
if (!isView)
{
isView = CrossDB.Exists(tableName, "V", conn);
}
if (!isView)
{
TableInfo info = CrossDB.GetTableInfoByName(mdcs.TableName, conn);
if (info != null)
{
mdcs.Description = info.Description;
}
}
MCellStruct mStruct = null;
SqlDbType sqlType = SqlDbType.NVarChar;
if (isView)
{
string sqlText = SqlFormat.BuildSqlWithWhereOneEqualsTow(tableName);// string.Format("select * from {0} where 1=2", tableName);
mdcs = GetViewColumns(sqlText, ref helper);
}
else
{
mdcs.AddRelateionTableName(SqlFormat.NotKeyword(tableName));
switch (dalType)
{
case DataBaseType.MsSql:
case DataBaseType.Oracle:
case DataBaseType.MySql:
case DataBaseType.Sybase:
case DataBaseType.PostgreSQL:
#region Sql
string sql = string.Empty;
if (dalType == DataBaseType.MsSql)
{
#region Mssql
string dbName = null;
if (!helper.Version.StartsWith("08"))
{
//先获取同义词，检测是否跨库
string realTableName = Convert.ToString(helper.ExeScalar(string.Format(MSSQL_SynonymsName, SqlFormat.NotKeyword(tableName)), false));
if (!string.IsNullOrEmpty(realTableName))
{
string[] items = realTableName.Split('.');
tableName = realTableName;
if (items.Length > 0) //跨库了
{
dbName = realTableName.Split('.')[0];
}
}
}
sql = GetMSSQLColumns(helper.Version.StartsWith("08"), dbName ?? helper.DataBaseName);
#endregion
}
else if (dalType == DataBaseType.MySql)
{
sql = GetMySqlColumns(helper.DataBaseName);
}
else if (dalType == DataBaseType.Oracle)
{
tableName = tableName.ToUpper(); //Oracle转大写。
//先获取同义词，不检测是否跨库
string realTableName = Convert.ToString(helper.ExeScalar(string.Format(Oracle_SynonymsName, SqlFormat.NotKeyword(tableName)), false));
if (!string.IsNullOrEmpty(realTableName))
{
tableName = realTableName;
}
sql = GetOracleColumns();
}
else if (dalType == DataBaseType.Sybase)
{
tableName = SqlFormat.NotKeyword(tableName);
sql = GetSybaseColumns();
}
else if (dalType == DataBaseType.PostgreSQL)
{
sql = GetPostgreColumns();
}
helper.AddParameters("TableName", SqlFormat.NotKeyword(tableName), DbType.String, 150, ParameterDirection.Input);
DbDataReader sdr = helper.ExeDataReader(sql, false);
if (sdr != null)
{
long maxLength;
bool isAutoIncrement = false;
short scale = 0;
string sqlTypeName = string.Empty;
while (sdr.Read())
{
short.TryParse(Convert.ToString(sdr["Scale"]), out scale);
if (!long.TryParse(Convert.ToString(sdr["MaxSize"]), out maxLength)) //mysql的长度可能大于int.MaxValue
{
maxLength = -1;
}
else if (maxLength > int.MaxValue)
{
maxLength = int.MaxValue;
}
sqlTypeName = Convert.ToString(sdr["SqlType"]);
sqlType = DataType.GetSqlType(sqlTypeName);
isAutoIncrement = Convert.ToBoolean(sdr["IsAutoIncrement"]);
mStruct = new MCellStruct(mdcs.DataBaseType);
mStruct.ColumnName = Convert.ToString(sdr["ColumnName"]).Trim();
mStruct.OldName = mStruct.ColumnName;
mStruct.SqlType = sqlType;
mStruct.IsAutoIncrement = isAutoIncrement;
mStruct.IsCanNull = Convert.ToBoolean(sdr["IsNullable"]);
mStruct.MaxSize = (int)maxLength;
mStruct.Scale = scale;
mStruct.Description = Convert.ToString(sdr["Description"]);
mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, sdr["DefaultValue"], 0, sqlType);
mStruct.IsPrimaryKey = Convert.ToString(sdr["IsPrimaryKey"]) == "1";
switch (dalType)
{
case DataBaseType.MsSql:
case DataBaseType.MySql:
case DataBaseType.Oracle:
mStruct.IsUniqueKey = Convert.ToString(sdr["IsUniqueKey"]) == "1";
mStruct.IsForeignKey = Convert.ToString(sdr["IsForeignKey"]) == "1";
mStruct.FKTableName = Convert.ToString(sdr["FKTableName"]);
break;
}
mStruct.SqlTypeName = sqlTypeName;
mStruct.TableName = SqlFormat.NotKeyword(tableName);
mdcs.Add(mStruct);
}
sdr.Close();
if (dalType == DataBaseType.Oracle && mdcs.Count > 0) //默认没有自增概念，只能根据情况判断。
{
MCellStruct firstColumn = mdcs[0];
if (firstColumn.IsPrimaryKey && firstColumn.ColumnName.ToLower().Contains("id") && firstColumn.Scale == 0 && DataType.GetGroup(firstColumn.SqlType) == 1 && mdcs.JointPrimary.Count == 1)
{
firstColumn.IsAutoIncrement = true;
}
}
}
#endregion
break;
case DataBaseType.SQLite:
#region SQlite
if (helper.Con.State != ConnectionState.Open)
{
helper.Con.Open();
}
DataTable sqliteDt = helper.Con.GetSchema("Columns", new string[] { null, null, SqlFormat.NotKeyword(tableName) });
if (!helper.IsOpenTrans)
{
helper.Con.Close();
}
int size;
short sizeScale;
string dataTypeName = string.Empty;
foreach (DataRow row in sqliteDt.Rows)
{
object len = row["NUMERIC_PRECISION"];
if (len == null || len == DBNull.Value)
{
len = row["CHARACTER_MAXIMUM_LENGTH"];
}
short.TryParse(Convert.ToString(row["NUMERIC_SCALE"]), out sizeScale);
if (!int.TryParse(Convert.ToString(len), out size)) //mysql的长度可能大于int.MaxValue
{
size = -1;
}
dataTypeName = Convert.ToString(row["DATA_TYPE"]);
if (dataTypeName == "text" && size > 0)
{
sqlType = DataType.GetSqlType("varchar");
}
else
{
sqlType = DataType.GetSqlType(dataTypeName);
}
//COLUMN_NAME,DATA_TYPE,PRIMARY_KEY,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH AUTOINCREMENT
mStruct = new MCellStruct(row["COLUMN_NAME"].ToString(), sqlType, Convert.ToBoolean(row["AUTOINCREMENT"]), Convert.ToBoolean(row["IS_NULLABLE"]), size);
mStruct.Scale = sizeScale;
mStruct.Description = Convert.ToString(row["DESCRIPTION"]);
mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, row["COLUMN_DEFAULT"], 0, sqlType); //"COLUMN_DEFAULT"
mStruct.IsPrimaryKey = Convert.ToBoolean(row["PRIMARY_KEY"]);
mStruct.SqlTypeName = dataTypeName;
mStruct.TableName = SqlFormat.NotKeyword(tableName);
mdcs.Add(mStruct);
}
#endregion
break;
case DataBaseType.Access:
#region Access
DataTable keyDt, valueDt;
string sqlText = SqlFormat.BuildSqlWithWhereOneEqualsTow(tableName);// string.Format("select * from {0} where 1=2", tableName);
OleDbConnection con = new OleDbConnection(helper.Con.ConnectionString);
OleDbCommand com = new OleDbCommand(sqlText, con);
con.Open();
keyDt = com.ExecuteReader(CommandBehavior.KeyInfo).GetSchemaTable();
valueDt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, SqlFormat.NotKeyword(tableName) });
con.Close();
con.Dispose();
if (keyDt != null && valueDt != null)
{
string columnName = string.Empty, sqlTypeName = string.Empty;
bool isKey = false, isCanNull = true, isAutoIncrement = false;
int maxSize = -1;
short maxSizeScale = 0;
SqlDbType sqlDbType;
foreach (DataRow row in keyDt.Rows)
{
columnName = row["ColumnName"].ToString();
isKey = Convert.ToBoolean(row["IsKey"]); //IsKey
isCanNull = Convert.ToBoolean(row["AllowDBNull"]); //AllowDBNull
isAutoIncrement = Convert.ToBoolean(row["IsAutoIncrement"]);
sqlTypeName = Convert.ToString(row["DataType"]);
sqlDbType = DataType.GetSqlType(sqlTypeName);
short.TryParse(Convert.ToString(row["NumericScale"]), out maxSizeScale);
if (Convert.ToInt32(row["NumericPrecision"]) > 0) //NumericPrecision
{
maxSize = Convert.ToInt32(row["NumericPrecision"]);
}
else
{
long len = Convert.ToInt64(row["ColumnSize"]);
if (len > int.MaxValue)
{
maxSize = int.MaxValue;
}
else
{
maxSize = (int)len;
}
}
mStruct = new MCellStruct(columnName, sqlDbType, isAutoIncrement, isCanNull, maxSize);
mStruct.Scale = maxSizeScale;
mStruct.IsPrimaryKey = isKey;
mStruct.SqlTypeName = sqlTypeName;
mStruct.TableName = SqlFormat.NotKeyword(tableName);
foreach (DataRow item in valueDt.Rows)
{
if (columnName == item[3].ToString()) //COLUMN_NAME
{
if (item[8].ToString() != "")
{
mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, item[8], 0, sqlDbType); //"COLUMN_DEFAULT"
}
break;
}
}
mdcs.Add(mStruct);
}
}
#endregion
break;
}
}
}
catch (Exception err)
{
Log.Write(err, LogType.DataBase);
//helper.DebugInfo.Append(err.Message);
}
#endregion
}
}
if (mdcs.Count > 0)
{
//移除被标志的列：
string[] fields = AppConfig.DB.HiddenFields.Split(',');
foreach (string item in fields)
{
string field = item.Trim();
if (!string.IsNullOrEmpty(field) & mdcs.Contains(field))
{
mdcs.Remove(field);
}
}
}
#region 缓存设置
if (!_ColumnCache.ContainsKey(key) && mdcs.Count > 0)
{
_ColumnCache.Add(key, mdcs.Clone());
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
string folderPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath;
if (!System.IO.Directory.Exists(folderPath))
{
System.IO.Directory.CreateDirectory(folderPath);
}
mdcs.WriteSchema(folderPath + key + ".ts");
}
}
#endregion
return(mdcs);
}
internal bool LoadDataInsert(DataBaseType dalType, bool keepid)
{
bool fillGUID = CheckGUIDAndDateTime(dalType);
bool isNeedCreateDal = (_dalHelper == null);
if (isNeedCreateDal && dalType != DataBaseType.Oracle)
{
_dalHelper = DalCreate.CreateDal(_Conn);
_dalHelper.IsWriteLogOnError = false;
}
string path = MDataTableToFile(mdt, fillGUID ? true : keepid, dalType);
string formatSql = dalType == DataBaseType.MySql ? SqlCreate.MySqlBulkCopySql : SqlCreate.OracleBulkCopySql;
string sql = string.Format(formatSql, path, SqlFormat.Keyword(mdt.TableName, dalType),
AppConst.SplitChar, SqlCreate.GetColumnName(mdt.Columns, keepid, dalType));
if (dalType == DataBaseType.Oracle)
{
string ctlPath = CreateCTL(sql, path);
sql = string.Format(SqlCreate.OracleSqlidR, "sa/123456@ORCL", ctlPath);//只能用进程处理
}
try
{
if (dalType == DataBaseType.Oracle)
{
return(ExeSqlLoader(sql));
}
else
{
bool isGoOn = true;
if (IsTruncate)
{
_dalHelper.IsOpenTrans = true;//开启事务
isGoOn = _dalHelper.ExeNonQuery(string.Format(SqlCreate.TruncateTable, SqlFormat.Keyword(mdt.TableName, dalTypeTo)), false) != -2;
}
if (isGoOn && _dalHelper.ExeNonQuery(sql, false) != -2)
{
return(true);
}
}
}
catch (Exception err)
{
if (err.InnerException != null)
{
err = err.InnerException;
}
sourceTable.DynamicData = err;
Log.Write(err, LogType.DataBase);
}
finally
{
if (isNeedCreateDal && _dalHelper != null)
{
_dalHelper.EndTransaction();
_dalHelper.Dispose();
_dalHelper = null;
}
IOHelper.Delete(path);//删除文件。
}
return(false);
}
internal bool BulkCopyUpdate()
{
int count = 0, pageSize = 5000;
MDataTable dt = null;
bool result = false;
using (MAction action = new MAction(mdt.TableName, _Conn))
{
action.SetAopState(Aop.AopOp.CloseAll);
if (action.DataBaseVersion.StartsWith("08"))
{
pageSize = 1000;
}
count = mdt.Rows.Count / pageSize + 1;
DalBase sourceHelper = action.dalHelper;
if (_dalHelper != null)
{
action.dalHelper = _dalHelper;
}
else
{
action.BeginTransation();
}
MCellStruct keyColumn = jointPrimaryIndex != null ? mdt.Columns[jointPrimaryIndex[0]] : mdt.Columns.FirstPrimary;
string columnName = keyColumn.ColumnName;
for (int i = 0; i < count; i++)
{
dt = mdt.Select(i + 1, pageSize, null);//分页读取
if (dt != null && dt.Rows.Count > 0)
{
#region 核心逻辑
string whereIn = SqlCreate.GetWhereIn(keyColumn, dt.GetColumnItems <string>(columnName, BreakOp.NullOrEmpty, true), action.DataBaseType);
MDataTable dtData = action.Select(whereIn); //获取远程数据。
dtData.Load(dt, keyColumn); //重新加载赋值。
//处理如果存在IsDeleted，会被转Update（导致后续无法Insert）、外层也有判断，不会进来。
result = action.Delete(whereIn, true);
if (result)
{
dtData.DynamicData = action;
result = dtData.AcceptChanges(AcceptOp.InsertWithID);
}
if (!result)
{
if (_dalHelper == null) //有外部时由外部控制，没外部时直接回滚。
{
action.RollBack(); //回滚被删除的代码。
}
break;
}
#endregion
}
}
if (_dalHelper == null)
{
action.EndTransation();
}
else
{
action.dalHelper = sourceHelper;//还原。
}
}
return(result);
}
internal bool MsSqlBulkCopyInsert(bool keepid)
{
SqlTransaction sqlTran = null;
SqlConnection con = null;
bool isCreateDal = false;
try
{
CheckGUIDAndDateTime(DataBaseType.MsSql);
string conn = AppConfig.GetConn(_Conn);
if (_dalHelper == null)
{
if (IsTruncate)
{
isCreateDal = true;
_dalHelper = DalCreate.CreateDal(conn);
}
else
{
con = new SqlConnection(conn);
con.Open();
}
}
bool isGoOn = true;
if (_dalHelper != null)
{
if (IsTruncate)
{
_dalHelper.IsOpenTrans = true;
if (_dalHelper.ExeNonQuery(string.Format(SqlCreate.TruncateTable, SqlFormat.Keyword(mdt.TableName, dalTypeTo)), false) == -2)
{
isGoOn = false;
sourceTable.DynamicData = _dalHelper.DebugInfo;
Log.Write(_dalHelper.DebugInfo.ToString(), LogType.DataBase);
}
}
if (isGoOn)
{
con = _dalHelper.Con as SqlConnection;
_dalHelper.OpenCon(null, AllowConnLevel.MasterBackup);//如果未开启，则开启，打开链接后，如果以前没执行过数据，事务对象为空，这时会产生事务对象
sqlTran = _dalHelper._tran as SqlTransaction;
}
}
if (isGoOn)
{
using (SqlBulkCopy sbc = new SqlBulkCopy(con, (keepid ? SqlBulkCopyOptions.KeepIdentity : SqlBulkCopyOptions.Default) | SqlBulkCopyOptions.FireTriggers, sqlTran))
{
sbc.BatchSize = 100000;
sbc.DestinationTableName = SqlFormat.Keyword(mdt.TableName, DataBaseType.MsSql);
sbc.BulkCopyTimeout = AppConfig.DB.CommandTimeout;
foreach (MCellStruct column in mdt.Columns)
{
sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
if (AppConfig.IsAspNetCore)
{
sbc.WriteToServer(mdt.ToDataTable());
}
else
{
sbc.WriteToServer(mdt);
}
}
}
return(true);
}
catch (Exception err)
{
sourceTable.DynamicData = err;
Log.Write(err, LogType.DataBase);
}
finally
{
if (_dalHelper == null)
{
con.Close();
con = null;
}
else if (isCreateDal)
{
_dalHelper.EndTransaction();
_dalHelper.Dispose();
}
}
return(false);
}
internal bool Auto()
{
bool result = true;
using (MAction action = new MAction(mdt.TableName, _Conn))
{
action.SetAopState(Aop.AopOp.CloseAll);
DalBase sourceHelper = action.dalHelper;
if (_dalHelper != null)
{
action.dalHelper = _dalHelper;
}
else
{
action.BeginTransation();
}
action.dalHelper.IsRecordDebugInfo = false;//屏蔽SQL日志记录 2000数据库大量的In条件会超时。
if ((jointPrimaryIndex != null && jointPrimaryIndex.Count == 1) || (jointPrimaryIndex == null && mdt.Columns.JointPrimary.Count == 1))
//jointPrimaryIndex == null && mdt.Columns.JointPrimary.Count == 1 && mdt.Rows.Count <= 10000
//&& (!action.DalVersion.StartsWith("08") || mdt.Rows.Count < 1001)) //只有一个主键-》组合成In远程查询返回数据-》
{
#region 新逻辑
MCellStruct keyColumn = jointPrimaryIndex != null ? mdt.Columns[jointPrimaryIndex[0]] : mdt.Columns.FirstPrimary;
string columnName = keyColumn.ColumnName;
//计算分组处理
int pageSize = 5000;
if (action.DataBaseVersion.StartsWith("08"))
{
pageSize = 1000;
}
int count = mdt.Rows.Count / pageSize + 1;
for (int i = 0; i < count; i++)
{
MDataTable dt = mdt.Select(i + 1, pageSize, null);//分页读取
if (dt != null && dt.Rows.Count > 0)
{
string whereIn = SqlCreate.GetWhereIn(keyColumn, dt.GetColumnItems <string>(columnName, BreakOp.NullOrEmpty, true), action.DataBaseType);
action.SetSelectColumns(columnName);
MDataTable keyTable = action.Select(whereIn); //拿到数据，准备分拆上市
MDataTable[] dt2 = dt.Split(SqlCreate.GetWhereIn(keyColumn, keyTable.GetColumnItems <string>(columnName, BreakOp.NullOrEmpty, true), DataBaseType.None)); //这里不需要格式化查询条件。
result = dt2[0].Rows.Count == 0;
if (!result)
{
MDataTable updateTable = dt2[0];
updateTable.SetState(2, BreakOp.Null);
updateTable.DynamicData = action;
result = updateTable.AcceptChanges(AcceptOp.Update, _Conn, columnName);
if (!result)
{
sourceTable.DynamicData = updateTable.DynamicData;
}
}
if (result && dt2[1].Rows.Count > 0)
{
MDataTable insertTable = dt2[1];
insertTable.DynamicData = action;
bool keepid = !insertTable.Rows[0].PrimaryCell.IsNullOrEmpty;
result = insertTable.AcceptChanges((keepid ? AcceptOp.InsertWithID : AcceptOp.Insert), _Conn, columnName);
if (!result)
{
sourceTable.DynamicData = insertTable.DynamicData;
}
}
}
}
#endregion
#region 旧逻辑，已不用 分拆处理 本地比较分拆两个表格【更新和插入】-》分开独立处理。
/*
* string columnName = mdt.Columns.FirstPrimary.ColumnName;
* string whereIn = SqlCreate.GetWhereIn(mdt.Columns.FirstPrimary, mdt.GetColumnItems<string>(columnName, BreakOp.NullOrEmpty, true), action.DalType);
* action.SetSelectColumns(mdt.Columns.FirstPrimary.ColumnName);
* dt = action.Select(whereIn);
*
* MDataTable[] dt2 = mdt.Split(SqlCreate.GetWhereIn(mdt.Columns.FirstPrimary, dt.GetColumnItems<string>(columnName, BreakOp.NullOrEmpty, true), DalType.None));//这里不需要格式化查询条件。
* result = dt2[0].Rows.Count == 0;
* if (!result)
* {
* dt2[0].SetState(2, BreakOp.Null);
* dt2[0].DynamicData = action;
* MDataTableBatchAction m1 = new MDataTableBatchAction(dt2[0], _Conn);
* m1.SetJoinPrimaryKeys(new string[] { columnName });
* result = m1.Update();
* if (!result)
* {
* sourceTable.DynamicData = dt2[0].DynamicData;
* }
* }
* if (result && dt2[1].Rows.Count > 0)
* {
* dt2[1].DynamicData = action;
* MDataTableBatchAction m2 = new MDataTableBatchAction(dt2[1], _Conn);
* m2.SetJoinPrimaryKeys(new string[] { columnName });
* result = m2.Insert(!dt2[1].Rows[0].PrimaryCell.IsNullOrEmpty);
* if (!result)
* {
* sourceTable.DynamicData = dt2[1].DynamicData;
* }
* }
*/
#endregion
}
else
{
// action.BeginTransation();
foreach (MDataRow row in mdt.Rows)
{
#region 循环处理
action.ResetTable(row, false);
string where = SqlCreate.GetWhere(action.DataBaseType, GetJoinPrimaryCell(row));
bool isExists = action.Exists(where);
if (action.RecordsAffected == -2)
{
result = false;
}
else
{
if (!isExists)
{
action.AllowInsertID = !row.PrimaryCell.IsNullOrEmpty;
action.Data.SetState(1, BreakOp.Null);
result = action.Insert(InsertOp.None);
}
else
{
action.Data.SetState(2);
result = action.Update(where);
}
}
if (!result)
{
string msg = "Error On : MDataTable.AcceptChanges.Auto." + mdt.TableName + " : [" + where + "] : " + action.DebugInfo;
sourceTable.DynamicData = msg;
Log.Write(msg, LogType.DataBase);
break;
}
#endregion
}
}
action.dalHelper.IsRecordDebugInfo = true;//恢复SQL日志记录
if (_dalHelper == null)
{
action.EndTransation();
}
else
{
action.dalHelper = sourceHelper;//还原
}
}
return(result);
}
internal bool NormalUpdate()
{
List <int> indexList = new List <int>();
bool result = true;
using (MAction action = new MAction(mdt.TableName, _Conn))
{
action.SetAopState(Aop.AopOp.CloseAll);
DalBase sourceHelper = action.dalHelper;
if (_dalHelper != null)
{
action.dalHelper = _dalHelper;
}
else
{
action.BeginTransation();
}
action.dalHelper.IsRecordDebugInfo = false;//屏蔽SQL日志记录
MDataRow row;
for (int i = 0; i < mdt.Rows.Count; i++)
{
row = mdt.Rows[i];
if (row.GetState(true) > 1)
{
action.ResetTable(row, false);
string where = SqlCreate.GetWhere(action.DataBaseType, GetJoinPrimaryCell(row));
result = action.Update(where) || action.RecordsAffected == 0;//没有可更新的数据，也返回true
if (action.RecordsAffected > 0)
{
sourceTable.RecordsAffected += action.RecordsAffected;//记录总更新的数量。
}
if (!result)
{
sourceTable.RecordsAffected = 0;
string msg = "Error On : MDataTable.AcceptChanges.Update." + mdt.TableName + " : where (" + where + ") : " + action.DebugInfo;
sourceTable.DynamicData = msg;
Log.Write(msg, LogType.DataBase);
break;
}
else
{
indexList.Add(i);
}
}
}
action.dalHelper.IsRecordDebugInfo = true;//恢复SQL日志记录
if (_dalHelper == null)
{
action.EndTransation();
}
else
{
action.dalHelper = sourceHelper;//恢复原来，避免外来的链接被关闭。
}
}
if (result)
{
foreach (int index in indexList)
{
mdt.Rows[index].SetState(0);
}
indexList.Clear();
indexList = null;
}
return(result);
}
/*
* internal bool SybaseBulkCopyInsert()
* {
*
* // string a, b, c;
* string conn = DalCreate.FormatConn(DalType.Sybase, AppConfig.GetConn(_Conn));
*
* using (Sybase.Data.AseClient.AseBulkCopy sbc = new Sybase.Data.AseClient.AseBulkCopy(conn, Sybase.Data.AseClient.AseBulkCopyOptions.Keepidentity))
* {
* sbc.BatchSize = 100000;
* sbc.DestinationTableName = mdt.TableName;
* foreach (MCellStruct column in mdt.Columns)
* {
* Sybase.Data.AseClient.AseBulkCopyColumnMapping ac = new Sybase.Data.AseClient.AseBulkCopyColumnMapping();
* ac.SourceColumn = ac.DestinationColumn = column.ColumnName;
* sbc.ColumnMappings.Add(ac);
* }
* sbc.WriteToServer(mdt.ToDataTable());
* }
* return true;
*
*
* //Assembly ass = SybaseDal.GetAssembly();
*
* //object sbc = ass.CreateInstance("Sybase.Data.AseClient.AseBulkCopy", false, BindingFlags.CreateInstance, null, new object[] { conn }, null, null);
*
* //Type sbcType = sbc.GetType();
* //try
* //{
*
* // sbcType.GetProperty("BatchSize").SetValue(sbc, 100000, null);
* // sbcType.GetProperty("DestinationTableName").SetValue(sbc, SqlFormat.Keyword(mdt.TableName, DalType.Sybase), null);
* // PropertyInfo cInfo = sbcType.GetProperty("ColumnMappings");
* // object cObj = cInfo.GetValue(sbc, null);
* // MethodInfo addMethod = cInfo.PropertyType.GetMethods()[2];
* // foreach (MCellStruct column in mdt.Columns)
* // {
* // object columnMapping = ass.CreateInstance("Sybase.Data.AseClient.AseBulkCopyColumnMapping", false, BindingFlags.CreateInstance, null, new object[] { column.ColumnName, column.ColumnName }, null, null);
* // addMethod.Invoke(cObj, new object[] { columnMapping });
* // }
* // //Oracle.DataAccess.Client.OracleBulkCopy ttt = sbc as Oracle.DataAccess.Client.OracleBulkCopy;
* // //ttt.WriteToServer(mdt);
* // sbcType.GetMethods()[14].Invoke(sbc, new object[] { mdt.ToDataTable() });
* // return true;
* //}
* //catch (Exception err)
* //{
* // Log.Write(err);
* // return false;
* //}
* //finally
* //{
* // sbcType.GetMethod("Dispose").Invoke(sbc, null);
* //}
* }
*/
#endregion
internal bool NomalInsert(bool keepid)
{
bool result = true;
using (MAction action = new MAction(mdt.TableName, _Conn))
{
DalBase sourceHelper = action.dalHelper;
action.SetAopState(Aop.AopOp.CloseAll);
if (_dalHelper != null)
{
action.dalHelper = _dalHelper;
}
else
{
action.BeginTransation(); //事务由外部控制
}
action.dalHelper.IsRecordDebugInfo = false; //屏蔽SQL日志记录
if (keepid)
{
action.SetidentityInsertOn();
}
bool isGoOn = true;
if (IsTruncate)
{
if (dalTypeTo == DataBaseType.Txt || dalTypeTo == DataBaseType.Xml)
{
action.Delete("1=1");
}
else if (action.dalHelper.ExeNonQuery(string.Format(SqlCreate.TruncateTable, SqlFormat.Keyword(action.TableName, dalTypeTo)), false) == -2)
{
isGoOn = false;
sourceTable.DynamicData = action.DebugInfo;
Log.Write(action.DebugInfo, LogType.DataBase);
}
}
if (isGoOn)
{
MDataRow row;
for (int i = 0; i < mdt.Rows.Count; i++)
{
row = mdt.Rows[i];
action.ResetTable(row, false);
action.Data.SetState(1, BreakOp.Null);
result = action.Insert(InsertOp.None);
sourceTable.RecordsAffected = i;
if (!result)
{
string msg = "Error On : MDataTable.AcceptChanges.Insert." + mdt.TableName + " : [" + row.PrimaryCell.Value + "] : " + action.DebugInfo;
sourceTable.DynamicData = msg;
Log.Write(msg, LogType.DataBase);
break;
}
}
}
if (keepid)
{
action.SetidentityInsertOff();
}
if (_dalHelper == null)
{
action.EndTransation();
}
action.dalHelper.IsRecordDebugInfo = true; //恢复SQL日志记录
action.dalHelper = sourceHelper; //恢复原来，避免外来的链接被关闭。
}
return(result);
}
private void CheckSingleFolder2Test(
string folderName
, int amountProductsHasPicture
, int amountProductNotHasPicture
, int amountProductExistsInDb
, int amountImagesHasNotProduct
, bool needCheckDataBase
, DALProduct dalProduct
, DALSupplier dalSupplier
, IFormatSerialNoPersistent ifsp
, DalBase<Product> dalBaseProduct)
{
string folderFullPath = Environment.CurrentDirectory + "\\TestFiles\\FormatCheck\\" + folderName + "\\";
SingleFolderImport checker = new SingleFolderImport(folderFullPath);
BizProduct bizP = new BizProduct();
bizP.DalProduct = dalProduct;
bizP.DalBase = dalBaseProduct;
BizSupplier bizS = new BizSupplier();
bizS.DalSupplier = dalSupplier;
bizP.DalSupplier = dalSupplier;
checker.NeedCheckWithDB = needCheckDataBase;
checker.Import(bizP, bizS, ifsp);
IList<Product> productsHasPicture = checker.ProductsPassedDBCheck
, productsNotHasPicture = checker.ProductsNotHasImage
, productsExistedInDB = checker.ProductsExistedInDB;
IList<FileInfo> imagesHasProduct = checker.ImagesHasProduct
, imagesHasNotProduct = checker.ImagesNotHasProduct;
// Assert.AreEqual("Success", FormatChecker.Check(folderContainsExcelAndImages));
Assert.AreEqual(amountProductsHasPicture, checker.ProductsPassedDBCheck.Count);
Assert.AreEqual(amountProductNotHasPicture, checker.ProductsNotHasImage.Count);
Assert.AreEqual(amountProductExistsInDb, checker.ProductsExistedInDB.Count);
Assert.AreEqual(amountImagesHasNotProduct, checker.ImagesNotHasProduct.Count);
DateTime beginSaveResult = DateTime.Now;
string saveFolder = Environment.CurrentDirectory + "\\TestFiles\\FormatCheck\\检测结果\\";
string saveFolderOfSupplier;
if (productsHasPicture.Count > 0) saveFolderOfSupplier = bizS.GetByCode(productsHasPicture[0].SupplierCode).Name;
else if (productsNotHasPicture.Count > 0) saveFolderOfSupplier = bizS.GetByCode(productsNotHasPicture[0].SupplierCode).Name;
else throw new Exception();
DirectoryInfo dirOfSavedSupplier = new DirectoryInfo(saveFolder + "合格数据\\" + saveFolderOfSupplier + "\\");
if (dirOfSavedSupplier.Exists)
{
dirOfSavedSupplier.Delete(true);
}
string supplierName = string.Empty;
if (productsExistedInDB.Count > 0) supplierName = bizS.GetByCode(productsExistedInDB[0].SupplierCode).Name;
else if (productsHasPicture.Count > 0) supplierName = bizS.GetByCode(productsHasPicture[0].SupplierCode).Name;
else if (productsNotHasPicture.Count > 0) supplierName = bizS.GetByCode(productsNotHasPicture[0].SupplierCode).Name;
else
{
return;
}
supplierName = StringHelper.ReplaceInvalidChaInFileName(supplierName, string.Empty);
checker.HandlerCheckResult(
supplierName,
saveFolder,
WebProductImagesPath
);
Assert.AreEqual(productsHasPicture.Count, dirOfSavedSupplier.GetImageFiles().ToArray().Length);
Console.WriteLine("Time Cost CheckImage:" + (DateTime.Now - beginSaveResult).TotalSeconds);
}