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();
}
