|
|
using System;using System.Collections.Generic;using System.Linq;using System.Text;using ICSSoft.Frame.Data.Entity;using System.Data;using ICSSoft.Base.Config.AppConfig;using ICSSoft.Base.Config.DBHelper;
namespace ICSSoft.Frame.Data.DAL{ public class ICSUserInfoDAL { public static void DeleteThenInsert(List<ICSUserInfo> list) { FramDataContext db = new FramDataContext(AppConfig.AppConnectString); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { var lines = db.ICSUserInfo.Where(a => a.UserCode == list[0].UserCode && a.WorkPoint == list[0].WorkPoint); db.ICSUserInfo.DeleteAllOnSubmit(lines); db.SubmitChanges(); db.ICSUserInfo.InsertAllOnSubmit(list); db.SubmitChanges(); db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } finally { db.Connection.Close(); }
}
public static int[] AddAndEdit(List<ICSUserInfo> users) { int insert = 0; int update = 0; FramDataContext db = new FramDataContext(AppConfig.AppConnectString); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { foreach (ICSUserInfo user in users) { bool isNew = false; var line = db.ICSUserInfo.FirstOrDefault(a => a.UserCode == user.UserCode && a.WorkPoint == AppConfig.WorkPointCode); //var line = db.ICSUserInfo.FirstOrDefault(a => a.UserCode == user.UserCode && a.SEGCode == user.SEGCode && a.SGroup == user.SGroup && a.WorkPoint == AppConfig.WorkPointCode);
if (line == null) { isNew = true; line = new ICSUserInfo(); line.ID = AppConfig.GetGuid(); } line.UserCode = user.UserCode; line.SEGCode = user.SEGCode; line.SGroup = user.SGroup; line.FrontLine = user.FrontLine; line.Phone = user.Phone; line.Mail = user.Mail; line.ModifyDate = DateTime.Now; line.ModifyUserCode = AppConfig.UserCode; line.WorkPoint = AppConfig.WorkPointCode; if (isNew) { insert++; line.CreateDate = line.ModifyDate; line.CreateUserCode = line.ModifyUserCode; db.ICSUserInfo.InsertOnSubmit(line); } else { update++; } db.SubmitChanges(); } db.SubmitChanges(); db.Transaction.Commit(); return new int[2] { insert, update }; } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } finally { db.Connection.Close(); } }
/// <summary>
/// 删除用户所有关联的车间
/// </summary>
/// <param name="listUserCode"></param>
public static void DeleteAllLine(List<string> listUserCode) { FramDataContext db = new FramDataContext(AppConfig.AppConnectString); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { var lines = db.ICSUserInfo.Where(a => listUserCode.Contains(a.UserCode) && a.WorkPoint == AppConfig.WorkPointCode); db.ICSUserInfo.DeleteAllOnSubmit(lines);
db.SubmitChanges(); db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } }
/// <summary>
/// 删除用户一条关联的车间
/// </summary>
/// <param name="listUserCode"></param>
public static void DeleteOneLine(List<string> listID) { FramDataContext db = new FramDataContext(AppConfig.AppConnectString); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { var lines = db.ICSUserInfo.Where(a => listID.Contains(a.ID) && a.WorkPoint == AppConfig.WorkPointCode); db.ICSUserInfo.DeleteAllOnSubmit(lines);
db.SubmitChanges(); db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } }
public static DataTable Select(string UserCode) { string sql = @"
SELECT convert(bit,1) as isSelect, A.ID, A.UserCode,B.UserName, A.FrontLine, A.SEGCODE,C.SEGDESC, A.SGroup, A.Phone, A.Mail, A.CreateUserCode,A.CreateDate, A.ModifyUserCode,A.ModifyDate , CONVERT(BIT,0) AS [ADD]FROM dbo.ICSUserInfo ALEFT JOIN dbo.Sys_User B ON A.UserCode=B.UserCode AND A.WorkPoint=B.WorkPointCodeLEFT JOIN dbo.ICSSEG C ON A.SEGCODE=C.SEGCODE AND A.WorkPoint=C.WorkPointWHERE A.UserCode='{0}' AND A.WorkPoint ='" + AppConfig.WorkPointCode + "'";
sql = string.Format(sql, UserCode == null ? "" : UserCode); DataSet ds = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql); if (ds.Tables.Count == 0) { return null; } return ds.Tables[0]; }
public static bool AllowAdd(string UserCode) { FramDataContext db = new FramDataContext(AppConfig.AppConnectString); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { var line = db.ICSUserInfo.SingleOrDefault(a => a.UserCode == UserCode && a.WorkPoint == AppConfig.WorkPointCode); return line == null; } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } } public static bool ExistSGroup(string Sgroup) { string sql = @"SELECT 1 FROM dbo.Sys_EnumValues WHERE EnumKey='00022' AND StartFlag=1 AND WorkPointCode='" + AppConfig.WorkPointCode + "' AND EnumValue='{0}'"; sql = string.Format(sql, Sgroup); DataSet ds = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql); if (ds.Tables.Count == 0) { return false; } if (ds.Tables[0].Rows.Count == 0) { return false; } return true; }
public static bool ExistUserCode(string userCode) { string sql = @"SELECT 1 FROM dbo.Sys_User WHERE UserCode ='{0}' AND WorkPointCode='" + AppConfig.WorkPointCode + "'"; sql = string.Format(sql, userCode); DataSet ds = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql); if (ds.Tables.Count == 0) { return false; } if (ds.Tables[0].Rows.Count == 0) { return false; } return true; }
public static string QueryTeamUsed(List<string> teamList) { string sql = @"SELECT STUFF((SELECT ',' + T.SGroup FROM (SELECT DISTINCT SGroup FROM dbo.ICSUserInfo WHERE SGroup IN('{0}') AND WorkPoint='{1}')T FOR XML PATH('')), 1, 1, '') AS sgs "; sql = string.Format(sql, string.Join("','", teamList), AppConfig.WorkPointCode); DataTable dt = DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0]; return dt.Rows[0][0].ToString(); }
public static DataTable QueryUsersInTeams(List<string> teamList) { string sql = @"
SELECT DISTINCT A.SGroup, STUFF((SELECT ',' + UserCode FROM dbo.ICSUserInfo WHERE SGroup=A.SGroup AND WorkPoint='{1}' FOR XML PATH('')), 1, 1, '') AS USERS FROM ICSUserInfo A WHERE A.SGroup in ('{0}') AND A.WorkPoint='{1}' ";
sql = string.Format(sql, string.Join("','", teamList), AppConfig.WorkPointCode); return DBHelper.ExecuteDataset(AppConfig.AppConnectString, CommandType.Text, sql).Tables[0]; }
public static void Test(string testCode) { FramDataContext db = new FramDataContext(AppConfig.AppConnectString); db.Connection.Open(); db.Transaction = db.Connection.BeginTransaction(); try { IDbCommand cmd = db.GetCommand(db.ICSITEMLot); cmd.CommandText = "SELECT * FROM dbo.ICSITEMLot WHERE LotNO='55A22022011000000004100001'"; cmd.CommandType = CommandType.Text; cmd.Connection = db.Connection; IDataReader r = cmd.ExecuteReader();
db.Transaction.Commit(); } catch (Exception ex) { db.Transaction.Rollback(); throw ex; } finally { db.Connection.Close(); }
}
}}
|
