public void ClearBusinessData() { SqlService sqlService = SqlServiceFactory.GetSQLService(); string sql = @"select name from sysobjects where xtype='U' and name != 'User' and name != 'Role' and name != 'User_Role' and name != 'Resource' and name != 'Permission' and name != 'LoginInfo' and name != 'Log4net' and name != 'BrowseInfo' and name != 'sysdiagrams'"; List <string> listTableName = sqlService.QueryOneColumn(sql); List <string> listSql = new List <string>(); foreach (string tableName in listTableName) { sql = "delete from " + tableName; listSql.Add(sql); } sqlService.ExecuteTransactionReturnCount(listSql); }
private Hashtable GetDataBaseUnique(string tableName, XmlNode tableUniques) { try { SqlService sqlService = SqlServiceFactory.GetSQLService(); Hashtable ht = new Hashtable(); StringBuilder sql = new StringBuilder(); StringBuilder sbFields = new StringBuilder(); StringBuilder sbJoinTable = new StringBuilder(); foreach (XmlElement unique in tableUniques.ChildNodes) { if (unique.SelectSingleNode("joinTable") == null) { sbFields.Append(tableName); sbFields.Append("."); sbFields.Append(unique.SelectSingleNode("field").InnerText); sbFields.Append("+"); } else { XmlNode joinTable = unique.SelectSingleNode("joinTable"); sbFields.Append(joinTable.InnerText); sbFields.Append("."); sbFields.Append(unique.SelectSingleNode("field").InnerText); sbFields.Append("+"); sbJoinTable.Append(" join "); sbJoinTable.Append(joinTable.InnerText); sbJoinTable.Append(" on "); sbJoinTable.Append(joinTable.Attributes["onTable"].Value); sbJoinTable.Append("."); sbJoinTable.Append(joinTable.Attributes["onField"].Value); sbJoinTable.Append("="); sbJoinTable.Append(joinTable.InnerText); sbJoinTable.Append("."); sbJoinTable.Append(joinTable.Attributes["joinField"].Value); } } sbFields.Remove(sbFields.Length - 1, 1); sbFields.Append(" as [Key],"); sbFields.Append(tableName); sbFields.Append(".Id as [Value]"); sql.Append("select ").Append(sbFields).Append(" from ").Append(tableName).Append(sbJoinTable); DataTable dt = sqlService.QueryDataTable(sql.ToString()); for (int i = 0; i < dt.Rows.Count; i++) { ht.Add(dt.Rows[i]["Key"], dt.Rows[i]["Value"]); } return(ht); } catch { throw; } }
public void foo() { SqlServiceFactory <IDbContext> factory = new SqlServiceFactory <IDbContext>(); string path = factory.FindSelf(); Console.WriteLine(path); }
public bool ChangeUserStatus(string userId) { SqlService service = SqlServiceFactory.GetSQLService(); string sql = "update [User] set IsBan=(IsBan+1)%2 where Id=@userId"; List <SqlParameter> listPar = new List <SqlParameter>(); listPar.Add(new SqlParameter("@userId", userId)); return(service.ExecuteNonQueryReturnBool(sql, listPar)); }
public void GetService() { //usage SqlServiceFactory <IDbContext> factory = new SqlServiceFactory <IDbContext>(); dynamic sqlAccess = factory.CreateService <SqlDbAccess>(); sqlAccess.SetConnection("connectionString"); Console.WriteLine(sqlAccess.GetType()); }
public void test() { string s = ""; int l = s.Length; SqlService service = SqlServiceFactory.GetSQLService(); string sql = @"update Property set [des]='" + s + @"' where id='A8284ECC-91FC-47B5-A0B3-11EA6C78627E'"; service.ExecuteNonQueryReturnBool(sql); //for (int i = 0; i < 400; i++) //{ // s += "aaaaaaaaaa"; //} //var s1 = s; }
public List <string> GetUserIdsByRoleId(string roleId) { try { SqlService sqlService = SqlServiceFactory.GetSQLService(); string sql = "select UserId from User_Role where RoleId=@RoleId"; List <SqlParameter> listPar = new List <SqlParameter>(); listPar.Add(new SqlParameter("RoleId", roleId)); return(sqlService.QueryOneColumn(sql, listPar)); } catch { throw; } }
public List <Resource> GetAllResource() { try { using (IDbContextBase context = DbContextFactory.GetDbContext()) { SqlService sqlService = SqlServiceFactory.GetSQLService(); string sql = "select * from Resource where Id!='0'"; List <Resource> listResource = sqlService.GetEntities <Resource>(sql); return(listResource); } } catch { throw; } }
public List <string> GetResourceIdByRoleId(Guid roleId) { try { using (IDbContextBase context = DbContextFactory.GetDbContext()) { SqlService sqlService = SqlServiceFactory.GetSQLService(); string sql = "select ResourceId from Permission where RoleId=@RoleId"; List <SqlParameter> listPar = new List <SqlParameter>(); listPar.Add(new SqlParameter("RoleId", roleId)); return(sqlService.QueryOneColumn(sql, listPar)); } } catch { throw; } }
public List <DataTable> GetDataSet() { try { SqlService sqlService = SqlServiceFactory.GetSQLService(); XmlDocument dom = new XmlDocument(); List <DataTable> listTable = new List <DataTable>(); string path = AppDomain.CurrentDomain.BaseDirectory; string[] arr = path.Split('\\'); path = ""; for (int i = 0; i < arr.Length - 2; i++) { path += arr[i] + "\\"; } dom.Load(path + "ExportExcel2.xml");//装载XML文档 foreach (XmlElement sheet in dom.DocumentElement.ChildNodes) { StringBuilder fields = new StringBuilder(); StringBuilder joinTables = new StringBuilder(); string tableName = sheet.SelectSingleNode("table").InnerText; string sheetName = sheet.SelectSingleNode("name").InnerText; XmlNode columns = sheet.SelectSingleNode("columns"); foreach (XmlElement column in columns) { string field = column.SelectSingleNode("field").InnerText; string showColumn = column.SelectSingleNode("name").InnerText; if ("yes".Equals(column.GetAttribute("fk"))) { joinTables.Append(" left join "); joinTables.Append(column.GetAttribute("referenceTable")); joinTables.Append(" on "); if (column.SelectSingleNode("joinTable") == null) { joinTables.Append(tableName); } else { joinTables.Append(column.SelectSingleNode("joinTable").InnerText); } joinTables.Append("."); joinTables.Append(field); joinTables.Append("="); joinTables.Append(column.GetAttribute("referenceTable")); joinTables.Append("."); joinTables.Append(column.GetAttribute("referenceColumn")); fields.Append(column.GetAttribute("referenceTable")); fields.Append("."); fields.Append(column.GetAttribute("showColumn")); fields.Append(" as '"); fields.Append(showColumn); fields.Append("',"); } else { fields.Append(field); fields.Append(" as '"); fields.Append(showColumn); fields.Append("',"); } } if (fields.Length > 0) { fields.Remove(fields.Length - 1, 1); string sql = "select " + fields + " from " + tableName + " " + joinTables; DataTable dt = sqlService.QueryDataTable(sql); dt.TableName = sheetName; listTable.Add(dt); } } return(listTable); } catch { throw; } }
public RouteSqlRepository() { _sqlServerService = SqlServiceFactory.CreateService(); }
public void ImporttoExcel(string fileName) { SqlService sqlService = SqlServiceFactory.GetSQLService(); FileInfo existingFile = new FileInfo(fileName); Dictionary <string, int> dictHeader = new Dictionary <string, int>(); Hashtable hashTables = new Hashtable(); Hashtable hash = null; XmlDocument dom = new XmlDocument(); string path = AppDomain.CurrentDomain.BaseDirectory; string[] arr = path.Split('\\'); path = ""; for (int i = 0; i < arr.Length - 2; i++) { path += arr[i] + "\\"; } dom.Load(path + "ImportExcel2.xml");//装载XML文档 using (ExcelPackage package = new ExcelPackage(existingFile)) { ExcelWorksheet commonWorksheet = package.Workbook.Worksheets[1]; int colStart = commonWorksheet.Dimension.Start.Column; //工作区开始列 int colEnd = commonWorksheet.Dimension.End.Column; //工作区结束列 int rowStart = commonWorksheet.Dimension.Start.Row; //工作区开始行号 string mergeHeader = ""; for (int i = colStart; i <= colEnd; i++) { string headerName = commonWorksheet.Cells[rowStart, i].Value + "" + commonWorksheet.Cells[rowStart + 1, i].Value; if (commonWorksheet.Cells[rowStart, i].Merge) { if (commonWorksheet.Cells[rowStart, i].Value != null) { mergeHeader = commonWorksheet.Cells[rowStart, i].Value + ""; } else { headerName = mergeHeader + commonWorksheet.Cells[rowStart + 1, i].Value; } } headerName = headerName.Replace("\n", ""); dictHeader[headerName] = i; } bool loadData = false; foreach (ExcelWorksheet worksheet in package.Workbook.Worksheets) { int rowEnd = worksheet.Dimension.End.Row; //工作区结束行号 foreach (XmlElement model in dom.DocumentElement.ChildNodes) { string tableName = model.SelectSingleNode("name").InnerText; List <string> listSql = new List <string>(); XmlNode keys = model.SelectSingleNode("tableUniques"); if (!loadData) { hash = new Hashtable(); if (keys != null) { hash = GetDataBaseUnique(tableName, keys); } if (!hashTables.Contains(tableName)) { hashTables.Add(tableName, hash); } } else { hash = (Hashtable)hashTables[tableName]; } for (int i = rowStart + 2; i <= rowEnd; i++) { bool whitespace = false; string sql = ""; string fields = ""; string values = ""; XmlNode xmlFields = model.SelectSingleNode("fields"); string unique = ""; foreach (XmlElement field in xmlFields.ChildNodes) { string fieldName = field.SelectSingleNode("name").InnerText; string columnName = ""; object fieldValue = null; int row = i; //if (field.SelectSingleNode("row") != null) //{ // row = Convert.ToInt32(field.SelectSingleNode("row").InnerText); //} if (field.SelectSingleNode("columns") != null) { XmlNode columns = field.SelectSingleNode("columns"); foreach (XmlElement column in columns) { row = i; if (!string.IsNullOrWhiteSpace(column.GetAttribute("row"))) { row = Convert.ToInt32(column.GetAttribute("row")); } columnName = column.InnerText; fieldValue += worksheet.Cells[row, dictHeader[columnName]].Value + ""; } } else { if (field.SelectSingleNode("column").Attributes["row"] != null) { row = Convert.ToInt32(field.SelectSingleNode("column").Attributes["row"].Value); } columnName = field.SelectSingleNode("column").InnerText; fieldValue = worksheet.Cells[row, dictHeader[columnName]].Value; } if ("yes".Equals(field.GetAttribute("unique"))) { if (string.IsNullOrWhiteSpace(fieldValue + "")) { whitespace = true; break; } unique += fieldValue; } if ("yes".Equals(field.GetAttribute("notnull"))) { if (string.IsNullOrWhiteSpace(fieldValue + "")) { whitespace = true; break; } } if ("yes".Equals(field.GetAttribute("fk"))) { string referenceTable = field.GetAttribute("referenceTable"); fieldValue = (fieldValue == null) ? null : ((Hashtable)hashTables[referenceTable])[fieldValue]; } fields += "[" + fieldName + "],"; if (fieldValue == null) { values += "NULL,"; } else { values += "N'" + (fieldValue + "").Replace("'", "''") + "',"; } } if (whitespace) { break; } if (hash.Contains(unique)) { continue; } string id = ""; id = Guid.NewGuid() + ""; if (!string.IsNullOrWhiteSpace(unique)) { hash.Add(unique, id); } if (fields.Length > 0) { fields = fields.Substring(0, fields.Length - 1); values = values.Substring(0, values.Length - 1); } sql += "insert into " + tableName + " (Id,"; sql += fields; sql += ") values ('" + id + "',"; sql += values; sql += ")"; listSql.Add(sql); } sqlService.ExecuteTransactionReturnCount(listSql); } loadData = true; } } }
public void ImporttoExcel(string fileName) { FileInfo existingFile = new FileInfo(fileName); Dictionary <string, int> dictHeader = null; using (ExcelPackage package = new ExcelPackage(existingFile)) { SqlService sqlService = SqlServiceFactory.GetSQLService(); XmlDocument dom = new XmlDocument(); Hashtable hashTables = new Hashtable(); Hashtable hash = null; string path = AppDomain.CurrentDomain.BaseDirectory; string[] arr = path.Split('\\'); path = ""; for (int i = 0; i < arr.Length - 2; i++) { path += arr[i] + "\\"; } dom.Load(path + "ImportExcel.xml");//装载XML文档 foreach (XmlElement model in dom.DocumentElement.ChildNodes) { string tableName = model.SelectSingleNode("name").InnerText; List <string> listSql = new List <string>(); hash = new Hashtable(); if (!hashTables.Contains(tableName)) { XmlNode keys = model.SelectSingleNode("tableUniques"); if (keys != null) { hash = GetDataBaseUnique(tableName, keys); } hashTables.Add(tableName, hash); } else { hash = (Hashtable)hashTables[tableName]; } if (string.IsNullOrWhiteSpace(model.GetAttribute("data"))) { //读取数据 string sheetName = model.SelectSingleNode("sheetName").InnerText; ExcelWorksheet worksheet = package.Workbook.Worksheets[sheetName]; int colStart = worksheet.Dimension.Start.Column; //工作区开始列 int colEnd = worksheet.Dimension.End.Column; //工作区结束列 int rowStart = worksheet.Dimension.Start.Row; //工作区开始行号 int rowEnd = worksheet.Dimension.End.Row; //工作区结束行号 dictHeader = new Dictionary <string, int>(); //将每列标题添加到字典中 for (int i = colStart; i <= colEnd; i++) { string headerName = worksheet.Cells[rowStart, i].Value + ""; headerName = headerName.Replace("\n", ""); dictHeader[headerName] = i; } for (int i = rowStart + 1; i <= rowEnd; i++) { string sql = ""; string fields = ""; string values = ""; XmlNode xmlFields = model.SelectSingleNode("fields"); string unique = ""; foreach (XmlElement field in xmlFields.ChildNodes) { string fieldName = field.SelectSingleNode("name").InnerText; string columnName = ""; object fieldValue = null; if (field.SelectSingleNode("columns") != null) { XmlNode columns = field.SelectSingleNode("columns"); foreach (XmlElement column in columns) { columnName = column.InnerText; fieldValue += worksheet.Cells[i, dictHeader[columnName]].Value + ""; } } else { if (field.SelectSingleNode("value") == null) { columnName = field.SelectSingleNode("column").InnerText; fieldValue = worksheet.Cells[i, dictHeader[columnName]].Value; } else { fieldValue = field.SelectSingleNode("value").InnerText; } } if (string.IsNullOrWhiteSpace(fieldValue + "") && !string.IsNullOrWhiteSpace(field.GetAttribute("defaultValue"))) { fieldValue = field.GetAttribute("defaultValue"); } if ("yes".Equals(field.GetAttribute("unique"))) { unique += fieldValue; } if ("yes".Equals(field.GetAttribute("fk"))) { string referenceTable = field.GetAttribute("referenceTable"); fieldValue = ((Hashtable)hashTables[referenceTable])[fieldValue]; } fields += "[" + fieldName + "],"; values += "N'" + (fieldValue + "").Replace("'", "''") + "',"; } if (hash.Contains(unique)) { continue; } string id = ""; id = Guid.NewGuid() + ""; hash.Add(unique, id); if (fields.Length > 0) { fields = fields.Substring(0, fields.Length - 1); values = values.Substring(0, values.Length - 1); } sql += "insert into " + tableName + " (Id,"; sql += fields; sql += ") values ('" + id + "',"; sql += values; sql += ")"; listSql.Add(sql); } } else { XmlNode xmlFields = model.SelectSingleNode("record"); foreach (XmlElement record in xmlFields.ChildNodes) { string fields = ""; string values = ""; string unique = ""; string sql = ""; foreach (XmlElement field in record.ChildNodes) { string fieldValue = field.SelectSingleNode("data").InnerText; if (string.IsNullOrWhiteSpace(fieldValue + "") && !string.IsNullOrWhiteSpace(field.GetAttribute("defaultValue"))) { fieldValue = field.GetAttribute("defaultValue"); } if ("yes".Equals(field.GetAttribute("unique"))) { unique += fieldValue; } fields += "[" + field.SelectSingleNode("name").InnerText + "],"; values += "N'" + (fieldValue + "").Replace("'", "''") + "',"; } if (hash.Contains(unique)) { continue; } string id = ""; id = Guid.NewGuid() + ""; hash.Add(unique, id); if (fields.Length > 0) { fields = fields.Substring(0, fields.Length - 1); values = values.Substring(0, values.Length - 1); } sql += "insert into " + tableName + " (Id,"; sql += fields; sql += ") values ('" + id + "',"; sql += values; sql += ")"; listSql.Add(sql); } } sqlService.ExecuteTransactionReturnCount(listSql); } } }
public void ServiceList() { SqlServiceFactory <IDbContext> factory = new SqlServiceFactory <IDbContext>(); factory.ServiceList(); }