public Client GetClient(int id)
{
string query = string.Format("select * from dsto_client where oid='{0}'", id);
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0)
{
DataRow row = table.Rows[0];
Client client = new Client();
client.Key = row["guid"].ToString();
client.OID = int.Parse(row["OID"].ToString());
client.Name = row["name"].ToString();
client.Contact = row["contact"].ToString();
client.Deleted = bool.Parse(row["deleted"].ToString());
client.Location = row["Location"].ToString();
//Set Logo
if (row["Logo"] != DBNull.Value)
{
client.Logo = row["Logo"].ToString();
}
client.Email = row["Email"].ToString();
client.CreatedBy = row["created_by"].ToString();
client.Users = new Users(client.Parent);
client.Package = new PackageProvider(DbInfo).RetrievePackage(row["yref_package"].ToString());
return(client);
}
return(null);
}
public StudentGrades GetGrade(string studentSsn, string courseName)
{
StudentGrades studentGrades = new StudentGrades();
studentGrades.course = courseName;
DbInfo dbInfo = new DbInfo();
SqlConnection connection = dbInfo.OpenConnection();
using (connection)
{
using (SqlCommand command = new SqlCommand(String.Format("select id from Students where SSN='{0}'", studentSsn), connection))
{
string studentId = command.ExecuteScalar().ToString();
command.CommandText = String.Format("select grade from CoursesStudentsRelation where id_student='{0}' and title_course='{1}'", studentId, courseName);
using (SqlDataReader dataReader = command.ExecuteReader())
{
dataReader.Read();
if (dataReader.HasRows)
{
studentGrades.grade = dataReader.GetDouble(dataReader.GetOrdinal("grade"));
}
else
{
studentGrades.grade = null;
}
}
}
}
return(studentGrades);
}
private bool Insert(Client client)
{
try
{
client.Logo = string.IsNullOrEmpty(client.Logo)? "" : client.Logo;
var cmd = DbInfo.CreateDbCommand();
cmd.CommandText = string.Format("select oid from dsto_client where guid='{0}'", client.Key);
var oid = DbInfo.ExecuteScalar(cmd);
string query = string.Empty;
var exists = oid != null;
if (!exists)
{
query = $"INSERT INTO dsto_client ([guid],created_by,[Name],[Email],[Logo],[Contact],[Location],[yref_package]) values('{client.Key}','Admin','{client.Name}','{client.Email}','{client.Logo}','{client.Contact}','{client.Location}','{client.Package.Key}')";
}
else
{
query = $"UPDATE dsto_client SET Name='{client.Name}', " +
$"Email='{client.Email}', " +
$"Contact='{client.Contact}', " +
$"Location='{client.Location}', " +
$"Logo='{client.Logo}', " +
$"[Deleted]='{client.Deleted}', " +
$"yref_package='{client.Package.Key}' " +
$"WHERE oid='{client.OID}'";
}
var added = DbInfo.ExecuteNonQuery(query) > 0;
if (added)
{
oid = DbInfo.ExecuteScalar(cmd);
client.OID = (int)oid;
if (!exists)
{
Billing billing = new Billing();
billing.Package = client.Package;
billing.PaymentStatus = PaymentStatus.Pending;
billing.Client = client;
billing.Bill = client.Package.Price.ToString();
billing.BillingDate = DateTime.Now;
billing.InvoiceNo = "XYZ";
new BillingProvider(DbInfo).Save(billing);
}
foreach (var user in client.Users)
{
user.ClientId = client.OID.ToString();
new UserProvider(DbInfo).AddOrUpdateUser(user);
}
}
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
public List <Topic> GetTopics(string courseName)
{
List <Topic> result = new List <Topic>();
DbInfo dbInfo = new DbInfo();
SqlConnection connection = dbInfo.OpenConnection();
using (connection)
{
using (SqlCommand command = new SqlCommand(String.Format("select name,course_title from Topics where course_title='{0}'", courseName), connection))
{
using (SqlDataReader dataReader = command.ExecuteReader())
{
if (dataReader.HasRows)
{
int name = dataReader.GetOrdinal("name");
int course = dataReader.GetOrdinal("course_title");
while (dataReader.Read())
{
Topic topic = new Topic();
topic.name = dataReader.GetString(name);
topic.course = dataReader.GetString(course);
result.Add(topic);
}
}
}
}
}
return(result);
}
internal Sections GetSections(string reference, string response_id = null)
{
try
{
QuestionProvider provider = new QuestionProvider(DbInfo);
Sections sections = new Sections();
string query = $"select * from dsto_sections where (yref_questionaire='{reference}' or yref_field_inspection='{reference}' or yref_certification='{reference}' or yref_template='{reference}') and deleted=0 order by oid asc";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
Section section = new Section(null);
section.Key = row["guid"].ToString();
section.OID = int.Parse(row["OID"].ToString());
section.Name = row["Name"].ToString();
section.Description = row["Description"].ToString();
section.Deleted = bool.Parse(row["deleted"].ToString());
section.CreatedBy = row["created_by"].ToString();
section.Questions = provider.GetQuestions(section, response_id);
section.SubSections = new SubSectionProvider(DbInfo).GetSubSections(section, response_id);
sections.Add(section);
}
return(sections);
}
return(null);
}
catch (Exception ex)
{
throw ex;
}
}
public Configuration(string token, string dbName, string pathToPlugins, int remindTimeOut)
{
Token = new ApiToken(token);
Db = new DbInfo(dbName);
PathToPluginsFolder = new PluginsPath(pathToPlugins);
RemindTimeOut = new ReminderTimeOut(remindTimeOut);
}
public async Task <IResponseEntity> UpdateAsync(TaskQzEditRequest req)
{
if (req == null || req?.Id == "")
{
return(ResponseEntity.Error("更新的实体主键丢失"));
}
var entity = _mapper.Map <SysTaskQzEntity>(req);
if (entity.IsDefaultDatabase)
{
DbInfo dbInfo = _authUser.Tenant.DbOptions.Where(x => x.Key == Constants.SystemDbKey).FirstOrDefault();
DbConnectionString connectionString = dbInfo.ConnectionStrings.Where(x => x.UseType == DbUseType.Master).FirstOrDefault();
entity.ConnectionParam = JsonConvert.SerializeObject(new
{
ConnectionString = connectionString.ConnectionString,
DbType = Convert.ToInt32(dbInfo.DbType)
});
}
await _SysTaskQzRepository.UpdateAsync(entity);
if (entity.IsStart)
{
var res = await _schedulerCenter.AddScheduleJobAsync(entity);
}
return(ResponseEntity.Ok("更新成功"));
}
public override bool Save(DCAnalyticsObject obj)
{
try
{
SkipCondition condition = obj as SkipCondition;
var exists = RecordExists("dsto_skipcondition", condition.Key);
string query = string.Empty;
string targetKey = (condition.Target.Section != null) ? condition.Target.Section.Key : (condition.Target.SubSection != null) ? condition.Target.SubSection.Key: condition.Target.Question.Key;
if (!exists)
{
query = $"insert into dsto_skipcondition([guid],[created_by],[yref_attribute],[yref_target],[answer],[yref_question],[dataCollectionObectType]) " +
$"values('{condition.Key}','Admin','{condition.AttributeKey}','{targetKey}','{condition.Answer.Key}','{condition.QuestionKey}', '{(int)condition.DataCollectionObectType}')";
}
else
{
query = $"UPDATE dsto_skipcondition SET" +
$"[yref_attribute] = '{condition.AttributeKey}', " +
$"[yref_target] = '{targetKey}', " +
$"[dataCollectionObectType] = '{(int)condition.DataCollectionObectType}', " +
$"[answer] = '{condition.Answer.Key}', " +
$"[Deleted]='{condition.Deleted}' " +
$"WHERE [guid] = '{condition.Key}'";
}
return(DbInfo.ExecuteNonQuery(query) > -1);
}
catch (Exception ex)
{
throw ex;
}
}
public Trainings GetMobileConfigurationTrainings(int id)
{
Trainings trainings = new Trainings();
try
{
string query = $"select * from dsto_Training where configuration_id = '{id}' and Deleted=0";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0)
{
System.Data.DataRow row = table.Rows[0];
DCAnalytics.Training training = trainings.Add();
InitTraining(training, row);
training.Trainers = new TrainerProvider(DbInfo).GetTrainers(training.Key);
training.Trainees = new TraineeProvider(DbInfo).GetTrainees(training.Key);
training.Topics = new TopicProvider(DbInfo).GetTopics(training.Key);
}
}
catch (Exception ex)
{
throw ex;
}
return(trainings);
}
public static DbBase GetSqlRepository(DbInfo dbInfo)
{
DbBase db = null;
switch (dbInfo.DbType)
{
case DbType.MySql:
db = new MySql(dbInfo);
break;
case DbType.Oracle:
db = new Oracle(dbInfo);
break;
case DbType.SqlServer:
db = new SqlServer(dbInfo);
break;
case DbType.PostgreSQL:
db = new PostgreSql(dbInfo);
break;
default:
throw new Exception($"SqlRepositoryManager.GetSqlRepository未知数据库类型{dbInfo.DbType.ToString()}");
}
return(db);
}
private IFreeSql CreateDb(FreeSql.DataType dbType, DbInfo currentDbOption)
{
var master = currentDbOption.ConnectionStrings?.FirstOrDefault(e => e.UseType == DbUseType.Master);
if (master == null)
{
throw new ArgumentNullException($"请设置租户 {Tenant.Code} 的主库连接字符串");
}
var slaveConnectionStrings = currentDbOption.ConnectionStrings?.Where(e => e.UseType == DbUseType.Slave).Select(e => e.ConnectionString).ToArray();
var freeSqlBuilder = new FreeSql.FreeSqlBuilder()
.UseConnectionString(dbType, master.ConnectionString);
if (slaveConnectionStrings?.Length > 0)
{
freeSqlBuilder = freeSqlBuilder.UseSlave(slaveConnectionStrings);
}
if (_env.IsDevelopment())
{
freeSqlBuilder = freeSqlBuilder.UseAutoSyncStructure(true); //自动同步实体结构【开发环境必备】
}
var fsql = freeSqlBuilder.Build();
fsql.Aop.ConfigEntityProperty += ConfigEntityProperty;
fsql.Aop.CurdBefore += CurdBefore;
fsql.Aop.AuditValue += AuditValue;
//fsql.Aop.SyncStructureAfter += SyncStructureAfter;
DataFilterAsync(fsql);
return(fsql);
}
//-------------------------CRUD--------------------------------
//Add new Database
public static void AddNewDatabase(DatabaseInfo Databaseinfo)
{
//Create Connection
using (MySqlConnection connection = DbInfo.Connection())
{
//Create cmd
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
//Create CommandText
cmd.CommandText = "INSERT INTO `databases`(`ID`, `Datasource`, `Username`, `Password`, `Databasename`) " +
"VALUES (@ID,@Datasource,@Username,@Password,@Databasename)";
//Set Parameters
cmd.Parameters.AddWithValue("@ID", "");
cmd.Parameters.AddWithValue("@Datasource", Databaseinfo.DataSource);
cmd.Parameters.AddWithValue("@Username", Databaseinfo.Username);
cmd.Parameters.AddWithValue("@Password", Databaseinfo.Password);
cmd.Parameters.AddWithValue("@Databasename", Databaseinfo.DatabaseName);
try
{
int recordsAffected = cmd.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
}
}
internal SubSections GetSubSections(Section section, string response_id)
{
try
{
QuestionProvider provider = new QuestionProvider(DbInfo);
string query = $"select * from dsto_subsections where yref_section='{section.Key}' and deleted=0";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0)
{
DataRow row = table.Rows[0];
SubSection subsection = section.SubSections.Add();
subsection.Key = row["guid"].ToString();
subsection.OID = int.Parse(row["OID"].ToString());
subsection.Name = row["Name"].ToString();
subsection.Deleted = bool.Parse(row["deleted"].ToString());
subsection.CreatedBy = row["created_by"].ToString();
subsection.Questions = provider.GetQuestions(subsection, response_id);
}
return(section.SubSections);
}
catch (Exception ex)
{
throw ex;
}
}
//Register a New User
public static void RegisterNow(RegisterInfo registerInfo)
{
using (MySqlConnection connection = DbInfo.Connection())
{
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
cmd.CommandText =
"INSERT INTO `users`(`ID`, `Firstname`, `Middlename`, `Lastname`, `Email`, `Password`, `Pin`) " +
"VALUES (@ID,@Firstname,@Middlename,@Lastname,@Email,@Password,@Pin)";
cmd.Parameters.AddWithValue("@ID", "");
cmd.Parameters.AddWithValue("@Firstname", registerInfo.firstname);
cmd.Parameters.AddWithValue("@Middlename", registerInfo.middlename);
cmd.Parameters.AddWithValue("@Lastname", registerInfo.lastname);
cmd.Parameters.AddWithValue("@Email", registerInfo.email);
cmd.Parameters.AddWithValue("@Password", registerInfo.Password);
cmd.Parameters.AddWithValue("@Pin", int.Parse("0"));
try
{
//connection.Open();
int recordsAffected = cmd.ExecuteNonQuery();
//MessageBox.Show("person has added");
}
finally
{
connection.Close();
}
}
}
}
public Reports GetReports(string response_id)
{
try
{
Reports reports = new Reports();
string query = $"select dsto_questionaire.guid as questionaire_guid, dsto_questionaire.oid as questionaire_oid, dsto_purchase.* from dsto_questionaire inner join dsto_purchase on dsto_questionaire.guid = dsto_purchase.farmerid where dsto_questionaire.yref_template='{response_id}'";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
Report report = reports.Add();
report.Questionaire = new Questionaire(null);
report.Questionaire.Key = row["questionaire_guid"].ToString();
report.Questionaire.OID = int.Parse(row["questionaire_oid"].ToString());
report.Questionaire.Name = new SectionProvider(DbInfo).QuestionaireIdentification(response_id, report.Questionaire.Key);
report.Purchase = new Purchase();
InitPurchases(report.Purchase, row);
}
}
return(reports);
}
catch (Exception ex)
{
throw ex;
}
}
//public bool Delete(string reference)
//{
// string query = $"delete from dsto_sections where yref_questionaire = '{reference}' or yref_field_inspection = '{reference}' or yref_certification = '{reference}'";
// var rows = DbInfo.ExecuteNonQuery(query);
// return rows > 0;
//}
public Certifications GetCertifications(int configuration_Id)
{
Certifications certifications = new Certifications(null);
try
{
string query = $"select * from dsto_Certification where status=0 and configuration_id = '{configuration_Id}' and Deleted=0";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
var certificationType = (CertificationTypes)Enum.Parse(typeof(CertificationTypes), row["CertificationType"].ToString());
DCAnalytics.Certification certification = certifications.Add(certificationType);
InitCertification(certification, row);
certification.Sections = new SectionProvider(DbInfo).GetSections(certification.Key);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(certifications);
}
public override bool Save(DCAnalyticsObject obj)
{
Purchase purchase = obj as Purchase;
string query = string.Empty;
var exists = RecordExists("dsto_Purchase", purchase.Key);
if (!exists)
{
query = $"insert into dsto_Purchase([guid],[created_by],[price],[dateofpurchase],[quantity],[farmerid],[lotid],[configuration_id],[product],[station]) values('{purchase.Key}','{purchase.CreatedBy}','{purchase.Price}','{purchase.DateOfPurchase.ToString("yyyy-MM-dd HH:mm:ss.fff")}','{purchase.Quantity}','{purchase.Farmer}','{purchase.Lotid}','{purchase.ConfigurationId}','{purchase.Product}','{purchase.Station}')";
}
else
{
//update
query = $"UPDATE dsto_Purchase SET [price]={purchase.Price}, " +
$"[dateofpurchase] = '{purchase.DateOfPurchase.ToString("yyyy-MM-dd HH:mm:ss.fff")}', " +
$"[quantity] = '{purchase.Quantity}', " +
$"[created_by] = '{purchase.CreatedBy}', " +
$"[lotid] = '{purchase.Lotid}', " +
$"[farmerid] = '{purchase.Farmer}', " +
$"[product]='{purchase.Product}', " +
$"[station]='{purchase.Station}', " +
$"[Deleted]='{purchase.Deleted}' " +
$"WHERE [guid] = '{purchase.Key}'";
}
if (DbInfo.ExecuteNonQuery(query) > -1)
{
return(true);
}
return(false);
}
public bool DeletePurchase(int id)
{
string query = $"delete from dsto_Purchase where [oid]={id}";
var rows = DbInfo.ExecuteNonQuery(query);
return(rows > -1);
}
//Create Database Connection | Using DB name
public static MySqlConnection CreateDBConnection(string DBName)
{
string constring = "";
//create connection and open it
MySqlConnection connection = DbInfo.Connection();
MySqlCommand id_cmd = connection.CreateCommand();
id_cmd.CommandText =
"SELECT `ID`, `Datasource`, `Username`, `Password`, `Databasename` FROM `databases` WHERE Databasename = @Databasename";
id_cmd.Parameters.AddWithValue("@Databasename", DBName);
MySqlDataReader reader = id_cmd.ExecuteReader();
//if match is found
if (reader.Read())
{
constring = "datasource = " + reader["Datasource"] + "; username = "******"Username"] + "; password="******"Password"] + "; database = " + reader["Databasename"].ToString();
//Create mysqlconnection
MySqlConnection newcon = new MySqlConnection(constring);
newcon.Open();
return(newcon);
}
else
{
//Return connection
MySqlConnection nl = null;
return(nl);
}
}
//Get all Databases | Using DB name
public static List <string> GetAllDatabases()
{
//Temp list
List <string> temp = new List <string>();
//create connection and open it
MySqlConnection connection = DbInfo.Connection();
//Create command
MySqlCommand id_cmd = connection.CreateCommand();
id_cmd.CommandText =
"SELECT `ID`, `Datasource`, `Username`, `Password`, `Databasename` FROM `databases`";
//Create reader
MySqlDataReader reader = id_cmd.ExecuteReader();
//While reading
while (reader.Read())
{
temp.Add(reader["Databasename"].ToString());
}
return(temp);
}
//Delete new Database
public static void DeleteDatabase(int ID)
{
//Create Connection
using (MySqlConnection connection = DbInfo.Connection())
{
//Create Cmd
using (MySqlCommand cmd = new MySqlCommand())
{
cmd.Connection = connection;
cmd.CommandType = CommandType.Text;
//Set CommandText
cmd.CommandText = "DELETE FROM `databases` WHERE `ID` = @ID";
//Add Parameters
cmd.Parameters.AddWithValue("@ID", ID);
try
{
int recordsAffected = cmd.ExecuteNonQuery();
}
finally
{
connection.Close();
}
}
}
}
public void GetDbInfoTest()
{
var expected = new DbInfo();
expected.Name = db1;
expected.Version = "1.0.0";
expected.CRS = CRS.WGS84;
var url = String.Join("/", new string[] { urlPrefix, db1 });
var handler = new MockHttpHandler(url, "GET", (req, res, param) =>
{
var result = new RestResult();
result.Success = true;
result.Data = JsonConvert.SerializeObject(expected);
return(JsonConvert.SerializeObject(result));
});
mockServer.AddRequestHandler(handler);
try
{
var db = new MapDB(db1);
var actual = db.GetDbInfo();
Assert.AreEqual <DbInfo>(expected, actual);
}
catch
{
Assert.Fail();
}
}
public List <Student> GetStudents()
{
List <Student> result = new List <Student>();
DbInfo dbInfo = new DbInfo();
SqlConnection connection = dbInfo.OpenConnection();
using (connection)
{
using (SqlCommand command = new SqlCommand("select name,surname,SSN from Students", connection))
{
using (SqlDataReader dataReader = command.ExecuteReader())
{
if (dataReader.HasRows)
{
int name = dataReader.GetOrdinal("name");
int surname = dataReader.GetOrdinal("surname");
int SSN = dataReader.GetOrdinal("SSN");
while (dataReader.Read())
{
Student student = new Student();
student.name = dataReader.GetString(name);
student.surname = dataReader.GetString(surname);
student.SSN = dataReader.GetString(SSN);
result.Add(student);
}
}
}
}
}
return(result);
}
public bool DeleteDependency(string key)
{
string query = $"delete from dsto_dependency where [guid]='{key}'";
var rows = DbInfo.ExecuteNonQuery(query);
return(rows > -1);
}
internal static void SetUserInfo(int ID)
{
//create connection and open it
MySqlConnection connection = DbInfo.Connection();
//try to connect to database
try
{
//Build Mysql command
MySqlCommand cmd = connection.CreateCommand();
cmd.CommandText =
"SELECT `ID`, `Firstname`, `Middlename`, `Lastname`, `Email`, `Password`, `Pin` FROM `users` WHERE `ID`=" + ID;
MySqlDataReader reader = cmd.ExecuteReader();
//if match is found
if (reader.Read())
{
//MessageBox.Show(reader["Firstname"] + " "+ reader["Middlename"].ToString());
Userinformation = new userinf(ID, reader["Firstname"].ToString(), reader["Middlename"].ToString(), reader["Lastname"].ToString(), reader["Email"].ToString());
}
}
//finally
finally
{
//check state and clone
if (connection.State == ConnectionState.Open)
{
connection.Clone();
}
}
}
public override bool Save(DCAnalyticsObject obj)
{
QuestionProvider provider = new QuestionProvider(DbInfo);
SubSection subsection = obj as SubSection;
var exists = RecordExists("dsto_subsections", subsection.Key);
string query = string.Empty;
if (!exists)
{
query = $"insert into dsto_subsections([guid],Name,[created_by],[yref_section]) values('{subsection.Key}','{subsection.Name}','Admin','{subsection.SectionKey}')";
}
else
{
//update
query = $"UPDATE dsto_subsections SET [Name]='{subsection.Name}', " +
$"[Deleted]='{subsection.Deleted}' " +
$"WHERE [guid] = '{subsection.Key}'";
}
if (DbInfo.ExecuteNonQuery(query) > -1)
{
//save questions
foreach (var qn in subsection.Questions)
{
qn.SubSectionKey = subsection.Key;
provider.Save(qn);
}
return(true);
}
return(false);
}
public CosmosDbService(DbInfo dbInfo)
{
_databaseId = dbInfo.DatabaseId;
_disableConcurrencyCheck = dbInfo.DisableConcurrencyCheck;
_client = new DocumentClient(new Uri(dbInfo.ServiceEndpoint), dbInfo.SasKey);
}
public EnumList GetEnumList(int id, EnumListTypes enumListType)
{
try
{
EnumList enumList = new EnumList(null);
string query = string.Empty;
if (enumListType == EnumListTypes.Question)
{
query = $"select * from dsto_EnumLists where questionId='{id}' and type='{(int)enumListType}'";
}
else
{
query = $"select * from dsto_EnumLists where ConfigurationId='{id}' and type='{(int)enumListType}'";
}
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0)
{
InitEnumList(enumList, table.Rows[0]);
}
return(enumList);
}
catch (Exception ex)
{
throw ex;
}
}
public bool DeleteByReference(string reference)
{
string query = $"delete from dsto_question where yref_questionaire = '{reference}' or yref_section = '{reference}' or yref_subsection = '{reference}'";
var rows = DbInfo.ExecuteNonQuery(query);
return(rows > 0);
}
public Reports GetReports(string configuration_id)
{
try
{
Reports reports = new Reports();
string query = $"select * from dsto_training where configuration_id='{configuration_id}'";
var table = DbInfo.ExecuteSelectQuery(query);
if (table.Rows.Count > 0)
{
foreach (DataRow row in table.Rows)
{
Report report = reports.Add();
report.Training = new Trainings().Add();
InitTraining(report.Training, row);
report.Training.Trainers = new TrainerProvider(DbInfo).GetTrainers(report.Training.Key);
report.Training.Trainees = new TraineeProvider(DbInfo).GetRegisteredTrainees(report.Training.Key);
report.Training.Topics = new TopicProvider(DbInfo).GetTopics(report.Training.Key);
}
}
return(reports);
}
catch (Exception ex)
{
throw ex;
}
}
public static void Main(string[] args)
{
var excludesObjTyps = new string[] { "INTERNAL_TABLE", "SYSTEM_TABLE" };
var excludeDBs = new string[] { "master", "tempdb", "model", "msdb" };
DbInfo dc;
IEnumerable<string> dbs;
string server = ".";
string connStr = String.Format("Data Source={0};Integrated Security=True", server);
using (dc = new DbInfo(connStr))
{
dbs = dc.databases.Where(db => !excludeDBs.Contains(db.name)).Select(db => db.name).ToArray();
}
var allDbObjTypes = dbs.SelectMany(dataBase =>
{
using (dc = new DbInfo(string.Format("Data Source=.;Initial Catalog={0};Integrated Security=True", dataBase)))
{
var objsForDB = from dbobjs in dc.all_objects
join schma in dc.schemas on dbobjs.schema_id equals schma.schema_id
where !excludesObjTyps.Contains(dbobjs.type_desc)
&& !dbobjs.type_desc.Contains("CONSTRAINT")
&& !dbobjs.is_ms_shipped
select new { Name = String.Format("{0}.{1}.{2}", dataBase, schma.name, dbobjs.name), Type = dbobjs.type_desc };
return objsForDB.ToArray();
}
}).ToDictionary(item => item.Name, item => item.Type);
var allDependencies = dbs.SelectMany(dataBase =>
{
string connStr2 = String.Format("Data Source={0};Initial Catalog={1};Integrated Security=True", server, dataBase);
using (dc = new DbInfo(connStr2))
{
var objsForDB = from dbobjs in dc.all_objects
join schma in dc.schemas on dbobjs.schema_id equals schma.schema_id
where !excludesObjTyps.Contains(dbobjs.type_desc)
&& !dbobjs.type_desc.Contains("CONSTRAINT")
&& !dbobjs.is_ms_shipped
select new
{
NameForLookup = String.Format("{1}.{2}", dataBase, schma.name, dbobjs.name),
NameForGraph = String.Format("{0}.{1}.{2}", dataBase, schma.name, dbobjs.name)
};
var deps = objsForDB.ToArray().Select(item => StatementExpressions.TryCatch(() => dc.dm_sql_referenced_entities(item.NameForLookup, "OBJECT")
.Where(dep => dep.referenced_class == 1 && dep.referenced_minor_id == 0)
.Select(dep => new Tuple<string, string, string>(item.NameForGraph,
string.Format("{0}.{1}.{2}", dep.referenced_database_name ?? dataBase, dep.referenced_schema_name ?? "dbo", dep.referenced_entity_name),
dep.referenced_class_desc)).ToArray(),
(SqlException x) => Debug.Write(x),
() => null));
var hot = deps.ToArray();
return hot.Where(item => item != null).SelectMany(l => l);
}
}).ToArray();
DirectedGraph graphToDirectedGraphML = BuildGraph(allDbObjTypes, allDependencies);
graphToDirectedGraphML.WriteXml("SqlDeps.dgml");
Console.WriteLine("Open file 'SqlDeps.dgml'");
}
/*
** Name: loadDbCaps
**
** Description:
** Requests DBMS capabilities from the server and
** initialize dbmsinfo() access.
**
** Input:
** None.
**
** Output:
** None.
**
** Returns:
** void
**
** History:
** 12-Nov-99 (gordy)
** If OPEN_SQL_DATES, use local time rather than gmt.
** 3-Nov-00 (gordy)
** Timezone stuff removed from DbConn. Replaced with a public
** boolean indicating which timezone to use for the connection.
** 7-Mar-01 (gordy)
** Use new class to read DBMS capabilities.
** 10-May-01 (gordy)
** Check DBMS capabilities relevant to driver configuration.
** 20-Feb-02 (gordy)
** Added dbmsInfo checks for DBMS type and protocol level.
** 31-Oct-02 (gordy)
** Adapted for generic GCF driver.
** 23-Dec-02 (gordy)
** Connection level ID changed at protocol level 3.
*/
public void loadDbCaps()
{
String value;
int ivalue;
/*
** Initialize helper classes and load DB capabilities.
*/
dbInfo = new DbInfo( this );
dbCaps = new DbCaps( this );
dbCaps.loadDbCaps();
/*
** Check DB capabilties.
*/
;
if ( (value = dbCaps.getDbCap( (msg_protocol_level < MSG_PROTO_3)
? DrvConst.DRV_DBCAP_CONNECT_LVL0
: DrvConst.DRV_DBCAP_CONNECT_LVL1 )) != null )
try { db_protocol_level = Byte.Parse( value ); }
catch( Exception ) {}
if ( (value = dbCaps.getDbCap( DBMS_DBCAP_DBMS_TYPE )) != null )
is_ingres = ToInvariantUpper(value).Equals( DBMS_TYPE_INGRES );
if ((value = dbCaps.getDbCap(DBMS_DBCAP_ING_SQL_LVL)) != null)
try { sqlLevel = Int32.Parse(value); }
catch (Exception) { }
// How the native timestamp type reacts to timezones,
// lack of it indicates an Ingres DBMS
if ((value = dbCaps.getDbCap(DBMS_DBCAP_OSQL_DATES)) != null)
osql_dates = true; // TODO: check for 'LEVEL 1'
if ( (value = dbCaps.getDbCap( DBMS_DBCAP_UCS2_TYPES )) != null &&
value.Length >= 1 )
ucs2_supported = (value[0] == 'Y' || value[0] == 'y');
if ((value = dbCaps.getDbCap(DBMS_DBCAP_MAX_CHR_COL)) != null)
try { max_char_len = Int32.Parse(value); }
catch (Exception) { }
if ((value = dbCaps.getDbCap(DBMS_DBCAP_MAX_VCH_COL)) != null)
try { max_vchr_len = Int32.Parse(value); }
catch (Exception) { }
if ((value = dbCaps.getDbCap(DBMS_DBCAP_MAX_BYT_COL)) != null)
try { max_byte_len = Int32.Parse(value); }
catch (Exception) { }
if ((value = dbCaps.getDbCap(DBMS_DBCAP_MAX_VBY_COL)) != null)
try { max_vbyt_len = Int32.Parse(value); }
catch (Exception) { }
/*
** The NCS max column lengths default to half (two bytes per char)
** the max for regular columns to match prior behaviour.
*/
if ((value = dbCaps.getDbCap(DBMS_DBCAP_MAX_NCHR_COL)) == null)
max_nchr_len = max_char_len / 2;
else
try { max_nchr_len = Int32.Parse(value); }
catch (Exception) { }
if ((value = dbCaps.getDbCap(DBMS_DBCAP_MAX_NVCH_COL)) == null)
max_nvch_len = max_vchr_len / 2;
else
try { max_nvch_len = Int32.Parse(value); }
catch (Exception) { }
if ((value = dbCaps.getDbCap(DBMS_DBCAP_MAX_DEC_PREC)) != null)
if (Int32.TryParse(value, out ivalue)) // convert value to int ivalue
max_dec_prec = ivalue;
return;
}
public Copier(DbInfo source, DbInfo dest, bool overwriteExistingDatabase)
{
_source = source;
_dest = dest;
_overwriteExistingDatabase = overwriteExistingDatabase;
}
