public void NWindPipeline()
{
var tm = TypeModel.Create(false, ProtoCompatibilitySettingsValue.FullCompatibility);
DAL.Database masterDb = DAL.NWindTests.LoadDatabaseFromFile <DAL.Database>(tm);
int orderCount = masterDb.Orders.Count,
lineCount = masterDb.Orders.Sum(o => o.Lines.Count),
unitCount = masterDb.Orders.SelectMany(o => o.Lines).Sum(l => (int)l.Quantity);
decimal freight = masterDb.Orders.Sum(order => order.Freight).GetValueOrDefault(),
value = masterDb.Orders.SelectMany(o => o.Lines).Sum(l => l.Quantity * l.UnitPrice);
Assert.AreEqual(830, orderCount);
Assert.AreEqual(2155, lineCount);
Assert.AreEqual(51317, unitCount);
Assert.AreEqual(1354458.59M, value);
DatabaseReader db = DAL.NWindTests.LoadDatabaseFromFile <DatabaseReader>(tm);
Assert.AreEqual(orderCount, db.OrderReader.OrderCount);
Assert.AreEqual(lineCount, db.OrderReader.LineCount);
Assert.AreEqual(unitCount, db.OrderReader.UnitCount);
Assert.AreEqual(freight, db.OrderReader.FreightTotal);
Assert.AreEqual(value, db.OrderReader.ValueTotal);
}
public DataTable GetStuList()
{
string strSql = "select * from Stu";
DAL.Database DAL = new DAL.Database();
return(DAL.Query(strSql.ToString()));
}
public string Insert(string stuNo, string stuName)
{
StringBuilder strSql = new StringBuilder();
strSql.AppendFormat("select * from Stu where StuNo='{0}',stuNo");
DAL.Database dal = new DAL.Database();
DataTable dt = dal.Query(strSql.ToString());
if (dt.Rows.Count > 0)
{
return("学生编号已占用");
}
strSql.Clear();
strSql.AppendFormat("select IsNull(max(Id),0) from Stu");
DataTable dt_Id = dal.Query(strSql.ToString());
int Id = 1;
if (dt_Id.Rows.Count > 0)
{
Id = int.Parse(dt_Id.Rows[0][0].ToString()) + 1;
}
strSql.Clear();
strSql.AppendFormat("insert into Stu(Id,stuNo,stuName) values('{0}'，'{1}','{2}')", Id, stuNo, stuName);
return(dal.ExeSQL(strSql.ToString()));
}
private void btnSave_Click(object sender, EventArgs e)
{
if (txtPassword.Text == string.Empty)
{
MessageBox.Show("Password Missing???");
txtPassword.Focus();
return;
}
if (txtPassword.Text.ToUpper() != "PANTHERSOFT")
{
MessageBox.Show("Incorrect Password!!!");
txtPassword.Focus();
return;
}
if (txtScript.Text == string.Empty)
{
MessageBox.Show("No Script found!!!");
txtScript.Focus();
return;
}
try
{
string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["MyString"].ConnectionString;
DAL.Database obj = new DAL.Database(connectionstring);
string script = txtScript.Text;
// split script on GO command
IEnumerable <string> commandStrings = Regex.Split(script, @"^\s*GO\s*$",
RegexOptions.Multiline | RegexOptions.IgnoreCase);
foreach (string commandString in commandStrings)
{
if (commandString.Trim() != "")
{
obj.ExecuteNonQueryOnly(commandString);
}
}
MessageBox.Show("Script Run Successfull.");
txtScript.Text = string.Empty;
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}
}
private void buttonLogin_Click(object sender, EventArgs e)
{
if (UtilidadesFormularios.Validar(new List <TextBox> {
textBoxPassword, textBoxUserName
}))
{
string user = textBoxUserName.Text;
string password = textBoxPassword.Text;
using (var db = new DAL.Database())
{
if (db.Usuarios.Where(x => x.UserName == user && x.Password == password).FirstOrDefault() != null)
{
this.Hide();
new Inicio().Show();
}
else
{
error.SetError(textBoxUserName, "Usuario o password o incorrecto!");
error.SetError(textBoxPassword, "Usuario o password o incorrecto!");
}
}
}
}
private void btnSave_Click(object sender, EventArgs e)
{
if (txtPassword.Text == string.Empty)
{
MessageBox.Show("Password Missing???");
txtPassword.Focus();
return;
}
if (txtPassword.Text.ToUpper() != "PANTHERSOFTGO")
{
MessageBox.Show("Incorrect Password!!!");
txtPassword.Focus();
return;
}
if (txtToDatabase.Text == string.Empty)
{
MessageBox.Show("Data Base Name Missing???");
txtPassword.Focus();
return;
}
try
{
string connectionstring = System.Configuration.ConfigurationManager.ConnectionStrings["MyString"].ConnectionString;
DAL.Reports objDAL = new DAL.Reports();
objDAL.connectionstring = connectionstring;
DataTable dt = objDAL.getTrialBalance(DateTime.Now.Date.ToShortDateString());
if (dt.Rows.Count == 0)
{
MessageBox.Show("No Data To Display", "No Data");
return;
}
DAL.Misc dal = new DAL.Misc();
dal.connectionstring = objDAL.connectionstring;
decimal vNetSaleVal = dal.getNetSalesValue("01/01/2000", DateTime.Now.Date.ToShortDateString());
decimal vNetPurVal = dal.getNetPurchaseValue("01/01/2000", DateTime.Now.Date.ToShortDateString());
decimal vTTLExp = 0, vTTLRev = 0;
foreach (DataRow dr in dt.Rows)
{
if (dr["AccountType"].ToString() == "EXPENSE")
{
vTTLExp += decimal.Parse(dr["Debit"].ToString());
}
if (dr["AccountType"].ToString() == "REVENUE")
{
vTTLRev += decimal.Parse(dr["Credit"].ToString());
}
}
decimal vOpStockVal = dal.getOpeningStockValue(string.Empty);
DataTable dTemp = objDAL.getCurrentStock(string.Empty);
decimal vCSValue = 0;
foreach (DataRow dr in dTemp.Rows)
{
decimal vCurrentStockVal = (decimal)dr["Value"];
decimal vNetQty = (decimal)dr["Qty"];
decimal vDeducted = (decimal)dr["LessQty"];
decimal vCSRate = vCurrentStockVal / (vNetQty + vDeducted);
vCSValue += (vNetQty * vCSRate);
}
decimal vAdjustment = 0;
decimal.TryParse(txtBalAdjust.Text, out vAdjustment);
decimal vPLS = (vNetSaleVal - (vNetPurVal + vOpStockVal - vCSValue)) - vTTLExp + vTTLRev + vAdjustment;
connectionstring = string.Format("Data Source=(local);Initial Catalog={0};Trusted_Connection=YES;", txtToDatabase.Text);
DAL.Database obj = new DAL.Database(connectionstring);
obj.ExecuteNonQueryOnly("update AccountChart set OpeningCredit = 0 , OpeningDebit=0");
foreach (DataRow drX in dt.Rows)
{
if (drX["AccountType"].ToString() == "ASSET" || drX["AccountType"].ToString() == "LIABILITY")
{
string vQuery = string.Format(" Update AccountChart Set OpeningDebit={0}, OpeningCredit={1} Where AccountNo='{2}'"
, drX["Debit"].ToString(), drX["Credit"].ToString(), drX["AccountNo"].ToString());
obj.ExecuteNonQueryOnly(vQuery);
}
else if (drX["AccountType"].ToString() == "CAPITAL")
{
string vQuery = string.Format(" Update AccountChart Set OpeningDebit={0}, OpeningCredit={1} Where AccountNo='{2}'"
, drX["Debit"].ToString(), (decimal.Parse(drX["Credit"].ToString()) + vPLS).ToString(), drX["AccountNo"].ToString());
obj.ExecuteNonQueryOnly(vQuery);
}
}
DataTable dt2 = objDAL.getCurrentStock(string.Empty);
foreach (DataRow drX in dt2.Rows)
{
string vQuery = string.Format(" Update Products Set OpeningStock={0}, OpeningStockValue={1} Where ProductID='{2}'"
, drX["Qty"].ToString(),
(decimal.Parse(drX["Qty"].ToString()) * (decimal.Parse(drX["Value"].ToString()) / (decimal.Parse(drX["Qty"].ToString()) + decimal.Parse(drX["LessQty"].ToString())))).ToString("G29"),
drX["ProductID"].ToString());
obj.ExecuteNonQueryOnly(vQuery);
}
MessageBox.Show("Balance Transfer Successfull.");
txtToDatabase.Text = string.Empty;
}
catch (Exception exc)
{
MessageBox.Show(exc.Message);
}
}
private void DisplayLastSelectedObject()
{
LastSelectedDatabase = Global.Snapshot.Servers.SelectMany(c => c.Databases).SingleOrDefault(c => c.DatabaseServerId == LastSelectedObject.DatabaseServerId);
LastSelectedServer = Global.Snapshot.Servers.SingleOrDefault(c => c.Id == LastSelectedDatabase.ServerId);
var relatedObjects = GetRelatedObjects(LastSelectedObject.Object, LastSelectedDatabase);
int newTabIndex = 0;
if (LastSelectedObject.Object is DAL.Table)
{
LastSelectedTable = LastSelectedObject.Object as DAL.Table;
var code = new StringBuilder();
code.AppendFormat("use {0}" + System.Environment.NewLine, LastSelectedObject.Database);
code.AppendLine("go");
code.AppendFormat("create table [{0}].[{1}] (" + System.Environment.NewLine, LastSelectedTable.Schema, LastSelectedTable.Name);
code.Append(string.Join("," + System.Environment.NewLine, LastSelectedTable.Columns.Select(c => c.ToSQL())));
code.AppendLine(System.Environment.NewLine + ")" + System.Environment.NewLine + "go");
txtTable1.Text = code.ToString();
btnSelect_Click(null, null);
tableRelatedObjects.Clear();
foreach (var item in relatedObjects.ObjectsThatThisDependsOn.Select(c => new DatabaseObjectWithParents(c))) tableRelatedObjects.Add(item);
foreach (var item in relatedObjects.ObjectsDependingOnThis.Select(c => new DatabaseObjectWithParents(c))) tableRelatedObjects.Add(item);
newTabIndex = 0;
}
if (LastSelectedObject.Object is DAL.StoredProcedure)
{
LastSelectedStoredProcedure = LastSelectedObject.Object as DAL.StoredProcedure;
txtStoredProcedure1.Text = new SqlServerCore().GenerateCodeForStoredProcedure(LastSelectedStoredProcedure, LastSelectedObject.Database);
storedProceduresRelatedObjects.Clear();
foreach (var item in relatedObjects.ObjectsThatThisDependsOn.Select(c => new DatabaseObjectWithParents(c))) storedProceduresRelatedObjects.Add(item);
foreach (var item in relatedObjects.ObjectsDependingOnThis.Select(c => new DatabaseObjectWithParents(c))) storedProceduresRelatedObjects.Add(item);
txtStoredProcedure2.Text = new SqlServerCore().GenerateCSharpCodeForSP(LastSelectedStoredProcedure);
newTabIndex = 1;
}
if (LastSelectedObject.Object is DAL.View)
{
LastSelectedView = LastSelectedObject.Object as DAL.View;
txtView1.Text = LastSelectedView.Text;
viewRelatedObjects.Clear();
foreach (var item in relatedObjects.ObjectsThatThisDependsOn.Select(c => new DatabaseObjectWithParents(c))) viewRelatedObjects.Add(item);
foreach (var item in relatedObjects.ObjectsDependingOnThis.Select(c => new DatabaseObjectWithParents(c))) viewRelatedObjects.Add(item);
txtView2.Text = new SqlServerCore().GenerateSP_Select(LastSelectedView, LastSelectedObject.Database);
newTabIndex = 2;
}
if (databaseTabs.SelectedIndex != newTabIndex)
{
// has to changed this way, as a double click inside a tab prevents the tab from being changed by just calling .SelectedIndex normally
Application.Current.Dispatcher.BeginInvoke((Action)delegate
{
databaseTabs.SelectedIndex = newTabIndex;
}, DispatcherPriority.Render, null);
}
}
