public OleDbDataReader selectquery(string query)
{
try
{
cmd = new OleDbCommand(query, con);
return cmd.ExecuteReader();
}
catch (Exception ex)
{
Out.Print.Line(ex.Message);
}
return null;
}
public int runinsertquery(string query)
{
try
{
cmd = new OleDbCommand(query, con);
return cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
Out.Print.Line(ex.Message);
}
return -1;
}
public static DataSet ImportExcel(this DataSet dataSet, String filename, bool hasHeaders = true)
{
if (filename.IsNotNullOrWhiteSpace()) return default(DataSet);
var connectionString = String.Empty;
var HDR = hasHeaders ? "Yes" : "No";
var extension = Path.GetExtension(filename);
if (extension.IsNotNullOrWhiteSpace())
switch (extension.Substring(1))
{
case "xls": //Excel 97-03
connectionString = String.Format(
@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1'",
filename, HDR);
break;
case "xlsx": //Excel 07
connectionString = String.Format(
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1'",
filename, HDR);
break;
}
dataSet = new DataSet(Path.GetFileNameWithoutExtension(filename));
using (var conOleDb = new OleDbConnection(connectionString))
{
conOleDb.Open();
//{TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE}
var arrRestrict = new Object[] {null, null, null, "TABLE"};
var dtSchema = conOleDb.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, arrRestrict);
if (null != dtSchema)
{
foreach (DataRow drSchema in dtSchema.Rows)
{
var nameSheet =
//drSchema[dtSchema.Columns[2].ColumnName].ToString();
drSchema["TABLE_NAME"].ToString();
var selectQuery = String.Format("SELECT * FROM [{0}]", nameSheet);
var table = new DataTable(nameSheet);
var cmdOleDb = new OleDbCommand(selectQuery, conOleDb) { CommandType = CommandType.Text };
new OleDbDataAdapter(cmdOleDb).Fill(table);
dataSet.Tables.Add(table);
}
}
conOleDb.Close();
}
RemoveNull(dataSet);
return dataSet;
}
public static bool CheckExcelSheetByWord(string FilePath, string[] sheetNameWord)
{
bool find = false;
//First Tab Connection
OleDbConnection conn = new OleDbConnection();
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter da = new OleDbDataAdapter();
DataSet ds = new DataSet();
//Response.Write(FilePath);
string Extension = Path.GetExtension(FilePath);
string header = "Yes";
string connString = setConnectionStringForExcel(FilePath, Extension, header);
//Response.Write(connString);
//Response.Write(query);
//Create the connection object
conn = new OleDbConnection(connString);
cmd.Connection = conn;
//Open connection
if (conn.State == ConnectionState.Closed) conn.Open();
DataTable Sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//Console.Write(sheetname);
int same = 0;
string getsheet = string.Empty;
foreach (DataRow dr in Sheets.Rows)
{
string sheetname = dr["TABLE_NAME"].ToString().Replace("'", "");
if (sheetname.Contains("$"))
{
sheetname = sheetname.Replace("$", "");
}
if (!sheetname.Contains("_xlnm"))
{
same = 0;
foreach (string sheetName in sheetNameWord)
{
if (sheetname.Contains(sheetName) == true)
{
same = 1;
getsheet = sheetname;
break;
}
}
}
}
if (getsheet != null)
{
find = true;
}
else
{
find = false;
}
//Close OleDbConnection Connection
//conn.Dispose();
//conn.Close();
//Close connection
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
return find;
}
//-----------------------------------------------------------------
public static DataSet ReadExcelToGetSheetname(string FilePath, string querypart,string wherequery, int indexNo)
{
String query = string.Empty;
//First Tab Connection
OleDbConnection conn = new OleDbConnection();
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter da = new OleDbDataAdapter();
DataSet ds = new DataSet();
System.Data.DataTable dt = new System.Data.DataTable();
//Response.Write(FilePath);
string Extension = Path.GetExtension(FilePath);
string header = "Yes";
string connString = setConnectionStringForExcel(FilePath, Extension, header);
//Response.Write(connString);
//Response.Write(query);
//Create the connection object
conn = new OleDbConnection(connString);
//Open connection
if (conn.State == ConnectionState.Closed) conn.Open();
DataTable Sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetname = string.Empty;
sheetname = Sheets.Rows[indexNo]["TABLE_NAME"].ToString().Replace("'", "");
//Console.Write(sheetname);
//query = String.Format("SELECT * FROM [{0}" + querypart + "]", sheetname);
query = String.Format("SELECT * FROM [{0}{1}]", sheetname, querypart);
query = query + wherequery;
/*
for (int i = 0; i < Sheets.Rows.Count; i++)
{
string worksheets = Sheets.Rows[i]["TABLE_NAME"].ToString();
query = String.Format("SELECT * FROM [{0}]", worksheets);
} */
/*
foreach (DataRow dr in Sheets.Rows)
{
sheetname = dr[indexNo].ToString().Replace("'", "");
//sheetname = dr["TABLE_NAME"].ToString();
query = String.Format("SELECT * FROM [{0}]", sheetname);
} */
//if (sheetname != string.Empty)
//{
// query = "select * from [" + sheetname + "]" + querypart;
//}
//Create the command object
cmd = new OleDbCommand(query, conn);
da = new OleDbDataAdapter(cmd);
ds = new DataSet();
da.Fill(ds);
dt = ds.Tables[0];
//Close OleDbConnection Connection
da.Dispose();
//conn.Dispose();
//conn.Close();
//Close connection
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
RemoveAllNullRowsFromDataTable(dt);
return ds;
}
private void buttonApply_Click(object sender, EventArgs e)
{
if (this.textBoxID.Text == "") // if ID textbox is empty
{
MessageBox.Show("Item ID empty!");
}
else
{
//MessageBox.Show("Begin operate database");
//int int.Parse(this.textBoxID.Text) = int.Parse(this.textBoxID.Text);
OleDbConnection conn = getConn(); //get connection object
conn.Open();
//do actions depends on status
switch (status)
{
case Add:
try
{
string strCheck = "SELECT * FROM item WHERE iID =" + int.Parse(this.textBoxID.Text);
OleDbCommand cmdCheck = new OleDbCommand(strCheck, conn);
OleDbDataReader reader;
reader = cmdCheck.ExecuteReader(); //execute query and get according DataReader
if (reader.Read())
{
MessageBox.Show("This item ID already exists!");
reader.Close();
}
else
{
reader.Close();
string strInsertItem = "INSERT INTO item VALUES (" + int.Parse(this.textBoxID.Text) + "," + int.Parse(this.textBoxYear.Text) +
"," + int.Parse(this.textBoxPrice.Text) + "," + int.Parse(this.textBoxQ.Text) + ",'" +
this.textBoxTitle.Text + "')";
OleDbCommand cmdInsertItem = new OleDbCommand(strInsertItem, conn);
cmdInsertItem.ExecuteNonQuery();
cmdInsertItem.Dispose();
if (this.radioButtonBook.Checked == true)
{
string strInsertBook = "INSERT INTO book VALUES (" + int.Parse(this.textBoxID.Text) + ",'" + this.textBoxISDN.Text +
"'," + int.Parse(this.textBoxEdi.Text) + ",'" + this.textBoxAuthor.Text + "','" +
this.textBoxEditor.Text + "')";
OleDbCommand cmdInsertBook = new OleDbCommand(strInsertBook, conn);
cmdInsertBook.ExecuteNonQuery();
cmdInsertBook.Dispose();
}
else if (this.radioButtonCD.Checked == true)
{
string strInsertCD = "INSERT INTO CD VALUES (" + int.Parse(this.textBoxID.Text) + ",'" + this.textBoxSub.Text +
"','" + this.textBoxComp.Text + "','" + this.textBoxArtist.Text + "','" +
this.textBoxProducer.Text + "')";
OleDbCommand cmdInsertCD = new OleDbCommand(strInsertCD, conn);
cmdInsertCD.ExecuteNonQuery();
cmdInsertCD.Dispose();
}
MessageBox.Show("Add this item successfully");
}
}
catch (Exception ee)
{
throw (new Exception("Database error:" + ee.Message));
}
break;
case Delete:
try
{
//do we have to check if this item exist at firest?
string strDeleteItem = "DELETE From item WHERE iID = " + int.Parse(this.textBoxID.Text);
OleDbCommand cmdDeleteItem = new OleDbCommand(strDeleteItem, conn);
cmdDeleteItem.ExecuteNonQuery();
cmdDeleteItem.Dispose();
string strDeleteBook = "DELETE From book WHERE bookID = " + int.Parse(this.textBoxID.Text);
OleDbCommand cmdDeleteBook = new OleDbCommand(strDeleteBook, conn);
cmdDeleteBook.ExecuteNonQuery();
cmdDeleteBook.Dispose();
string strDeleteCD = "DELETE From CD WHERE cdID = " + int.Parse(this.textBoxID.Text);
OleDbCommand cmdDeleteCD = new OleDbCommand(strDeleteCD, conn);
cmdDeleteCD.ExecuteNonQuery();
cmdDeleteCD.Dispose();
MessageBox.Show("Delete this item successfully");
}
catch (Exception ee)
{
throw(new Exception("Delete error:" + ee.Message));
}
break;
case Change:
string strCheckU = "SELECT * FROM item WHERE iID =" + int.Parse(this.textBoxID.Text);
OleDbCommand cmdCheckU = new OleDbCommand(strCheckU, conn);
OleDbDataReader readerUpdateItem = cmdCheckU.ExecuteReader();
if (!readerUpdateItem.Read())
{
MessageBox.Show("This item ID does no exists!");
}
else
{
try
{
string strUpdateItem = "UPDATE item SET itemY = " + int.Parse(this.textBoxYear.Text) + ",itemP = "
+ this.textBoxPrice.Text + ",itemQ = " + this.textBoxQ.Text + ", itemT = '" + this.textBoxTitle.Text
+ "' WHERE iID = " + int.Parse(this.textBoxID.Text);
OleDbCommand cmdUpdateItem = new OleDbCommand(strUpdateItem, conn);
cmdUpdateItem.ExecuteNonQuery();
cmdUpdateItem.Dispose();
string strCheckBook = "SELECT * FROM book WHERE bookID =" + int.Parse(this.textBoxID.Text);
OleDbCommand cmdCheckBook = new OleDbCommand(strCheckBook, conn);
OleDbDataReader readerUpdateBook = cmdCheckBook.ExecuteReader();
if (readerUpdateBook.Read())
{
string strUpdateBook = "UPDATE book SET ISDN = '" + this.textBoxISDN.Text + "', edition =" + this.textBoxEdi.Text
+ ", author = '" + this.textBoxAuthor.Text + "', editor = '" + this.textBoxEditor.Text + "' WHERE bookID = "
+ int.Parse(this.textBoxID.Text);
OleDbCommand cmdUpdateBook = new OleDbCommand(strUpdateBook, conn);
cmdUpdateBook.ExecuteNonQuery();
cmdUpdateBook.Dispose();
}
else
{
string strUpdateCD = "UPDATE CD SET subject = '" + this.textBoxSub.Text + "', composer ='" + this.textBoxComp.Text
+ "', artist = '" + this.textBoxArtist.Text + "', producer = '" + this.textBoxProducer.Text + "' WHERE cdID = "
+ int.Parse(this.textBoxID.Text);
OleDbCommand cmdUpdateCD = new OleDbCommand(strUpdateCD, conn);
cmdUpdateCD.ExecuteNonQuery();
cmdUpdateCD.Dispose();
}
MessageBox.Show("Modify the information of this item successfully");
}
catch (Exception ee)
{
throw (new Exception("Update error: " + ee.Message));
}
}
break;
case Query:
string strQueryItem = "SELECT * FROM item WHERE iID =" + int.Parse(this.textBoxID.Text);
OleDbCommand cmdQueryItem = new OleDbCommand(strQueryItem, conn);
OleDbDataReader readerItem;
readerItem = cmdQueryItem.ExecuteReader(); //execute query and get according DataReader
if (readerItem.Read())
{
this.textBoxYear.Text = readerItem["itemY"].ToString();
this.textBoxPrice.Text = readerItem["itemP"].ToString();
this.textBoxQ.Text = readerItem["itemQ"].ToString();
this.textBoxTitle.Text = readerItem["itemT"].ToString();
string strQueryBook = "SELECT * FROM book WHERE bookID = " + int.Parse(this.textBoxID.Text);
OleDbCommand cmdQueryBook = new OleDbCommand(strQueryBook, conn);
OleDbDataReader readerBook = cmdQueryBook.ExecuteReader();
if (readerBook.Read())
{
this.textBoxISDN.Text = readerBook["ISDN"].ToString();
this.textBoxEdi.Text = readerBook["edition"].ToString();
this.textBoxAuthor.Text = readerBook["author"].ToString();
this.textBoxEditor.Text = readerBook["editor"].ToString();
}
else
{
string strQueryCD = "SELECT * FROM CD WHERE cdID = " + int.Parse(this.textBoxID.Text);
OleDbCommand cmdQueryCD = new OleDbCommand(strQueryCD, conn);
OleDbDataReader readerCD = cmdQueryCD.ExecuteReader();
if (readerCD.Read())
{
this.textBoxSub.Text = readerCD["subject"].ToString();
this.textBoxComp.Text = readerCD["composer"].ToString();
this.textBoxArtist.Text = readerCD["artist"].ToString();
this.textBoxProducer.Text = readerCD["producer"].ToString();
}
}
}
else
{
MessageBox.Show("This item ID does no exists!");
}
break;
default:
break;
}
conn.Close();
}
}
protected void Page_Load(object sender, EventArgs e)
{
OleDbConnection cn;
OleDbCommand cmd, cmd1;
OleDbDataReader dr1, dr2;
string query;
if (!IsPostBack)
{
txtName.Text = Profile.FirstName;
txtStreet.Text = Profile.Street;
txtCity.Text = Profile.City;
txtState.Text = Profile.State;
txtEmail.Text = Profile.Email;
txtZip.Text = Profile.Zip;
try
{
cn = new OleDbConnection();
if (Request.UserHostAddress.ToString().Equals("::1"))
{
//Local server...
cn.ConnectionString =
@"Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\Tadto\source\Websites\WebSite9\App_Data\ShoppingDatabase1.accdb;Persist Security Info=False;";
}
else
{
//Smarterasp.net access
cn.ConnectionString =
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=h:\root\home\tadtoo22-001\www\xeffles\App_Data\ShoppingDatabase1.accdb;Persist Security Info=False;";
}
//Create the SQL Command...
//cmd = new OleDbCommand("SELECT * FROM InvoiceQuery", cn);
cmd = new OleDbCommand("SELECT * FROM NextOrderNumber WHERE CompanyName = 'Quokka Team';", cn);
cn.Open();
dr1 = cmd.ExecuteReader();
while (dr1.Read())
{
companyName = dr1["CompanyName"].ToString();
ordNumber = Convert.ToInt32(dr1["NextOrder"]);
}
Session["OrderNumber"] = ordNumber;
Session["CompanyName"] = companyName;
dr1.Close();
try {
query = "UPDATE NextOrderNumber SET NextOrder = " + (ordNumber + 1) + " WHERE CompanyName = '" + companyName + "';";
cmd1 = new OleDbCommand(query, cn);
cmd1.ExecuteNonQuery();
}
catch
{
}
cn.Close();
}
catch (Exception err)
{
return;
}
int quantityTotal;
double weight, orderTotal, shipping, totalCost;
weight = orderTotal = shipping = totalCost = quantityTotal = 0;
cart = (List <ItemInfo>)Session["Cart"];
if (cart == null)
{
}
else
{
for (int i = 0; i < cart.Count; i++)
{
quantityTotal += cart[i].Quantity;
orderTotal += cart[i].Price * cart[i].Quantity;
weight += cart[i].Weight * cart[i].Quantity;
}
shipping = weight * 0.46;
Table tbl = new Table();
TableRow r1 = new TableRow();
TableRow r2 = new TableRow();
TableRow r3 = new TableRow();
TableRow r4 = new TableRow();
TableRow r5 = new TableRow();
TableRow r6 = new TableRow();
TableRow r7 = new TableRow();
TableHeaderCell items1;
TableCell items2;
items1 = new TableHeaderCell();
items2 = new TableCell();
items1.Text = "Items:";
items2.Text = cart.Count.ToString();
r1.Cells.Add(items1);
r1.Cells.Add(items2);
TableHeaderCell quantity1 = new TableHeaderCell();
quantity1.Text = "Quantity: ";
TableCell quantity2 = new TableCell();
quantity2.Text = quantityTotal.ToString();
r2.Cells.Add(quantity1);
r2.Cells.Add(quantity2);
TableHeaderCell weightCell = new TableHeaderCell();
weightCell.Text = "Weight: ";
TableCell weightCell2 = new TableCell();
weightCell2.Text = weight.ToString();
r3.Cells.Add(weightCell);
r3.Cells.Add(weightCell2);
TableHeaderCell orderNum = new TableHeaderCell();
orderNum.Text = "Order Number: ";
TableCell orderNum2 = new TableCell();
orderNum2.Text = companyName + "-" + ordNumber;
r4.Cells.Add(orderNum);
r4.Cells.Add(orderNum2);
TableHeaderCell itemTotal = new TableHeaderCell();
itemTotal.Text = "Item Total: ";
TableCell itemTotal2 = new TableCell();
itemTotal2.Text = "$" + String.Format("{0:0.00}", orderTotal);
r5.Cells.Add(itemTotal);
r5.Cells.Add(itemTotal2);
TableHeaderCell shippingCost = new TableHeaderCell();
shippingCost.Text = "Shipping Cost: ";
TableCell shippingCost2 = new TableCell();
shippingCost2.Text = "$" + String.Format("{0:0.00}", shipping);
r6.Cells.Add(shippingCost);
r6.Cells.Add(shippingCost2);
TableHeaderCell orderTotalCell = new TableHeaderCell();
orderTotalCell.Text = "Order Total: ";
TableCell orderTotalCell2 = new TableCell();
orderTotalCell2.Text = "$" + String.Format("{0:0.00}", shipping + orderTotal);
r7.Cells.Add(orderTotalCell);
r7.Cells.Add(orderTotalCell2);
tbl.Rows.Add(r4);
tbl.Rows.Add(r1);
tbl.Rows.Add(r2);
tbl.Rows.Add(r3);
tbl.Rows.Add(r5);
tbl.Rows.Add(r6);
tbl.Rows.Add(r7);
tbl.Attributes.Add("width", "50%");
tableHere.Controls.Add(tbl);
}
}
List <ItemInfo> itemInfoList = (List <ItemInfo>)Session["Cart"];
int j = 0;
while (j < itemInfoList.Count)
{
HtmlGenericControl createDiv = new HtmlGenericControl("div");
createDiv.ID = "row" + j;
createDiv.Attributes["class"] = "row";
TableRow tr = new TableRow();
// Make a panel for each cell and populate it with data
for (int i = 0; i < 4; i++)
{
if (j >= itemInfoList.Count)
{
break;
}
ItemInfo info = itemInfoList[j];
string url = "BookInfoPage.aspx?";
url += "Item=" + info.ModelNumber;
HtmlGenericControl createCol = new HtmlGenericControl("div");
createCol.ID = "col" + j + "" + i;
createCol.Attributes["class"] = "col-md-3";
HtmlGenericControl createPanel = new HtmlGenericControl("div");
createPanel.ID = "colPanel" + j + "" + i;
createPanel.Attributes["class"] = "panel panel-primary";
HtmlGenericControl createPanelHeader = new HtmlGenericControl("div");
createPanelHeader.InnerHtml = info.Title + " x " + info.Quantity;
createPanelHeader.Attributes["class"] = "panel-heading";
Image image = new ImageButton();
image.ImageUrl = info.ImgThumb;
HtmlGenericControl createImg = new HtmlGenericControl("img");
createImg.Attributes["src"] = info.ImgThumb;
createImg.Attributes["class"] = "img-responsive";
HtmlGenericControl createThumbnail = new HtmlGenericControl("div");
createThumbnail.Attributes["class"] = "thumbnail";
createThumbnail.Controls.Add(createImg);
HtmlGenericControl createPanelBody = new HtmlGenericControl("div");
createPanelBody.Attributes["class"] = "panel-body";
createPanelBody.Controls.Add(createThumbnail);
createPanel.Controls.Add(createPanelHeader);
createPanel.Controls.Add(createPanelBody);
createCol.Controls.Add(createPanel);
createDiv.Controls.Add(createCol);
j++;
}
tableHeres.Controls.Add(createDiv);
}
}
public static List <ModelBL> GetListModelBL(string fullPath)
{
List <ModelBL> listData = new List <ModelBL>();
try
{
DataTable dtResult = new DataTable();
int totalSheet = 0;
using (OleDbConnection objConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullPath + ";Extended Properties='Excel 12.0;HDR=NO;IMEX=1;';"))
{
objConn.Open();
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter oleda = new OleDbDataAdapter();
DataSet ds = new DataSet();
DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = string.Empty;
if (dt != null)
{
var tempDataTable = (from dataRow in dt.AsEnumerable()
where !dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase")
select dataRow).CopyToDataTable();
dt = tempDataTable;
totalSheet = dt.Rows.Count;
sheetName = dt.Rows[0]["TABLE_NAME"].ToString();
}
cmd.Connection = objConn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
oleda = new OleDbDataAdapter(cmd);
oleda.Fill(ds, "excelData");
dtResult = ds.Tables["excelData"];
objConn.Close();
}
dtResult = dtResult.Rows.Cast <DataRow>().Where(row => !row.ItemArray.All(field => field is DBNull || string.IsNullOrWhiteSpace(field as string))).CopyToDataTable();
if (dtResult.Rows.Count > 0 && dtResult.Rows[0]["F9"].ToString() == "Manual" && dtResult.Rows[2]["F1"].ToString() == "BRAND")
{
var listModelBLData = (from table in dtResult.AsEnumerable()
select new ModelBL
{
Title = GetTitle(table["F7"].ToString(), table["F8"].ToString()),
BRAND = table["F1"].ToString(),
BL_CATEGORY = table["F2"].ToString(),
BL_PRODUCT_TYPE = table["F3"].ToString(),
BL_PRODUCT_SIZE = table["F5"].ToString(),
REFRIGERANT = table["F6"].ToString(),
INDOOR = table["F7"].ToString(),
OUTDOOR = table["F8"].ToString(),
INSTALLATION = table["F9"].ToString(),
OWNER = table["F10"].ToString(),
DISC = table["F11"].ToString(),
SPECIFICATION = table["F12"].ToString(),
BULLETIN = table["F13"].ToString(),
DATABOOK = table["F14"].ToString(),
VDO = table["F15"].ToString(),
PRESENTATION = table["F16"].ToString(),
IMAGE_LOW = table["F17"].ToString(),
IMAGE_HD = table["F18"].ToString(),
CATALOGUE = table["F19"].ToString()
}).ToList();
listModelBLData.RemoveAt(0);
listModelBLData.RemoveAt(0);
listModelBLData.RemoveAt(0);
listData = listModelBLData;
Utility.WriteLog("Read : " + fullPath, "Success");
return(listData);
}
else
{
return(listData);
}
}
catch (Exception ex)
{
Utility.WriteLog(ex.Message.ToString(), "Error");
return(listData);
}
}
public int UpdateEx(DatabaseSet.工作單品號DataTable table)
{
int count = 0;
foreach (DatabaseSet.工作單品號Row row in table)
{
if (row.RowState != DataRowState.Added)
continue;
string idCmdTxt = "SELECT MAX(編號) FROM 工作單品號 WHERE 單號 = ?";
OleDbCommand cmd = new OleDbCommand(idCmdTxt, Instance.Connection);
cmd.Parameters.Add(new OleDbParameter("單號", row.單號));
Instance.Connection.Open();
object result = cmd.ExecuteScalar();
Instance.Connection.Close();
int id = 0;
if (result != DBNull.Value)
id = Convert.ToInt32(result);
row.編號 = id+1;
count += Instance.Update(row);
}
//string insertCmdTxt = "INSERT INTO 工作單品號 (單號, 品號, 數量, 實際完成日, 客戶需貨日, 預計完成日, 客戶, 編號) " +
// "VALUES (?, ?, ?, ?, ?, ?, ?, (SELECT MAX(編號) FROM 工作單品號 WHERE 單號 = ?))";
//string updateCmdTxt = "UPDATE 工作單品號 SET 單號=?, 品號=?, 數量=?, 實際完成日=?, 客戶需貨日=?, 預計完成日=?, 客戶 = ?, 編號 = ?";
//OleDbCommand insertCmd = new OleDbCommand(insertCmdTxt, Instance.Connection);
//insertCmd.Parameters.Add("單號", OleDbType.VarWChar);
//insertCmd.Parameters.Add("品號", OleDbType.VarWChar);
//insertCmd.Parameters.Add("數量", OleDbType.VarWChar);
//insertCmd.Parameters.Add("實際完成日", OleDbType.Date);
//insertCmd.Parameters.Add("客戶需貨日", OleDbType.Date);
//insertCmd.Parameters.Add("預計完成日", OleDbType.Date);
//insertCmd.Parameters.Add("客戶", OleDbType.VarWChar);
//insertCmd.Parameters.Add("單號2", OleDbType.VarWChar,255,"單號");
//Instance.Adapter.InsertCommand = insertCmd;
////OleDbCommand updateCmd = new OleDbCommand(updateCmdTxt, Instance.Connection);
////Instance.Adapter.UpdateCommand = updateCmd;
count += Instance.Update(table);
return count;
}
public int Reinspect(string from, string to, string qcn, int amount)
{
this.Connection.Open();
OleDbTransaction transaction = this.Connection.BeginTransaction();
int result = 0;
try
{
string cmdText = "SELECT 送檢次數 FROM 產品檢驗 WHERE 工時資料編號 = ?";
OleDbCommand cmd = new OleDbCommand(cmdText, this.Connection);
cmd.Transaction = transaction;
cmd.Parameters.Add(new OleDbParameter("工時資料編號", from));
int num = Convert.ToInt32(cmd.ExecuteScalar());
cmdText = "INSERT INTO 產品檢驗 (工時資料編號,QCN,待驗數量,送檢次數,最後送檢編號,送檢日期) VALUES (?,?,?,?,?,?)";
cmd = new OleDbCommand(cmdText, this.Connection);
cmd.Transaction = transaction;
cmd.Parameters.Add(new OleDbParameter("工時資料編號1", to));
cmd.Parameters.Add(new OleDbParameter("QCN", qcn));
cmd.Parameters.Add(new OleDbParameter("待驗數量", amount));
cmd.Parameters.Add(new OleDbParameter("送檢次數", num + 1));
cmd.Parameters.Add(new OleDbParameter("最後送檢編號", to));
//cmd.Parameters.Add(new OleDbParameter("送檢日期", DateTime.Today));
OleDbParameter paramDate = new OleDbParameter();
paramDate.OleDbType = OleDbType.DBTimeStamp;
paramDate.Value = DateTime.Now.ToString("s");
cmd.Parameters.Add(paramDate);
result = cmd.ExecuteNonQuery();
cmdText = "UPDATE 產品檢驗 SET 重驗 = True WHERE 工時資料編號 = ?";
cmd = new OleDbCommand(cmdText, this.Connection);
cmd.Transaction = transaction;
//cmd.Parameters.Add(new OleDbParameter("最後送檢編號", to));
cmd.Parameters.Add(new OleDbParameter("工時資料編號", from));
cmd.ExecuteNonQuery();
cmdText = "UPDATE 產品檢驗 SET 最後送檢編號 = ? WHERE 最後送檢編號 = ?";
cmd = new OleDbCommand(cmdText, this.Connection);
cmd.Transaction = transaction;
cmd.Parameters.Add(new OleDbParameter("最後送檢編號1", to));
cmd.Parameters.Add(new OleDbParameter("最後送檢編號2", from));
cmd.ExecuteNonQuery();
cmdText = "INSERT INTO NG原因 (工時資料編號, 原因, 來源編號) SELECT '" + to + "' as 工時資料編號, 原因, 來源編號 FROM NG原因 WHERE 工時資料編號 = ?";
cmd = new OleDbCommand(cmdText, this.Connection);
cmd.Transaction = transaction;
cmd.Parameters.Add(new OleDbParameter("工時資料編號", from));
cmd.ExecuteNonQuery();
transaction.Commit();
this.Connection.Close();
return result;
}
catch (Exception ex)
{
transaction.Rollback();
this.Connection.Close();
throw ex;
}
}
//抽奖算法函数
private void lottyCtrl()
{
//开始
string strPath = "";
int ocont = 0;
ocont = SeedsList.Items.Count - 1;
//随机数
if (timer_lottery.Enabled == false)
{
if (ocont > 20)
{
timer_lottery.Enabled = true;
strPath = System.IO.Directory.GetCurrentDirectory() + "\\images\\btn_end.png";
BtnStart.Image = Image.FromFile(strPath);
}
else
{
MessageBox.Show("已没有可供抽取的人员，抽奖中止");
}
}
else
{
strPath = System.IO.Directory.GetCurrentDirectory() + "\\images\\btn_start.png";
BtnStart.Image = Image.FromFile(strPath);
timer_lottery.Enabled = false;
//从数据库中抽一个人，显示，记录
OleDbConnection odcConnection = new OleDbConnection(strConn);
odcConnection.Open();
OleDbCommand odCommand = odcConnection.CreateCommand();
DateTime dt = DateTime.Now;
char[] delimiterChars = { ';' };
byte[] bytes = new byte[4];
for (int i = 0; i < 20; i++) //抽二十个奖
{
Application.DoEvents();
System.Security.Cryptography.RNGCryptoServiceProvider rng = new System.Security.Cryptography.RNGCryptoServiceProvider();
rng.GetBytes(bytes);
Random rnd2 = new Random(BitConverter.ToInt32(bytes, 0));
int rndNum = rnd2.Next(0, SeedsList.Items.Count - 1);
string Rtext = SeedsList.Items[rndNum].ToString();
string[] sArray = Rtext.Split(delimiterChars);
LabName[i].Text = sArray[2] + "[" + sArray[3] + "]";
LabCorp[i].Text = sArray[1];
string inaward = sArray[0].ToString();
string inaward_sql = "INSERT INTO AwardList(award,employee_dept,employee_name,employee_no,pubdate) VALUES('" + AwardName + "','" + sArray[1].ToString() + "','" + sArray[2].ToString() + "','" + sArray[3].ToString() + "','" + dt.ToString("yyyyMMddHHmmss") + "')";
odCommand.CommandText = "update seedlist set award_flag = '1' where id = " + inaward;
odCommand.ExecuteNonQuery();
odCommand.CommandText = inaward_sql;
odCommand.ExecuteNonQuery();
SeedsList.Items.Clear();
odCommand.CommandText = "select id,employee_dept,employee_name,employee_no from Seedlist where award_flag = '0' order by id asc";
OleDbDataReader odrReader = odCommand.ExecuteReader();
while (odrReader.Read())
{
SeedsList.Items.Add(odrReader[0].ToString() + ";" + odrReader[1].ToString() + ";" + odrReader[2].ToString() + ";" + odrReader[3].ToString());
}
odrReader.Close();
}
odcConnection.Close();
}
}
public int FillFilledDataBy員工編號(DatabaseSet.工時DataTable table, string 員工編號)
{
員工編號 = 員工編號.Replace("'","''");
OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "SELECT 員工編號, 日期 FROM 工時 GROUP BY 員工編號, 日期" +
" HAVING 員工編號 = '" + 員工編號 + "' AND 日期 >= #" + Settings.UnfilledDate.ToString("yyyy/MM/dd") + "# AND 日期 <= #" + DateTime.Today.ToString("yyyy/MM/dd") + "# AND SUM(工時) >= " + Settings.WorkingHoursPerDay;
cmd.Connection = this.Connection;
this.Adapter.SelectCommand = cmd;
return this.Adapter.Fill(table);
}
public int GetFinishProductAmount(string worksheet, int wpid)
{
string cmdTxt = "SELECT SUM(數量) FROM 工時 WHERE 工作單號 = '" + worksheet + "' AND 工品編號 = " + wpid + " GROUP BY 工作單號, 工品編號";
OleDbCommand cmd = new OleDbCommand(cmdTxt);
cmd.Connection = this.Connection;
this.Connection.Open();
object o = cmd.ExecuteScalar();
int result = 0;
if (o != null && o != DBNull.Value)
result = (int)(double)o;
this.Connection.Close();
return result;
}
public int Fill(DatabaseSet.工時DataTable table, string 員工編號, bool date, DateTime from, DateTime to)
{
員工編號 = 員工編號.Replace("'", "''");
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("SELECT 工時.編號, 借入產線, 員工編號, 工時.日期, 工作單號, WP.編號 as 工品編號, 品號, 工時, 工時類型, 工時.數量, 待驗數量, QCN, 非生產編號, 備註, 非生產.名稱 as 非生產名稱, 員工.姓名 as 員工姓名 " +
"FROM ((( 工時 INNER JOIN 非生產 ON 工時.非生產編號 = 非生產.編號 ) " +
" LEFT JOIN 產品檢驗 ON 工時.編號 = 產品檢驗.工時資料編號 )" +
" LEFT JOIN 工作單品號 as WP ON 工時.工作單號 = WP.單號 AND 工時.工品編號 = WP.編號) " +
" INNER JOIN 員工 ON 工時.員工編號 = 員工.編號 ");
System.Collections.Generic.List<string> whereCond = new System.Collections.Generic.List<string>();
if (員工編號.Trim() != string.Empty)
whereCond.Add("工時.員工編號 ='" + 員工編號 + "'");
if (date)
{
whereCond.Add("工時.日期 >= #" + from.ToString("yyyy/MM/dd") + "#");
whereCond.Add("工時.日期 <= #" + to.ToString("yyyy/MM/dd") + "#");
}
if (whereCond.Count > 0)
{
string whereStr = string.Join(" AND ", whereCond.ToArray());
sb.Append(" WHERE " + whereStr);
}
sb.Append(" ORDER BY 工時.日期,工時.編號 ");
OleDbCommand cmd = new OleDbCommand(sb.ToString());
cmd.Connection = this.Connection;
this.Adapter.SelectCommand = cmd;
return this.Adapter.Fill(table);
}
public int Fill(DatabaseSet.工時DataTable table, string 員工編號, int 非生產編號, bool date, DateTime from, DateTime to)
{
員工編號 = 員工編號.Replace("'", "''");
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("SELECT 工時.編號, 借入產線, 員工編號, 日期, 工作單號, NULL as 品號, 工時, 工時類型, 數量, NULL as 待驗數量, 非生產編號, 備註, 非生產.名稱 AS 非生產名稱, 員工.姓名 AS 員工姓名 FROM ( 工時 INNER JOIN 非生產 ON 工時.非生產編號 = 非生產.編號 ) INNER JOIN 員工 ON 工時.員工編號 = 員工.編號 ");
System.Collections.Generic.List<string> whereCond = new System.Collections.Generic.List<string>();
if (員工編號.Trim() != string.Empty)
whereCond.Add("工時.員工編號 ='" + 員工編號 + "'");
whereCond.Add("工時.非生產編號 =" + 非生產編號);
if (date)
{
whereCond.Add("日期 >= #" + from.ToString("yyyy/MM/dd") + "#");
whereCond.Add("日期 <= #" + to.ToString("yyyy/MM/dd") + "#");
}
if (whereCond.Count > 0)
{
string whereStr = string.Join(" AND ", whereCond.ToArray());
sb.Append(" WHERE " + whereStr);
}
sb.Append(" ORDER BY 日期,工時.編號 ");
OleDbCommand cmd = new OleDbCommand(sb.ToString());
cmd.Connection = this.Connection;
this.Adapter.SelectCommand = cmd;
return this.Adapter.Fill(table);
}
public int DeleteEx(string id)
{
int retVal = 0;
OleDbConnection conn = Instance.Connection;
OleDbTransaction transaction = null;
OleDbCommand cmd = new OleDbCommand();
conn.Open();
try
{
transaction = conn.BeginTransaction();
cmd.Connection = conn;
cmd.Transaction = transaction;
//改正產品檢驗資料
//取得產品檢驗資料
cmd.CommandText = "SELECT * FROM 產品檢驗 WHERE 工時資料編號=?";
cmd.Parameters.Clear();
cmd.Parameters.Add(new OleDbParameter("工時資料編號", id));
OleDbDataReader reader = cmd.ExecuteReader();
if (reader.Read())
{
string last = reader["最後送檢編號"].ToString();
int inspectDel = (int)reader["送檢次數"];
reader.Close();
cmd.CommandText = "SELECT * FROM 產品檢驗 WHERE 最後送檢編號=? AND 工時資料編號<>? ORDER BY 送檢次數 ASC";
cmd.Parameters.Clear();
cmd.Parameters.Add(new OleDbParameter("最後送檢編號old", last));
cmd.Parameters.Add(new OleDbParameter("工時資料編號", id));
OleDbDataReader reader2 = cmd.ExecuteReader();
while (reader2.Read())
{
string nextID = reader2["工時資料編號"].ToString();
int inspect = (int)reader2["送檢次數"];
if (inspect > inspectDel)
{
//將該筆之後的檢驗資料做處理
OleDbCommand cmd2 = new OleDbCommand();
cmd2.Connection = conn;
cmd2.Transaction = transaction;
cmd2.CommandText = "UPDATE 產品檢驗 SET 送檢次數=送檢次數-1 WHERE 工時資料編號=?";
cmd2.Parameters.Clear();
cmd2.Parameters.Add(new OleDbParameter("工時資料編號", nextID));
cmd2.ExecuteNonQuery();
}
else
{
//將該筆之前的檢驗資料做處理
}
}
reader2.Close();
//刪除產品檢驗資料
cmd.CommandText = "DELETE FROM 產品檢驗 WHERE 工時資料編號=?";
cmd.Parameters.Clear();
cmd.Parameters.Add(new OleDbParameter("工時資料編號", id));
cmd.ExecuteNonQuery();
//挑出最後一筆檢驗紀錄
cmd.CommandText = "SELECT 工時資料編號 FROM 產品檢驗 WHERE 最後送檢編號 =? ORDER BY 送檢次數 DESC";
cmd.Parameters.Clear();
cmd.Parameters.Add(new OleDbParameter("最後送檢編號old", last));
reader2 = cmd.ExecuteReader();
if (reader2.Read())
{
string maxID = reader2["工時資料編號"].ToString();
reader2.Close();
//將原有的最後檢驗紀錄更新
cmd.CommandText = "UPDATE 產品檢驗 SET 最後送檢編號=?, 最後檢驗紀錄=FALSE, 重驗=TRUE WHERE 最後送檢編號=?";
cmd.Parameters.Clear();
cmd.Parameters.Add(new OleDbParameter("最後送檢編號new", maxID));
cmd.Parameters.Add(new OleDbParameter("最後送檢編號old", last));
cmd.ExecuteNonQuery();
//將最後一筆設成最後檢驗記錄
cmd.CommandText = "UPDATE 產品檢驗 SET 最後檢驗紀錄=TRUE, 重驗=FALSE WHERE 工時資料編號=?";
cmd.Parameters.Clear();
cmd.Parameters.Add(new OleDbParameter("工時資料編號", maxID));
cmd.ExecuteNonQuery();
}
if (!reader2.IsClosed)
reader2.Close();
}
if (!reader.IsClosed)
reader.Close();
//刪除工時資料
cmd.CommandText = "DELETE FROM 工時 WHERE 編號=?";
cmd.Parameters.Clear();
cmd.Parameters.Add(new OleDbParameter("編號", id));
retVal = cmd.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
try
{
transaction.Rollback();
}
catch (Exception) { }
try
{
conn.Close();
}
catch (Exception) { }
throw ex;
}
conn.Close();
return retVal;
}
/// <summary> Set the next roast Date by city, notice this forces the calculation so if you do not want to run it every time check if
/// IsNextRoastDateByCityTodays, i.e. has it been calcualted already </summary>
public void SetNextRoastDateByCity()
{
string _sqlStr = "SELECT CityID, PrepDayOfWeekID, DeliveryDelayDays, DeliveryOrder FROM CityPrepDaysTbl ORDER BY CityID, PrepDayOfWeekID";
int _iCityID = 0;
byte _FirstPDOW;
short _FirstDeliveryDelay, _FirstSortOrder;
byte _ThisPDOW = (byte)System.DayOfWeek.Monday; // monday is the first day of the week in C# while in VBA it was Sunday
short _ThisDeliveryDelay = 0, _ThisSortOrder = 0;
short _NextDeliveryDelay = 0, _NextSortOrder = 0;
DateTime dtNow = System.DateTime.Now;
DateTime _dtPrep, _dtDelivery;
// NOTE for the database Sunday = 1 for System.DateTime.Now.DayOfWeek Sunday = 0; so this need to be remembers when doing coparisons and calculations
byte _TodaysDOW = (byte)System.DateTime.Now.DayOfWeek;
// (byte)((byte)System.DateTime.Now.DayOfWeek+1);
//if (_TodaysDOW > 7) _TodaysDOW = 1; // fix Sunday;
bool _bFirst, _bFound, _bIsSameCity = false, _EOF = false;
// open a database connection if it is not open already
if (TToolCon == null)
{
OpenTToolsConnection();
}
OleDbCommand _Cmd = new OleDbCommand(_sqlStr, TToolCon);
OleDbDataReader _Reader = _Cmd.ExecuteReader();
// count how many cites there are
// List <NextRoastDateByCityTbl> _NRDbyCities = new List<NextRoastDateByCityTbl>(); // that would be the max
// read the data if it is there
// while (_Reader.Read())
_EOF = !_Reader.Read();
while (!_EOF)
{
_iCityID = (int)_Reader["CityID"];
// set the first item of this collection
_FirstPDOW = GetCorrectedDOW((byte)_Reader["PrepDayOfWeekID"]);
// set the dow of week depending on what is the first day of the week
_FirstDeliveryDelay = (short)_Reader["DeliveryDelayDays"];
_FirstSortOrder = (short)((_Reader["DeliveryOrder"] == DBNull.Value) ? 1 : _Reader["DeliveryOrder"]);
_bFound = false;
// find this cities next roast day, search until city changes or end of file
while ((!_EOF) && (!_bFound))
{
_ThisPDOW = GetCorrectedDOW((byte)_Reader["PrepDayOfWeekID"]);
_ThisDeliveryDelay = (short)_Reader["DeliveryDelayDays"];
_ThisSortOrder = (short)((_Reader["DeliveryOrder"] == DBNull.Value) ? 1 : _Reader["DeliveryOrder"]);
// go to next record exit if we are on another city
_EOF = !_Reader.Read();
if (!_EOF)
{
_bFound = ((_TodaysDOW <= _ThisPDOW) || (_iCityID != (int)_Reader["CityID"])); // ' time check?
}
}
//
if (!_EOF)
{
_bFirst = ((_iCityID != (int)_Reader["CityID"]) && (_TodaysDOW > _ThisPDOW));
// go to the next City ID, if we get here then we have found a prep day for this City so we must skip ahead
_bIsSameCity = (_iCityID == (int)_Reader["CityID"]);
if (_bIsSameCity) // we have found a day that is after the next day
{
while ((!_EOF) && (_bIsSameCity))
{
_EOF = !_Reader.Read();
if (!_EOF)
{
_bIsSameCity = (_iCityID == (int)_Reader["CityID"]);
}
}
} // not eof
else if (!_bFound)
{
_bFirst = true; // must be the first record
}
if (_bFirst) // this means there are no pre days this week
{
if (_FirstPDOW >= _ThisPDOW)
{
_dtPrep = DateTime.Now.AddDays(_FirstPDOW - _TodaysDOW);
}
else
{
_dtPrep = DateTime.Now.AddDays((int)(7 - _TodaysDOW + _FirstPDOW));
}
_NextDeliveryDelay = _FirstDeliveryDelay;
_NextSortOrder = _FirstSortOrder;
} // use the first
else
{
_dtPrep = DateTime.Now.AddDays(_ThisPDOW - _TodaysDOW);
_NextDeliveryDelay = _ThisDeliveryDelay;
_NextSortOrder = _ThisSortOrder;
} // use the next
// insert or udpate the records depending on whether or not the records exists.
_dtDelivery = _dtPrep.AddDays(_NextDeliveryDelay);
bool _Success = UpdateOrInsertCityNextRstDate(_iCityID, _dtPrep, _dtDelivery, _NextSortOrder); // may want do do something here
}
} // end loop while
_Reader.Close();
_Cmd.CommandText = "UPDATE SysDataTbl SET DateLastPrepDateCalcd = " + DateTime.Now.ToShortDateString() + " WHERE ID=1";
_Cmd.ExecuteNonQuery();
// kill memory used
_Cmd.Dispose();
_Reader.Close();
_Reader.Dispose();
}
public static string GetOtherName()
{
string sql = "SELECT * FROM 非生產 WHERE 編號 = " + Global.NonProduct_Other;
OleDbCommand cmd = new OleDbCommand(sql, Instance.Connection);
Instance.Connection.Open();
OleDbDataReader dr = cmd.ExecuteReader();
dr.Read();
string name = dr["名稱"].ToString();
dr.Close();
Instance.Connection.Close();
return name;
}
/// <summary>
/// Delete Data
/// </summary>
/// <param name="_step">Query Selector</param>
/// <returns>true or false</returns>
public bool DeleteData(int _step)
{
string strQuery = "";
try
{
if (_dbc.gErrors.MsgInit() == false)
{
return(false);
}
OleDbCommand adoCommand = new OleDbCommand();
// Set Connection, Transaction
adoCommand.Connection = _dbc.gOleDbConnection;
adoCommand.Transaction = _dbc.gOleDbTransaction;
switch (_step)
{
case 1:
// Delete by Primary Key
strQuery = "DELETE FROM MFMBFACLYT"
+ " WHERE FACTORY=?"
+ " AND LAYOUT_ID=?";
// Add Parameters
adoCommand.Parameters.Add("@FACTORY", OleDbType.VarChar).Value = FACTORY;
adoCommand.Parameters.Add("@LAYOUT_ID", OleDbType.VarChar).Value = LAYOUT_ID;
break;
case 101:
// TODO : User Select Query
break;
default:
// Error Handling
_dbc.gErrors.SqlCode = SQL_CODE.SQL_CASE_ERROR;
return(false);
}
// ExecuteNonQuery()
adoCommand.CommandText = strQuery;
_dbc.gErrors.AddQuery(strQuery, adoCommand.Parameters);
if (adoCommand.ExecuteNonQuery() < 1)
{
// Data Not Found
_dbc.gErrors.SqlCode = SQL_CODE.SQL_NOT_FOUND;
// Dispose
adoCommand.Dispose();
return(false);
}
// Dispose
adoCommand.Dispose();
}
catch (Exception ex)
{
if (ex.GetType().ToString() == "System.Data.OleDb.OleDbException")
{
_dbc.gErrors.SetErrors(((OleDbException)ex).Errors);
}
else
{
_dbc.gErrors.SetErrors(ex);
}
return(false);
}
return(true);
}
public FuncStatus DataBind(BindOperator _bo)
{
bool _tf = true;
OleDbConnection _conn = new OleDbConnection(Prument.Properties.Settings.Default.Properties["dataConnectionString"].DefaultValue.ToString());
OleDbCommand _cmd = new OleDbCommand();
OleDbDataReader _dr;
_cmd.Connection = _conn;
try
{
_conn.Open();
if (_bo == BindOperator.Select)
{
_cmd.CommandText = "select Products.ID,Products.Name,Products.Description from Products where " + (this.ID != 0 ? "ID=" + this.ID.ToString() : (this.Name != null ? "Name='" + this.Name : "Description like '_" + this.Description + "_") + "'");
}
if (_bo == BindOperator.Insert)
{
_cmd.CommandText = "insert into Products (Name,Description) values ('" + this.Name + "','" + this.Description + "')";
_cmd.ExecuteScalar();
_cmd.CommandText = "select ID,Name,Description from products where id = (select Max(ID) from Products)";
}
if (_bo == BindOperator.Update) {
_cmd.CommandText = "update products set Name='" + this.Name + "',Description='" + this.Description + "' where ID=" +this.ID;
_cmd.ExecuteNonQuery();
_cmd.CommandText = "select ID,Name,Description from products where id = " + this.ID;
}
if (_bo == BindOperator.Delete)
{
_cmd.CommandText = "select ID,Name,Description from products where id = " + this.ID;
}
_dr = _cmd.ExecuteReader();
_dr.Read();
this.ID = _dr.GetInt32(0);
this.Name = _dr.GetValue(1).ToString();
this.Description = _dr.GetValue(2).ToString();
this.Images.pID = this.ID;
_dr.Close();
if (_bo == BindOperator.Select) {
Images.DataBind(_bo);
}
if (_bo == BindOperator.Insert) {
Images.DataBind(_bo);
}
if (_bo == BindOperator.Update && this.Images.Path[0] != "") {
Images.DataBind(_bo);
}
if (_bo == BindOperator.Delete)
{
_cmd.CommandText = "delete from products where ID=" + this.ID;
_cmd.ExecuteNonQuery();
this.Images.clear();
this.Images.DataBind(BindOperator.Update);
}
this.Status = ORMStatus.Saved;
}
catch (Exception ex)
{
_tf = false;
Exception _ex = new Exception("ORM.Products["+this.ID+"]:" + ex.Message);
throw _ex;
}
finally {
_conn.Close();
}
return _tf == false?FuncStatus.Fail:FuncStatus.Success;
}
protected override void RefreshChart()
{
List <long> types = new List <long>();
string sqlCmd = "select distinct(t1.TEST_ID) from TEST_TIME_DISTRIBUTION t1,"
+ " test_results t2 "
+ " where t2.test_time >= "
+ " #" + Properties.Settings.Default.DefaultDateTimeStart.ToString("yyyy-MM-dd HH:mm:ss") + "# "
+ " and t2.test_time < "
+ " #" + Properties.Settings.Default.DefaultDateTimeEnd.ToString("yyyy-MM-dd HH:mm:ss") + "# "
+ " and t1.test_id = t2.test_id "
+ " and t2.STATION like '%" + Properties.Settings.Default.DefaultTestBench + "%' "
+ " and t2.PRODUCT_NAME like '%" + Properties.Settings.Default.ProductType + "%' "
+ " and (t2.test_id in( "
+ " select test_id from ("
+ " select test_id,count(test_id) as num from test_item_values "
+ " group by test_id"
+ " ) t1_1,"
+ " ("
+ " select max(num) as maxNum from "
+ " ("
+ " select count(st1.test_id) as num "
+ " from test_item_values st1, test_results st2 "
+ " where st2.test_time >= "
+ " #" + Properties.Settings.Default.DefaultDateTimeStart.ToString("yyyy-MM-dd HH:mm:ss") + "# "
+ " and st2.test_time < "
+ " #" + Properties.Settings.Default.DefaultDateTimeEnd.ToString("yyyy-MM-dd HH:mm:ss") + "# "
+ " and st2.STATION like '%" + Properties.Settings.Default.DefaultTestBench + "%' "
+ " and st2.PRODUCT_NAME like '%" + Properties.Settings.Default.ProductType + "%' "
+ " and st1.test_id = st2.test_id "
+ " group by st1.test_id"
+ ") as tab1) as t2_1"
+ " where t1_1.num = t2_1.maxNum "
+ " ))";
DataTable dt = _db.GetDataTable(sqlCmd);
int TotalNumber = Properties.Settings.Default.DefaultTestDataUpLimit;
if (dt.Rows.Count < TotalNumber)
{
foreach (DataRow row in dt.Rows)
{
types.Add(long.Parse(row["TEST_ID"].ToString()));
}
}
else
{
for (int i = 0; i < dt.Rows.Count; i += dt.Rows.Count / TotalNumber)
{
types.Add(long.Parse(dt.Rows[i]["TEST_ID"].ToString()));
}
}
dt.Dispose();
this.Invoke(new Action(delegate()
{
this.chart1.Annotations.Clear();
this.chart1.Titles.Clear();
this.chart1.ChartAreas.Clear();
this.chart1.Series.Clear();
this.chart1.Legends.Clear();
this.chart1.ChartAreas.Add("测试项时间分布");
this.chart1.Legends.Add("测试项时间分布");
this.chart1.Legends[0].BackColor = SystemColors.AppWorkspace;
this.chart1.ChartAreas[0].BackColor = SystemColors.AppWorkspace;
this.chart1.Titles.Add("测试项时间分布");
this.chart1.Titles[0].Docking = System.Windows.Forms.DataVisualization.Charting.Docking.Bottom;
}));
this.Invoke(new Action(delegate()
{
this.chart1.Series.Add("TotalTime");
this.chart1.Series["TotalTime"].ChartType = (SeriesChartType)Enum.Parse(typeof(SeriesChartType), Properties.Settings.Default.DefaultChartType);
}));
sqlCmd = "select distinct(ITEM_NAME) from TEST_TIME_DISTRIBUTION t1,"
+ "test_results t2 "
+ "where t2.test_time >= "
+ "#" + Properties.Settings.Default.DefaultDateTimeStart.ToString("yyyy-MM-dd HH:mm:ss") + "#"
+ "and t2.test_time < "
+ "#" + Properties.Settings.Default.DefaultDateTimeEnd.ToString("yyyy-MM-dd HH:mm:ss") + "#"
+ "and t1.test_id = t2.test_id "
+ "and t2.STATION like '%" + Properties.Settings.Default.DefaultTestBench + "%' "
+ "and t2.PRODUCT_NAME like '%" + Properties.Settings.Default.ProductType + "%' "
+ "and t1.item_name <> 'TotalTime' ";
dt = _db.GetDataTable(sqlCmd);
Dictionary <string, double> testValues = new Dictionary <string, double>();
testValues.Add("TotalTime", 0);
foreach (DataRow row in dt.Rows)
{
this.Invoke(new Action(delegate()
{
testValues.Add(row[0].ToString().Trim(), 0);
this.chart1.Series.Add(row[0].ToString().Trim());
this.chart1.Series[row[0].ToString().Trim()].Color = NextColor();
this.chart1.Series[row[0].ToString().Trim()].ChartType = (SeriesChartType)Enum.Parse(typeof(SeriesChartType), Properties.Settings.Default.DefaultChartType);
}));
}
dt.Dispose();
DateTime timeValidFirst, timeValidLast;
sqlCmd = "select * from TEST_TIME_DISTRIBUTION t1,"
+ "test_results t2 "
+ "where t2.test_time >= "
+ "#" + Properties.Settings.Default.DefaultDateTimeStart.ToString("yyyy-MM-dd HH:mm:ss") + "#"
+ "and t2.test_time < "
+ "#" + Properties.Settings.Default.DefaultDateTimeEnd.ToString("yyyy-MM-dd HH:mm:ss") + "#"
+ "and t1.test_id = t2.test_id "
+ "and t2.STATION like '%" + Properties.Settings.Default.DefaultTestBench + "%' "
+ "and t2.PRODUCT_NAME like '%" + Properties.Settings.Default.ProductType + "%' "
+ "order by t2.test_time";
dt = _db.GetDataTable(sqlCmd);
timeValidFirst = (DateTime)dt.Rows[0]["TEST_TIME"];
timeValidLast = (DateTime)dt.Rows[dt.Rows.Count - 1]["TEST_TIME"];
double lastTimeLong = (double)dt.Rows[dt.Rows.Count - 1]["USED_TIME"];
TimeSpan lastTimeSpan = new TimeSpan((long)(lastTimeLong * 60 * 60 * 1000 * 1000 * 10));
timeValidLast = timeValidLast + lastTimeSpan;
this.Invoke(new Action(() =>
{
this.chart1.Titles[0].Text += string.Format(" 有效时长({0} -- {1}) ({2}小时)",
timeValidFirst.ToString("yyyy-MM-dd HH:mm:ss"),
timeValidLast.ToString("yyyy-MM-dd HH:mm:ss"),
(timeValidLast - timeValidFirst).TotalHours.ToString("0.0000"));
}));
foreach (int tt in types)
{
sqlCmd = "select t1.* from TEST_TIME_DISTRIBUTION t1,"
+ "test_results t2 "
+ "where t1.test_id = ? "
+ "and t1.test_id = t2.test_id "
+ "and t2.STATION like '%" + Properties.Settings.Default.DefaultTestBench + "%' "
+ "and t2.PRODUCT_NAME like '%" + Properties.Settings.Default.ProductType + "%' ";
using (OleDbCommand odc = new OleDbCommand(sqlCmd, _db.Conn))
{
odc.Parameters.Add("@1", OleDbType.Integer);
odc.Parameters[0].Value = tt;
OleDbDataAdapter oda = new OleDbDataAdapter(odc);
dt = new DataTable("DISTRIBUTION");
oda.Fill(dt);
oda.Dispose();
var totalRow = dt.AsEnumerable().First((row) =>
{
if (row["ITEM_NAME"].ToString().Trim() == "TotalTime")
{
return(true);
}
return(false);
});
double totalTestTime = double.Parse(totalRow["USED_TIME"].ToString());
if (totalTestTime < Properties.Settings.Default.DefaultMinTestTime ||
totalTestTime > Properties.Settings.Default.DefaultMaxTestTime)
{
continue;
}
this.Invoke(new Action(delegate()
{
foreach (Series se in this.chart1.Series)
{
se.Points.Add(0);
}
}));
foreach (DataRow row in dt.Rows)
{
string typeName = row["ITEM_NAME"].ToString().Trim();
this.Invoke(new Action(delegate()
{
this.chart1.Series[typeName].Points[
this.chart1.Series[typeName].Points.Count - 1].SetValueY(double.Parse(row["USED_TIME"].ToString()));
this.chart1.Series[typeName].Points[
this.chart1.Series[typeName].Points.Count - 1].BorderWidth = 5;
testValues[typeName] += double.Parse(row["USED_TIME"].ToString()) / totalTestTime;
if (Properties.Settings.Default.DefaultShowValues)
{
this.chart1.Series[typeName].Points[
this.chart1.Series[typeName].Points.Count - 1].Label = row["USED_TIME"].ToString();
}
}));
}
foreach (Series se in chart1.Series)
{
this.Invoke(new Action(() =>
{
se.LegendText = string.Format("{0} ({1}%)"
, se.Name
, (testValues[se.Name] / se.Points.Count() * 100).ToString("0.00"));
}));
}
}
}
#region 照时间的长短对图表进行排序
this.Invoke(new Action(() =>
{
var newSe = chart1.Series.OrderByDescending((se) =>
{
int sPos, nPos;
sPos = se.LegendText.LastIndexOf('(') + 1;
nPos = se.LegendText.LastIndexOf('%') - sPos;
double val = 0;
if (sPos <= 0 || nPos <= 0)
{
return(val);
}
double.TryParse(se.LegendText.Substring(sPos, nPos), out val);
return(val);
});
foreach (var s in newSe.ToArray())
{
_bkSeries.Add(s);
}
chart1.Series.Clear();
foreach (var s in _bkSeries)
{
chart1.Series.Add(s);
}
}));
#endregion 照时间的长短对图表进行排序
#region 过滤图表选项
try
{
this.Invoke(new Action(() =>
{
Filter.Exec(ref _bkSeries);
chart1.Series.Clear();
foreach (var s in _bkSeries)
{
chart1.Series.Add(s);
}
}));
}
catch (Exception exp)
{
this.Invoke(new Action(() =>
{
MessageBox.Show(this, exp.ToString());
}));
}
#endregion 过滤图标选项
#region 添加图例时间信息显示
this.Invoke(new Action(() =>
{
foreach (Series se in chart1.Series)
{
double avg = 0;
foreach (DataPoint pt in se.Points)
{
avg += pt.YValues[0] / se.Points.Count();
}
se.LegendText += "[" + avg.ToString("0.0000") + "小时]";
}
}));
#endregion 添加图例时间信息显示
AddMenuItems();
}//End RefreshChart
/// <summary>
/// 将数据导出至Excel文件
/// </summary>
/// <param name="Table">DataTable对象</param>
/// <param name="Columns">要导出的数据列集合</param>
/// <param name="ExcelFilePath">Excel文件路径</param>
public static bool OutputToExcel(DataTable Table, ArrayList Columns, string ExcelFilePath)
{
if (File.Exists(ExcelFilePath))
{
throw new Exception("该文件已经存在！");
}
//如果数据列数大于表的列数，取数据表的所有列
if (Columns.Count > Table.Columns.Count)
{
for (int s = Table.Columns.Count + 1; s <= Columns.Count; s++)
{
Columns.RemoveAt(s); //移除数据表列数后的所有列
}
}
//遍历所有的数据列，如果有数据列的数据类型不是 DataColumn，则将它移除
DataColumn column = new DataColumn();
for (int j = 0; j < Columns.Count; j++)
{
try
{
column = (DataColumn)Columns[j];
}
catch (Exception)
{
Columns.RemoveAt(j);
}
}
if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))
{
Table.TableName = "Sheet1";
}
//数据表的列数
int ColCount = Columns.Count;
//创建参数
OleDbParameter[] para = new OleDbParameter[ColCount];
//创建表结构的SQL语句
string TableStructStr = @"Create Table " + Table.TableName + "(";
//连接字符串
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(connString);
//创建表结构
OleDbCommand objCmd = new OleDbCommand();
//数据类型集合
ArrayList DataTypeList = new ArrayList();
DataTypeList.Add("System.Decimal");
DataTypeList.Add("System.Double");
DataTypeList.Add("System.Int16");
DataTypeList.Add("System.Int32");
DataTypeList.Add("System.Int64");
DataTypeList.Add("System.Single");
DataColumn col = new DataColumn();
//遍历数据表的所有列，用于创建表结构
for (int k = 0; k < ColCount; k++)
{
col = (DataColumn)Columns[k];
//列的数据类型是数字型
if (DataTypeList.IndexOf(col.DataType.ToString().Trim()) >= 0)
{
para[k] = new OleDbParameter("@" + col.Caption.Trim(), OleDbType.Double);
objCmd.Parameters.Add(para[k]);
//如果是最后一列
if (k + 1 == ColCount)
{
TableStructStr += col.Caption.Trim() + " Double)";
}
else
{
TableStructStr += col.Caption.Trim() + " Double,";
}
}
else
{
para[k] = new OleDbParameter("@" + col.Caption.Trim(), OleDbType.VarChar);
objCmd.Parameters.Add(para[k]);
//如果是最后一列
if (k + 1 == ColCount)
{
TableStructStr += col.Caption.Trim() + " VarChar)";
}
else
{
TableStructStr += col.Caption.Trim() + " VarChar,";
}
}
}
//创建Excel文件及文件结构
try
{
objCmd.Connection = objConn;
objCmd.CommandText = TableStructStr;
if (objConn.State == ConnectionState.Closed)
{
objConn.Open();
}
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
throw exp;
}
//插入记录的SQL语句
string InsertSql_1 = "Insert into " + Table.TableName + " (";
string InsertSql_2 = " Values (";
string InsertSql = "";
//遍历所有列，用于插入记录，在此创建插入记录的SQL语句
for (int colID = 0; colID < ColCount; colID++)
{
if (colID + 1 == ColCount) //最后一列
{
InsertSql_1 += Columns[colID].ToString().Trim() + ")";
InsertSql_2 += "@" + Columns[colID].ToString().Trim() + ")";
}
else
{
InsertSql_1 += Columns[colID].ToString().Trim() + ",";
InsertSql_2 += "@" + Columns[colID].ToString().Trim() + ",";
}
}
InsertSql = InsertSql_1 + InsertSql_2;
//遍历数据表的所有数据行
DataColumn DataCol = new DataColumn();
for (int rowID = 0; rowID < Table.Rows.Count; rowID++)
{
for (int colID = 0; colID < ColCount; colID++)
{
//因为列不连续，所以在取得单元格时不能用行列编号，列需得用列的名称
DataCol = (DataColumn)Columns[colID];
if (para[colID].DbType == DbType.Double && Table.Rows[rowID][DataCol.Caption].ToString().Trim() == "")
{
para[colID].Value = 0;
}
else
{
para[colID].Value = Table.Rows[rowID][DataCol.Caption].ToString().Trim();
}
}
try
{
objCmd.CommandText = InsertSql;
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
string str = exp.Message;
}
}
try
{
if (objConn.State == ConnectionState.Open)
{
objConn.Close();
}
}
catch (Exception exp)
{
throw exp;
}
return(true);
}
public int connectRegUG(UnderGrad x)
{
OleDbConnection connection = new OleDbConnection();
try
{
connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
Jet OLEDB:Database Password=MyDbPassword; Data Source=C:\Users\franc_000\Documents\College\StudentRegfinal\StudentReg\studentregdb.accdb; Persist Security Info = false; ";
connection.Open();
var cmd = new OleDbCommand("INSERT INTO Student ([Id],[Firstname], [Lastname] ,[Email] , [Address], [Subject], [intCAOdata], [doubleDegreegrade]) VALUES (@a, @b, @c, @d, @e, @f, @g, @h)");
cmd.Connection = connection;
cmd.Parameters.AddRange(new[] {
new OleDbParameter("@a", x.Id),
new OleDbParameter("@b", x.FirstName),
new OleDbParameter("@c", x.LastName),
new OleDbParameter("@d", x.Email), ////cant find reference to correct variables
new OleDbParameter("@e", x.Address),
new OleDbParameter("@f", x.Subject),
new OleDbParameter("@g", x.CAO),
new OleDbParameter("@h", "")
});
cmd.ExecuteNonQuery();
Console.WriteLine("Student registered successfully.");
}
catch (Exception ex)
{
Console.WriteLine("Error " + ex);
}
connection.Close();
return 0;
}
public int Fill(DatabaseSet.工作單DataTable table, string 單號, int dateType, DateTime from, DateTime to,int doneOrNot)
{
單號 = 單號.Replace("'", "''");
//客戶名稱 = 客戶名稱.Replace("'", "''");
System.Text.StringBuilder sb = new System.Text.StringBuilder();
sb.Append("SELECT * FROM 工作單 ");
System.Collections.Generic.List<string> whereCond = new System.Collections.Generic.List<string>();
if (單號.Trim() != string.Empty)
whereCond.Add("單號 LIKE '%" + 單號 + "%'");
//if (客戶名稱.Trim() != string.Empty)
// whereCond.Add("客戶名稱 LIKE'%" + 客戶名稱 + "%'");
if (dateType > 0)
{
if (dateType == 1)
{
whereCond.Add("單據日期 >= #" + from.ToString("yyyy/MM/dd") + "#");
whereCond.Add("單據日期 <= #" + to.ToString("yyyy/MM/dd") + "#");
}
else
{
whereCond.Add("實際完成日 >= #" + from.ToString("yyyy/MM/dd") + "#");
whereCond.Add("實際完成日 <= #" + to.ToString("yyyy/MM/dd") + "#");
}
}
if (doneOrNot > 0)
{
if (doneOrNot == 1)
{
whereCond.Add("實際完成日 IS NOT NULL");
}
else
{
whereCond.Add("實際完成日 IS NULL");
}
}
if (whereCond.Count > 0)
{
string whereStr = string.Join(" AND ", whereCond.ToArray());
sb.Append(" WHERE " + whereStr);
}
OleDbCommand cmd = new OleDbCommand(sb.ToString());
cmd.Connection = this.Connection;
this.Adapter.SelectCommand = cmd;
return this.Adapter.Fill(table);
}
public FuncStatus DataBind(BindOperator _bp)
{
bool _tf = true;
int i;
OleDbConnection _conn = new OleDbConnection(Prument.Properties.Settings.Default.Properties["dataConnectionString"].DefaultValue.ToString());
OleDbCommand _cmd = new OleDbCommand();
OleDbDataReader _dr;
_cmd.Connection = _conn;
try
{
_conn.Open();
if (_bp == BindOperator.Update) {
_cmd.CommandText = "delete from Images where pID = " + this.pID;
_cmd.ExecuteNonQuery();
}
if (_bp == BindOperator.Insert || _bp == BindOperator.Update)
{
i = 0;
while (i < _max)
{
_cmd.CommandText = "insert into Images (Path,pID) values ('" + this.Path[i] + "'," + this.pID + ")";
_cmd.ExecuteNonQuery();
i++;
}
}
_cmd.CommandText = "select ID,Path,pID from Images where pID=" + this.pID;
_dr = _cmd.ExecuteReader();
i = 0;
while (_dr.Read())
{
this.ID[i] = _dr.GetInt32(0);
this.Path[i] = _dr.GetValue(1).ToString();
this.pID = _dr.GetInt32(2);
i++;
}
}
catch (Exception ex) {
_tf = false;
Exception _ex = new Exception("ORM.Images:" + ex.Message);
throw _ex;
}
finally
{
_conn.Close();
}
return _tf == false ? FuncStatus.Fail : FuncStatus.Success;
}
public void execute(string query)
{
OleDbTransaction trans = this.conn.BeginTransaction();
OleDbCommand comm = new OleDbCommand();
comm.Connection = this.conn;
comm.CommandText = query;
comm.Transaction = trans;
comm.ExecuteNonQuery();
trans.Commit();
}
public static int UpdateObject(VariableHydroPlant dataObject)
{
bool isNew = false;
string query = string.Format("SELECT Recurso " +
"FROM {0} " +
"WHERE Id = {1}", table, dataObject.Id);
OleDbDataReader reader = DataBaseManager.ReadData(query);
if (!reader.Read())
{
query = string.Format("INSERT INTO {0}(Recurso, Embalse, Segmento, Volumen, FactorConversion, GeneracionMaxima, Escenario) " +
"VALUES(@Name, @Reservoir, @Segment, @Level, @ProductionFactor, @Max, @Case)", table);
isNew = true;
}
else
{
query = string.Format("UPDATE {0} SET " +
"Recurso = @Name, " +
"Embalse = @Reservoir, " +
"Segmento = @Segment, " +
"Volumen = @Level, " +
"FactorConversion = @ProductionFactor, " +
"GeneracionMaxima = @Max, " +
"escenario = @Case " +
"WHERE Id = @Id", table);
}
DataBaseManager.DbConnection.Close();
using (OleDbCommand command = new OleDbCommand(query, DataBaseManager.DbConnection))
{
command.Parameters.Add("@Name", OleDbType.VarChar);
command.Parameters.Add("@Reservoir", OleDbType.VarChar);
command.Parameters.Add("@Segment", OleDbType.Numeric);
command.Parameters.Add("@Level", OleDbType.Numeric);
command.Parameters.Add("@ProductionFactor", OleDbType.Numeric);
command.Parameters.Add("@Max", OleDbType.Numeric);
command.Parameters.Add("@Case", OleDbType.Numeric);
command.Parameters.Add("@Id", OleDbType.Numeric);
DataBaseManager.DbConnection.Open();
command.Parameters["@Name"].Value = dataObject.Name;
command.Parameters["@Reservoir"].Value = dataObject.Reservoir;
command.Parameters["@Segment"].Value = dataObject.Segment;
command.Parameters["@Level"].Value = dataObject.Level;
command.Parameters["@ProductionFactor"].Value = dataObject.ProductionFactor;
command.Parameters["@Max"].Value = dataObject.Max;
command.Parameters["@Case"].Value = dataObject.Case;
command.Parameters["@Id"].Value = dataObject.Id;
try
{
int rowsAffected = command.ExecuteNonQuery();
}
catch
{
DataBaseManager.DbConnection.Close();
throw;
}
DataBaseManager.DbConnection.Close();
}
if (isNew)
{
int id;
query = string.Format("SELECT Max(Id) FROM {0}", table);
reader = DataBaseManager.ReadData(query);
reader.Read();
id = Convert.ToInt32(reader.GetValue(0));
DataBaseManager.DbConnection.Close();
return(id);
}
else
{
return(-1);
}
}
public DataSet fetch(string query)
{
OleDbTransaction trans = this.conn.BeginTransaction();
OleDbCommand comm = new OleDbCommand();
comm.CommandText = query;
comm.Connection = this.conn;
comm.Transaction = trans;
OleDbDataAdapter adapter = new OleDbDataAdapter();
adapter.SelectCommand = comm;
DataSet ds = new DataSet();
adapter.Fill(ds, "tbl_0");
trans.Commit();
return ds;
}
public List <ImportViewModel> LoadDataFromExcel(string fileLocation)
{
List <ImportViewModel> modelList = new List <ImportViewModel>();
OleDbConnection oledbConn = new OleDbConnection();
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter oleda = new OleDbDataAdapter();
DataSet ds = new DataSet();
int totalRowsCount = 0;
string fileExtension = System.IO.Path.GetExtension(fileLocation);
//need to pass relative path after deploying on server
//string path = Request.Files["file"].FileName; //System.IO.Path.GetFullPath(Server.MapPath("~/InformationNew.xlsx"));
/*connection string to work with excel file. HDR=Yes - indicates
* that the first row contains columnnames, not data. HDR=No - indicates
* the opposite. "IMEX=1;" tells the driver to always read "intermixed"
* (numbers, dates, strings etc) data columns as text.
* Note that this option might affect excel sheet write access negative. */
using (OleDbConnection connection = new OleDbConnection())
{
if (Path.GetExtension(fileExtension) == ".xls")
{
connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
else if (Path.GetExtension(fileExtension) == ".xlsx")
{
connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;';";
}
try
{
connection.Open();
}
catch (Exception ex)
{
// MessageBox.Show("Error: " + ex.Message);
}
//string sql = "SELECT [location],[rack_shelf],[dc_location],[customer],[serialnumber],[model],[use_state],[localname],[asset_tag] FROM [device$]";
//string sql = "SELECT * FROM [device$]";
string sql = ConfigurationManager.AppSettings["SQL_Statement"];
if (sql == null)
{
throw new Exception(String.Format("Could not find setting '{0}',", "SQL statement"));
}
using (OleDbCommand command = new OleDbCommand(sql, connection))
{
try
{
command.CommandType = CommandType.Text;
oleda = new OleDbDataAdapter(command);
oleda.Fill(ds);
DataTable table = ds.Tables[0];
totalRowsCount = table.Rows.Count;
HttpContext.Current.Session.Add("ErrorReadingExcel", null);
if (totalRowsCount > 0)
{
int i = 0;
foreach (DataRow row in table.Rows) // Loop over the rows.
{
ImportViewModel model = new ImportViewModel();
for (int j = 0; j < row.ItemArray.Count(); j++)
{
//device.SelectedRowId = row.Table.Rows[i]["location"].ToString();
model.RowNumber = i.ToString();
model.Location = row.Table.Rows[i]["location"].ToString();
model.RackShelf = row.Table.Rows[i]["rack_shelf"].ToString();
model.DCLocation = row.Table.Rows[i]["dc_location"].ToString();
model.Customer = row.Table.Rows[i]["customer"].ToString();
model.SerialNumber = row.Table.Rows[i]["serialnumber"].ToString();
model.Model = row.Table.Rows[i]["model"].ToString();
model.UseState = row.Table.Rows[i]["use_state"].ToString();
model.LocalName = row.Table.Rows[i]["localname"].ToString();
model.AssetTag = row.Table.Rows[i]["asset_tag"].ToString();
modelList.Add(model);
break;
}
i++;
}
}
}
catch (Exception ex)
{
if (ex.Message.Contains("is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long."))
{
HttpContext.Current.Session.Add("ErrorReadingExcel", "Its not a valid file.");
}
}
}
}
return(modelList);
}
private void button4_Click(object sender, EventArgs e)
{
String connection = "Provider=OraOLEDB.Oracle;Data Source=localhost;User Id=system;Password=system;OLEDB.NET=True";
OleDbConnection obj3 = new OleDbConnection(connection);
String query1 = "select max_books from reader where rid = '" + textBox4.Text + "'";
String query2 = "select count(rid) from current_borrowings where rid='" + textBox4.Text + "'";
OleDbCommand cm4 = new OleDbCommand(query1, obj3);
obj3.Open();
OleDbDataReader rd = cm4.ExecuteReader();
Int64 Max_books = 0;
while (rd.Read())
{
Max_books = int.Parse(rd[0].ToString());
}
obj3.Close();
OleDbConnection obj4 = new OleDbConnection(connection);
OleDbCommand cm5 = new OleDbCommand(query2, obj4);
obj4.Open();
OleDbDataReader rd1 = cm5.ExecuteReader();
Int64 current_taken = 0;
while (rd1.Read())
{
current_taken = int.Parse(rd1[0].ToString());
}
obj4.Close();
if (current_taken + 1 > Max_books)
{
string error_string = "The Reader has exceeded its maximum books limit";
MessageBox.Show(error_string);
comboBox1.ResetText();
textBox2.Clear();
textBox4.Clear();
}
else
{
OleDbConnection obj5 = new OleDbConnection(connection);
obj5.Open();
string query3 = "Select isbn from book where Title='" + comboBox1.Text + "'";
OleDbCommand cm6 = new OleDbCommand(query3, obj5);
OleDbDataReader rd2 = cm6.ExecuteReader();
String isbn = string.Empty;
while (rd2.Read())
{
isbn = rd2[0].ToString();
}
Int64 trans = Int64.Parse(textBox1.Text);
Int64 copy = Int64.Parse(textBox2.Text);
String query4 = "insert into borrowed_by(Transaction_no, isbn, copy_no, rid,return) values(" + trans + ",'" + isbn + "'," + copy + ",'" + textBox4.Text + "', NULL)";
try
{
OleDbCommand cm7 = new OleDbCommand(query4, obj5);
cm7.ExecuteNonQuery();
MessageBox.Show("Successful");
textBox1.Clear();
textBox2.Clear();
textBox4.Clear();
}
catch (Exception ee)
{
MessageBox.Show("Error...");
}
obj5.Close();
}
}
private void RechercheVendeur_Load(object sender, EventArgs e)
{
if (textBox_Identidiant.Text != "")
{
listView1_vendeurs.Items.Clear();
string ChaineBd = "Provider=SQLOLEDB;Data Source=INFO-joyeux;Initial Catalog=IMMOBILLY_JACKYTEAM;Persist Security Info=True; Integrated Security=sspi;";
OleDbConnection dbConnection = new OleDbConnection(ChaineBd);
dbConnection.Open();
string sqlS1 = "Select v.Num_Client, v.Nom_Client, v.Prénom_Client, v.Adresse, v.Téléphone, v.E_mail, v.adresse, vi.nom_ville, vi.code_postal";
string sqlF1 = " from Vendeur v left join ville vi on vi.code_ville = v.code_ville";
string sqlW1 = " where v.Num_Client LIKE '" + textBox_Identidiant.Text + "%' ";
string sql = sqlS1 + sqlF1 + sqlW1;
OleDbCommand cmd = new OleDbCommand(sql, dbConnection);
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string[] ligne = { reader.GetInt16(0).ToString(), reader.GetString(1), reader.GetString(2) };
ListViewItem lvi = new ListViewItem(ligne);
listView1_vendeurs.Items.Add(lvi);
}
reader.Close();
}
else
{
if (textBox_Nom.Text != "")
{
listView1_vendeurs.Items.Clear();
string ChaineBd = "Provider=SQLOLEDB;Data Source=INFO-joyeux;Initial Catalog=IMMOBILLY_JACKYTEAM;Persist Security Info=True; Integrated Security=sspi;";
OleDbConnection dbConnection = new OleDbConnection(ChaineBd);
dbConnection.Open();
string sqlS1 = "Select v.Num_Client, v.Nom_Client, v.Prénom_Client, v.Adresse, v.Téléphone, v.E_mail, v.adresse, vi.nom_ville, vi.code_postal";
string sqlF1 = " from Vendeur v left join ville vi on vi.code_ville = v.code_ville";
string sqlW1 = " where v.Nom_Client LIKE '" + textBox_Nom.Text + "%' ";
string sql = sqlS1 + sqlF1 + sqlW1;
OleDbCommand cmd = new OleDbCommand(sql, dbConnection);
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string[] ligne = { reader.GetInt16(0).ToString(), reader.GetString(1), reader.GetString(2) };
ListViewItem lvi = new ListViewItem(ligne);
listView1_vendeurs.Items.Add(lvi);
}
reader.Close();
}
else
{
listView1_vendeurs.Items.Clear();
string ChaineBd = "Provider=SQLOLEDB;Data Source=INFO-joyeux;Initial Catalog=IMMOBILLY_JACKYTEAM;Persist Security Info=True; Integrated Security=sspi;";
OleDbConnection dbConnection = new OleDbConnection(ChaineBd);
dbConnection.Open();
string sqlS1 = "Select v.Num_Client, v.Nom_Client, v.Prénom_Client, v.Adresse, v.Téléphone, v.E_mail, v.adresse, vi.nom_ville, vi.code_postal";
string sqlF1 = " from Vendeur v left join ville vi on vi.code_ville = v.code_ville";
string sql = sqlS1 + sqlF1;
OleDbCommand cmd = new OleDbCommand(sql, dbConnection);
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string[] ligne = { reader.GetInt16(0).ToString(), reader.GetString(1), reader.GetString(2) };
ListViewItem lvi = new ListViewItem(ligne);
listView1_vendeurs.Items.Add(lvi);
}
reader.Close();
}
}
}
//
// Кнопка применения редактирования
//
private void button6_Click(object sender, EventArgs e)
{
tmpBool = false; //пеменная для проверки "можно ли создать/отредактировать данное поле"
//
// Исходные уникальные ключи
//
queryFor2("Select * From dayOfTheWeek Where dayoftheweek='" + Convert.ToString(dataGridView1.CurrentRow.Cells[0].Value) + "'");
int dayOfTheWeekSource = Convert.ToInt32(dataGridView2[0, 0].Value);
queryFor2("Select * From lessons Where name_of_lesson='" + Convert.ToString(dataGridView1.CurrentRow.Cells[1].Value) + "'");
int lessonsSource = Convert.ToInt32(dataGridView2[0, 0].Value);
queryFor2("Select * From cabinet Where number_of_cab=" + Convert.ToString(dataGridView1.CurrentRow.Cells[3].Value) + "");
int cabinetSource = Convert.ToInt32(dataGridView2[0, 0].Value);
queryFor2("Select * From lessonTime Where start_time='" + Convert.ToString(dataGridView1.CurrentRow.Cells[4].Value) + "'");
int startSource = Convert.ToInt32(dataGridView2[0, 0].Value);
//
// Получаем уникальные ключи выбранных полей, на которые будем менять
//
queryFor2("Select * From dayOfTheWeek Where dayoftheweek='" + comboBox1.SelectedItem.ToString() + "'");
int dayOfTheWeek = Convert.ToInt32(dataGridView2[0, 0].Value);
queryFor2("Select * From lessons Where name_of_lesson='" + comboBox2.SelectedItem.ToString() + "'");
int lessons = Convert.ToInt32(dataGridView2[0, 0].Value);
queryFor2("Select * From cabinet Where number_of_cab=" + comboBox3.SelectedItem.ToString() + "");
int cabinet = Convert.ToInt32(dataGridView2[0, 0].Value);
queryFor2("Select * From lessonTime Where start_time='" + comboBox4.SelectedItem.ToString() + "'");
int start = Convert.ToInt32(dataGridView2[0, 0].Value);
queryFor2("Select * From groupp Where name_of_group='" + comboBoxGr.SelectedItem.ToString() + "'");
int name_of_group = Convert.ToInt32(dataGridView2[0, 0].Value);
string findID = "SELECT id_schedule FROM schedule WHERE (id_groupp=" + name_of_group.ToString() + " AND id_dayOfTheWeek=" + dayOfTheWeekSource.ToString() + " AND id_lessonstime=" + startSource.ToString() + " AND id_lessons = " + lessonsSource.ToString() + " AND id_cabinet=" + cabinetSource.ToString() + ")";
queryFor2(findID);
String ID = Convert.ToString(dataGridView2[0, 0].Value);
textBox1.Text = ID;
//
// Для вызова метода проверки "свободна ли аудитория в выбраное время в выбранный день недели"
//
string s = "SELECT count(*) FROM dayOfTheWeek INNER JOIN(lessonTime INNER JOIN(cabinet INNER JOIN schedule ON cabinet.id_cab = schedule.id_cabinet) ON lessonTime.id_lessTime = schedule.id_lessonstime) ON dayOfTheWeek.id_days = schedule.id_dayOfTheWeek WHERE(((lessonTime.start_time) ='" + comboBox4.SelectedItem.ToString() + "') AND((cabinet.number_of_cab) =" + comboBox3.SelectedItem.ToString() + ") AND((dayOfTheWeek.dayoftheweek) ='" + comboBox1.SelectedItem.ToString() + "'))";
string mbS = "В этом кабинете уже есть занятие в это время! ";
NewMethod(s, mbS);
//
// Проверить нет ли такой записи в рассписании
//
string s2 = "Select count(*) From schedule WHERE (id_groupp=" + name_of_group.ToString() + " AND id_dayOfTheWeek=" + dayOfTheWeek.ToString() + " AND id_lessonstime=" + start.ToString() + " AND id_lessons=" + lessons.ToString() + " AND id_cabinet=" + cabinet.ToString() + ")";
string mbS2 = "Такая запись в рассписании присутствует!";
NewMethod(s2, mbS2);
//
// Проверить нет ли этого предмета в это время у другой группы
//
string s3 = "Select count(*) From schedule WHERE (id_lessons=" + lessons.ToString() + " AND id_lessonstime=" + start.ToString() + " AND id_dayOfTheWeek=" + dayOfTheWeek.ToString() + ")";
string mbS3 = "Этот предмет у другой группы в это время!";
NewMethod(s3, mbS3);
try
{
if (!tmpBool)
{
Con.Open();
string query1 = "UPDATE schedule SET [id_groupp]=" + name_of_group.ToString()
+ ", [id_dayOfTheWeek]=" + dayOfTheWeek.ToString()
+ ", [id_lessonstime]=" + start.ToString()
+ ", [id_lessons]=" + lessons.ToString()
+ ", [id_cabinet]=" + cabinet.ToString()
+ " WHERE [id_schedule]=" + ID + "";
OleDbCommand com = new OleDbCommand(query1, Con);
com.ExecuteNonQuery();
MessageBox.Show("Запись обновлена!");
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally { if (Con.State == ConnectionState.Open)
{
Con.Close();
}
}
}
public static DataSet ReadExcelOfSheetToGetDataTable(string FilePath, string query, string querypart, string wherequery, int indexNo, string[] sheetNameWord)
{
String findSheet = string.Empty;
//First Tab Connection
OleDbConnection conn = new OleDbConnection();
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter da = new OleDbDataAdapter();
DataSet ds = new DataSet();
//Response.Write(FilePath);
string Extension = Path.GetExtension(FilePath);
string header = "Yes";
string connString = setConnectionStringForExcel(FilePath, Extension, header);
//Response.Write(connString);
//Response.Write(query);
//Create the connection object
conn = new OleDbConnection(connString);
cmd.Connection = conn;
//Open connection
if (conn.State == ConnectionState.Closed) conn.Open();
DataTable Sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//Console.Write(sheetname);
foreach (DataRow dr in Sheets.Rows)
{
string sheetname = dr["TABLE_NAME"].ToString().Replace("'", "");
if (sheetname.Contains("$"))
{
sheetname = sheetname.Replace("$", "");
}
if (!sheetname.Contains("_xlnm"))
{
foreach (string word in sheetNameWord)
{
if (sheetname.Contains(word) == true)
{
findSheet = sheetname;
break;
}
}
}
}
if (findSheet != string.Empty)
{
//Create the command object
da = new OleDbDataAdapter(cmd);
ds = new DataSet();
cmd.CommandText = "SELECT " + query + " From [" + findSheet + "$" + querypart + "]" + wherequery;
cmd.CommandTimeout = 0;
da.SelectCommand = cmd;
da.Fill(ds);
}
//Close OleDbConnection Connection
da.Dispose();
//conn.Dispose();
//conn.Close();
//Close connection
if (conn.State == ConnectionState.Open)
{
conn.Close();
conn.Dispose();
}
RemoveAllNullRowsFromDataTable(ds.Tables[0]);
return ds;
}
//
// Кнопка для создания новой записи
//
private void button12_Click(object sender, EventArgs e)
{
tmpBool = false; //пеменная для проверки "можно ли создать/отредактировать данное поле"
//
// Получаем уникальные ключи выбранных полей
//
queryFor2("Select * From groupp Where name_of_group='" + comboBox5.SelectedItem.ToString() + "'");
int name_of_group = Convert.ToInt32(dataGridView2[0, 0].Value);
queryFor2("Select * From dayOfTheWeek Where dayoftheweek='" + comboBox6.SelectedItem.ToString() + "'");
int dayOfTheWeek = Convert.ToInt32(dataGridView2[0, 0].Value);
queryFor2("Select * From lessons Where name_of_lesson='" + comboBox7.SelectedItem.ToString() + "'");
int lessons = Convert.ToInt32(dataGridView2[0, 0].Value);
queryFor2("Select * From cabinet Where number_of_cab=" + comboBox8.SelectedItem.ToString() + "");
int cabinet = Convert.ToInt32(dataGridView2[0, 0].Value);
queryFor2("Select * From lessonTime Where start_time='" + comboBox9.SelectedItem.ToString() + "'");
int start = Convert.ToInt32(dataGridView2[0, 0].Value);
//
// Проверить нет ли занятия в этом кабинете в это время
//
string s = "SELECT count(*) FROM dayOfTheWeek INNER JOIN(lessonTime INNER JOIN(cabinet INNER JOIN schedule ON cabinet.id_cab = schedule.id_cabinet) ON lessonTime.id_lessTime = schedule.id_lessonstime) ON dayOfTheWeek.id_days = schedule.id_dayOfTheWeek WHERE(((lessonTime.start_time) ='" + comboBox9.SelectedItem.ToString() + "') AND((cabinet.number_of_cab) =" + comboBox8.SelectedItem.ToString() + ") AND((dayOfTheWeek.dayoftheweek) ='" + comboBox6.SelectedItem.ToString() + "'))";
string mbS = "В этом кабинете уже есть занятие в это время! ";
NewMethod(s, mbS);
//
// Проверить нет ли такой записи в рассписании
//
string s2 = "Select count(*) From schedule WHERE (id_groupp=" + name_of_group.ToString() + " AND id_dayOfTheWeek=" + dayOfTheWeek.ToString() + " AND id_lessonstime=" + start.ToString() + " AND id_lessons=" + lessons.ToString() + " AND id_cabinet=" + cabinet.ToString() + ")";
string mbS2 = "Такая запись в рассписании присутствует!";
NewMethod(s2, mbS2);
//
// Проверить нет ли у группы в это время урока в другом кабинете
//
string s4 = "Select count(*) From schedule WHERE (id_groupp=" + name_of_group.ToString() + " AND id_lessonstime=" + start.ToString() + " AND id_dayOfTheWeek=" + dayOfTheWeek.ToString() + ")";
string mbS4 = "У этой группы в это время другой урок!";
NewMethod(s4, mbS4);
try
{
if (!tmpBool)
{
Con.Open();
string query = "INSERT INTO schedule ([id_groupp], [id_dayOfTheWeek], [id_lessonstime], [id_lessons], [id_cabinet]) "
+ "VALUES"
+ "('" + name_of_group.ToString()
+ "', '" + dayOfTheWeek.ToString()
+ "', '" + start.ToString()
+ "', '" + lessons.ToString()
+ "', '" + cabinet.ToString()
+ "')";
OleDbCommand com = new OleDbCommand(query, Con);
com.ExecuteNonQuery();
if (Con.State == ConnectionState.Open)
{
Con.Close();
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally { if (Con.State == ConnectionState.Open)
{
Con.Close();
}
}
}
private void AddButton_Click(object sender, EventArgs e)
{
if (Edit)
{
string query = "UPDATE Orders SET " +
"ID_Dish=@ID_Dish, " +
"ID_Staff=@ID_Staff, " +
"Date_Order=@Date_Order, " +
"ID_Client=@ID_Client, " +
"ID_Type_Order=@ID_Type_Order, " +
"Number_Of_Servings=@Number_Of_Servings, " +
"Lead_Time=@Lead_Time " +
"WHERE ID_Order = " + ID;
if (String.IsNullOrWhiteSpace(CountField.Text))
{
MessageBox.Show("Заполните все поля!", "Ошибка ввода", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
using (OleDbCommand command = new OleDbCommand(query, connection))
{
try
{
connection.Open();
command.Parameters.AddWithValue("@ID_Dish", (int)DishDropdown.SelectedValue);
command.Parameters.AddWithValue("@ID_Staff", (int)StaffDropdown.SelectedValue);
command.Parameters.AddWithValue("@Date_Order", DatePicker.Value.ToShortDateString());
command.Parameters.AddWithValue("@ID_Client", (int)ClientsDropdown.SelectedValue);
command.Parameters.AddWithValue("@ID_Type_Order", (int)TypeOrderDropdown.SelectedValue);
command.Parameters.AddWithValue("@Number_Of_Servings", CountField.Text);
command.Parameters.AddWithValue("@Lead_Time", LeadTimePicker.Value.ToShortTimeString());
command.ExecuteNonQuery();
Control[] cards = FormMain.FM.OrdersPanel.Controls.Find("OrderCard", true);
foreach (Orders c in cards.OfType <Orders>())
{
if (c.ID == ID)
{
c.CountField.Text = CountField.Text;
c.DatePicker.Text = Convert.ToDateTime(DatePicker.Value).ToShortDateString();
c.LeadTimeField.Text = Convert.ToDateTime(LeadTimePicker.Value).ToShortTimeString();
break;
}
}
Close();
MessageBox.Show("Запись успешно изменена!", "Изменение записи", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception)
{
MessageBox.Show("Не удалось изменить запись!", "Ошибка изменения", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
FormMain.FM.RefreshOrder();
}
}
else
{
string query = "INSERT INTO Orders (ID_Dish,ID_Staff, " +
"Date_Order,ID_Client, ID_Type_Order, " +
"Number_Of_Servings, Lead_Time) " +
"VALUES (@ID_Dish,@ID_Staff, @Date_Order, @ID_Client, " +
"@ID_Type_Order, @Number_Of_Servings, @Lead_Time)";
if (String.IsNullOrWhiteSpace(CountField.Text))
{
MessageBox.Show("Заполните все поля!", "Ошибка ввода", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
using (OleDbConnection connection = new OleDbConnection(connectionString))
using (OleDbCommand command = new OleDbCommand(query, connection))
{
try
{
connection.Open();
command.Parameters.AddWithValue("@ID_Dish", (int)DishDropdown.SelectedValue);
command.Parameters.AddWithValue("@ID_Staff", (int)StaffDropdown.SelectedValue);
command.Parameters.AddWithValue("@Date_Order", DatePicker.Value.ToShortDateString());
command.Parameters.AddWithValue("@ID_Client", (int)ClientsDropdown.SelectedValue);
command.Parameters.AddWithValue("@ID_Type_Order", (int)TypeOrderDropdown.SelectedValue);
command.Parameters.AddWithValue("@Number_Of_Servings", CountField.Text);
command.Parameters.AddWithValue("@Lead_Time", LeadTimePicker.Value.ToShortTimeString());
command.ExecuteNonQuery();
command.CommandText = "SELECT @@IDENTITY";
int id = (int)(command.ExecuteScalar());
Control[] c = FormMain.FM.OrdersPanel.Controls.Find("OrderCard", true);
Orders lastCard;
int lastCardIndex;
if (c.Length != 0)
{
lastCard = c[c.Length - 1] as Orders;
lastCardIndex = FormMain.FM.OrdersPanel.Controls.GetChildIndex(lastCard);
}
else
{
lastCardIndex = -1;
}
Orders newCard = new Orders
{
Name = "OrderCard",
Parent = FormMain.FM.OrdersPanel,
ID = id
};
FormMain.FM.OrdersPanel.Controls.SetChildIndex(newCard, lastCardIndex + 1);
MessageBox.Show("Запись успешно добавлена!", "Добавление записи", MessageBoxButtons.OK, MessageBoxIcon.Information);
Close();
}
catch (Exception)
{
MessageBox.Show("Не удалось добавить запись!", "Ошибка добавления", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
}
FormMain.FM.RefreshOrder();
}
}
private void simpleButton1_Click(object sender, EventArgs e)
{
string kullanici = textEdit1.Text;
string sifre = textEdit1.Text;
if (kullanici.Trim() != "" && sifre.Trim() != "")
{
if (baglanti.State == ConnectionState.Closed)
{
baglanti.Open();
}
komut.Connection = baglanti;
komut.CommandText = "SELECT * FROM kullanicilar";
OleDbDataReader okuyucu = komut.ExecuteReader();
while (okuyucu.Read())
{
if (okuyucu["kullanici"].ToString() == kullanici.Trim() && okuyucu["sifre"].ToString() == sifre.Trim())
{
string nYasakli = okuyucu["yasakli"].ToString();
if (nYasakli == "SÜRESİZ")
{
MessageBox.Show("Yönetici tarafından süresiz olarak sistemden uzaklaştırıldınız.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.Exit();
}
else
{
if (nYasakli != "")
{
int yasakli = DateTime.Compare(DateTime.Parse(nYasakli), DateTime.Now);
if (yasakli == 1)
{
MessageBox.Show("Yönetici tarafından " + nYasakli + " tarihine kadar sistemden uzaklaştırıldınız.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error);
Application.Exit();
}
else
{
gAs = Convert.ToBoolean(okuyucu["asyonetici"]);
OleDbCommand komut2 = new OleDbCommand("SELECT * FROM ayarlar WHERE id=1", baglanti);
OleDbDataReader okuyucu2 = komut2.ExecuteReader();
bool sistem = true;
while (okuyucu2.Read())
{
sistem = Boolean.Parse(okuyucu2["sistem"].ToString());
}
if (sistem == false)
{
if (gAs == false)
{
MessageBox.Show("Sistem yönetici tarafından kapatılmıştır.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
gID = Convert.ToInt32(okuyucu["id"]);
gKullanici = kullanici.Trim();
gSifre = sifre.Trim();
gTarih = Convert.ToDateTime(okuyucu["tarih"].ToString());
gYonetici = Convert.ToBoolean(okuyucu["yonetici"].ToString());
MessageBox.Show("Sistem yönetici tarafından kapatılmış, ancak siz yönetici olduğunuz için giriş yapabilirsiniz.", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information);
Form2 form2 = new Form2(gKullanici.Trim(), gSifre.Trim(), gYonetici, gTarih, gID, gAs);
form2.ShowDialog();
}
}
else
{
gID = Convert.ToInt32(okuyucu["id"]);
gKullanici = kullanici.Trim();
gSifre = sifre.Trim();
gTarih = Convert.ToDateTime(okuyucu["tarih"].ToString());
gYonetici = Convert.ToBoolean(okuyucu["yonetici"].ToString());
MessageBox.Show("Giriş işlemi başarıyla tamamlanmıştır.");
Form2 form2 = new Form2(gKullanici.Trim(), gSifre.Trim(), gYonetici, gTarih, gID, gAs);
form2.ShowDialog();
}
}
}
else
{
gAs = Convert.ToBoolean(okuyucu["asyonetici"]);
OleDbCommand komut2 = new OleDbCommand("SELECT * FROM ayarlar WHERE id=1", baglanti);
OleDbDataReader okuyucu2 = komut2.ExecuteReader();
bool sistem = true;
while (okuyucu2.Read())
{
sistem = Boolean.Parse(okuyucu2["sistem"].ToString());
}
if (sistem == false)
{
if (gAs == false)
{
MessageBox.Show("Sistem yönetici tarafından kapatılmıştır.", "Hata", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
else
{
gID = Convert.ToInt32(okuyucu["id"]);
gKullanici = kullanici.Trim();
gSifre = sifre.Trim();
gTarih = Convert.ToDateTime(okuyucu["tarih"].ToString());
gYonetici = Convert.ToBoolean(okuyucu["yonetici"].ToString());
MessageBox.Show("Sistem yönetici tarafından kapatılmış, ancak siz yönetici olduğunuz için giriş yapabilirsiniz.", "Bilgi", MessageBoxButtons.OK, MessageBoxIcon.Information);
Form2 form2 = new Form2(gKullanici.Trim(), gSifre.Trim(), gYonetici, gTarih, gID, gAs);
form2.ShowDialog();
}
}
else
{
gID = Convert.ToInt32(okuyucu["id"]);
gKullanici = kullanici.Trim();
gSifre = sifre.Trim();
gTarih = Convert.ToDateTime(okuyucu["tarih"].ToString());
gYonetici = Convert.ToBoolean(okuyucu["yonetici"].ToString());
MessageBox.Show("Giriş işlemi başarıyla tamamlanmıştır.");
Form2 form2 = new Form2(gKullanici.Trim(), gSifre.Trim(), gYonetici, gTarih, gID, gAs);
form2.ShowDialog();
}
}
}
}
}
baglanti.Close();
}
}
private void button1_Click_1(object sender, EventArgs e)
{
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
string id = textBox1.Text;
string catePrd = comboBox1.SelectedItem.ToString();
string prdName = comboBox2.SelectedItem.ToString();
string table = MainMenu.tableName;
int price = Convert.ToInt32(prdPrice.Text);
int qty = Convert.ToInt32(qtyValue.Value);
int totalPrice = Convert.ToInt32(ttlPrice.Text);
string weighttype = comboBox4.SelectedItem.ToString();
string dateTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
string username = "";
cmd = con.CreateCommand();
if (product == "new")
{
if (productExist(catePrd, prdName))
{
MessageBox.Show("இந்த பொருள் ஏற்கனவே உள்ளது");
return;
}
}
cmd.CommandText = "insert into " + table + "(prdId,cateName,prdName,weightType,qty,price,totalPrice,userName,isDeleted,prdTime) values('" + id + "','" + catePrd + "','" + prdName + "','" + weighttype + "'," + qty + "," + price + "," + totalPrice + ",'" + username + "'," + 0 + ",'" + dateTime + "')";
int result = cmd.ExecuteNonQuery();
if (result > 0)
{
MessageBox.Show("பொருள் சேமிக்கப்பட்டது");
if (product != "old")
{
cmd.CommandText = "update IdRef set " + field + "=" + field + " + 1";
cmd.ExecuteNonQuery();
}
storeStockDetails(cmd, id, prdType, catePrd, prdName, qty);
if (table == "StoreProductDetails")
{
storeProductPrice(cmd, id, catePrd, prdName, price, username);
}
}
}
catch (NullReferenceException)
{
MessageBox.Show("பொருளின் வகை மற்றும் விலையை நிரப்பு");
}
catch (FormatException)
{
MessageBox.Show("பொருளின் விலையை நிரப்பு");
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
//textBox1.Text = "";
if (con.State == ConnectionState.Open)
{
con.Close();
}
cmd.Dispose();
comboBox1.SelectedItem = "தேர்ந்தெடு";
comboBox2.SelectedItem = "தேர்ந்தெடு";
prdPrice.Text = "";
ttlPrice.Text = "";
}
}
private void button2_Click(object sender, EventArgs e)
{
if (selection == "add")
{
mc.conn.Open();
cmd = new OleDbCommand("insert into Vendor Values(@VID,@VName,@VCode,@VCity,@PH1,@PH2,@VAddress,@CPName,@CPPH,@VEmail,@VFax,@VGroup,'Inactive')", mc.conn);
cmd.Parameters.AddWithValue("@VID", textBox12.Text);
cmd.Parameters.AddWithValue("@VName", textBox1.Text);
cmd.Parameters.AddWithValue("@VCode", textBox2.Text);
cmd.Parameters.AddWithValue("@VCity", textBox3.Text);
cmd.Parameters.AddWithValue("@PH1", textBox4.Text);
cmd.Parameters.AddWithValue("@PH2", textBox5.Text);
cmd.Parameters.AddWithValue("@VAddress", textBox6.Text);
cmd.Parameters.AddWithValue("@CPName", textBox7.Text);
cmd.Parameters.AddWithValue("@CPPH", textBox8.Text);
cmd.Parameters.AddWithValue("@VEmail", textBox9.Text);
cmd.Parameters.AddWithValue("@VFax", textBox10.Text);
cmd.Parameters.AddWithValue("@VGroup", comboBox2.Text);
cmd.ExecuteNonQuery();
mc.conn.Close();
MessageBox.Show("Vendor added!");
textBox1.ReadOnly = true;
textBox2.ReadOnly = true;
textBox3.ReadOnly = true;
textBox4.ReadOnly = true;
textBox5.ReadOnly = true;
textBox6.ReadOnly = true;
textBox7.ReadOnly = true;
textBox8.ReadOnly = true;
textBox9.ReadOnly = true;
textBox10.ReadOnly = true;
comboBox2.Enabled = false;
}
else if (selection == "approve")
{
mc.conn.Open();
cmd = new OleDbCommand("update Vendor set VStatus='Active' where VID='" + comboBox1.Text + "';", mc.conn);
cmd.ExecuteNonQuery();
mc.conn.Close();
MessageBox.Show("Vendor activated!");
textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
textBox4.Text = "";
textBox5.Text = "";
textBox6.Text = "";
textBox7.Text = "";
textBox8.Text = "";
textBox9.Text = "";
textBox10.Text = "";
textBox11.Text = "";
textBox12.Text = "";
textBox13.Text = "";
comboBox2.Text = "";
comboBox1.Text = "";
comboBox1.Items.Clear();
mc.conn.Open();
cmd = new OleDbCommand("select VID from Vendor where VStatus='Inactive';", mc.conn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
comboBox1.Items.Add(dr["VID"]);
}
mc.conn.Close();
button2.Enabled = false;
}
else if (selection == "update")
{
mc.conn.Open();
cmd = new OleDbCommand("update Vendor set VName=@VName, VCode=@VCode, VCity=@VCity, PH1=@PH1, PH2=@PH2, VAddress=@VAddress, CPName=@CPName, CPPH=@CPPH, VEmail=@VEmail, VFax=@VFax where VID=@VID;", mc.conn);
cmd.Parameters.AddWithValue("@VName", textBox1.Text);
cmd.Parameters.AddWithValue("@VCode", textBox2.Text);
cmd.Parameters.AddWithValue("@VCity", textBox3.Text);
cmd.Parameters.AddWithValue("@PH1", textBox4.Text);
cmd.Parameters.AddWithValue("@PH2", textBox5.Text);
cmd.Parameters.AddWithValue("@VAddress", textBox6.Text);
cmd.Parameters.AddWithValue("@CPName", textBox7.Text);
cmd.Parameters.AddWithValue("@CPPH", textBox8.Text);
cmd.Parameters.AddWithValue("@VEmail", textBox9.Text);
cmd.Parameters.AddWithValue("@VFax", textBox10.Text);
cmd.Parameters.AddWithValue("@VID", comboBox1.Text);
cmd.ExecuteNonQuery();
mc.conn.Close();
MessageBox.Show("Vendor updated!");
textBox1.Text = "";
textBox2.Text = "";
textBox3.Text = "";
textBox4.Text = "";
textBox5.Text = "";
textBox6.Text = "";
textBox7.Text = "";
textBox8.Text = "";
textBox9.Text = "";
textBox10.Text = "";
textBox11.Text = "";
textBox12.Text = "";
textBox13.Text = "";
comboBox2.Text = "";
comboBox1.Text = "";
}
}
/// <summary>
/// Update Data
/// </summary>
/// <param name="_step">Query Selector</param>
/// <returns>true or false</returns>
public bool UpdateData(int _step)
{
string strQuery = "";
try
{
if (_dbc.gErrors.MsgInit() == false)
{
return(false);
}
OleDbCommand adoCommand = new OleDbCommand();
// Set Connection, Transaction
adoCommand.Connection = _dbc.gOleDbConnection;
adoCommand.Transaction = _dbc.gOleDbTransaction;
switch (_step)
{
case 1:
// Update by Primary Key
strQuery = "UPDATE MFMBFACLYT SET"
+ " LAYOUT_DESC=?, WIDTH=?, HEIGHT=?, CREATE_USER_ID=?, CREATE_TIME=?,"
+ " UPDATE_USER_ID=?, UPDATE_TIME=?"
+ " WHERE FACTORY=?"
+ " AND LAYOUT_ID=?";
// Add Parameters
adoCommand.Parameters.Add("@LAYOUT_DESC", OleDbType.VarChar).Value = LAYOUT_DESC;
adoCommand.Parameters.Add("@WIDTH", OleDbType.Numeric).Value = WIDTH;
adoCommand.Parameters.Add("@HEIGHT", OleDbType.Numeric).Value = HEIGHT;
adoCommand.Parameters.Add("@CREATE_USER_ID", OleDbType.VarChar).Value = CREATE_USER_ID;
adoCommand.Parameters.Add("@CREATE_TIME", OleDbType.VarChar).Value = CREATE_TIME;
adoCommand.Parameters.Add("@UPDATE_USER_ID", OleDbType.VarChar).Value = UPDATE_USER_ID;
adoCommand.Parameters.Add("@UPDATE_TIME", OleDbType.VarChar).Value = UPDATE_TIME;
// Add Parameters for Condition
adoCommand.Parameters.Add("@C_FACTORY", OleDbType.VarChar).Value = FACTORY;
adoCommand.Parameters.Add("@C_LAYOUT_ID", OleDbType.VarChar).Value = LAYOUT_ID;
break;
case 101:
// TODO : User Select Query
break;
default:
// Error Handling
_dbc.gErrors.SqlCode = SQL_CODE.SQL_CASE_ERROR;
return(false);
}
// ExecuteNonQuery();
adoCommand.CommandText = strQuery;
_dbc.gErrors.AddQuery(strQuery, adoCommand.Parameters);
if (adoCommand.ExecuteNonQuery() < 1)
{
// Data Not Found
_dbc.gErrors.SqlCode = SQL_CODE.SQL_NOT_FOUND;
// Dispose
adoCommand.Dispose();
return(false);
}
// Dispose
adoCommand.Dispose();
}
catch (Exception ex)
{
if (ex.GetType().ToString() == "System.Data.OleDb.OleDbException")
{
_dbc.gErrors.SetErrors(((OleDbException)ex).Errors);
}
else
{
_dbc.gErrors.SetErrors(ex);
}
return(false);
}
return(true);
}
private void Vendor_Load(object sender, EventArgs e)
{
mc = new MyConnection();
if (selection == "add")
{
this.Text = "Add Vender";
comboBox1.Visible = false;
textBox13.Visible = false;
comboBox2.Visible = true;
textBox12.Visible = true;
mc.conn.Open();
cmd = new OleDbCommand("select GrpName from CusGroup;", mc.conn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
comboBox2.Items.Add(dr["GrpName"]);
}
mc.conn.Close();
}
else if (selection == "approve")
{
this.Text = "Approve Vendor";
button2.Enabled = false;
button3.Enabled = false;
button2.Text = "Approve";
textBox1.ReadOnly = true;
textBox2.ReadOnly = true;
textBox3.ReadOnly = true;
textBox4.ReadOnly = true;
textBox5.ReadOnly = true;
textBox6.ReadOnly = true;
textBox7.ReadOnly = true;
textBox8.ReadOnly = true;
textBox9.ReadOnly = true;
textBox10.ReadOnly = true;
textBox13.ReadOnly = true;
mc.conn.Open();
cmd = new OleDbCommand("select VID from Vendor where VStatus='Inactive';", mc.conn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
comboBox1.Items.Add(dr["VID"]);
}
mc.conn.Close();
}
else if (selection == "search")
{
this.Text = "Search Vendor";
button1.Visible = false;
button2.Visible = false;
button3.Enabled = false;
comboBox2.Visible = false;
textBox12.Visible = false;
textBox1.ReadOnly = true;
textBox2.ReadOnly = true;
textBox3.ReadOnly = true;
textBox4.ReadOnly = true;
textBox5.ReadOnly = true;
textBox6.ReadOnly = true;
textBox7.ReadOnly = true;
textBox8.ReadOnly = true;
textBox9.ReadOnly = true;
textBox10.ReadOnly = true;
textBox11.ReadOnly = true;
textBox13.ReadOnly = true;
mc.conn.Open();
cmd = new OleDbCommand("select VID from Vendor;", mc.conn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
comboBox1.Items.Add(dr["VID"]);
}
mc.conn.Close();
}
else if (selection == "update")
{
this.Text = "Update Vendor";
button2.Text = "Update";
button3.Enabled = false;
button2.Enabled = false;
comboBox2.Visible = false;
textBox12.Visible = false;
textBox13.ReadOnly = true;
mc.conn.Open();
cmd = new OleDbCommand("select VID from Vendor;", mc.conn);
dr = cmd.ExecuteReader();
while (dr.Read())
{
comboBox1.Items.Add(dr["VID"]);
}
mc.conn.Close();
}
}
public void UploadFile()
{
//the excel file has same the column name you can castmation the Method
string Year = "";
string Month = "";
string Product_Name = "";
string Product_type = "";
string Product_Category = "";
string Source = "";
string Sale_Flow = "";
string Unit = "";
string create_by = "";
string create_time = "";
string Update_by = "";
string Update_tiem = "";
string Product_code = "";
string Volume_Per_Day = "";
OleDbConnection connection = new OleDbConnection();
try
{
string path = string.Concat(("~/Uploaded Folder/"));
string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=No;IMEX=1\";", path);
connection.ConnectionString = excelConnectionString;
connection.Open();
//DataTable dt = null;
using (OleDbCommand cmd = new OleDbCommand("select * from [LS$]", connection))
{
using (OleDbDataReader rdr = cmd.ExecuteReader())
{
int i = 0;
while (rdr.Read())
{
if (i > 0)
{
Year = "";
Month = "";
Product_Name = "";
Product_type = "";
Product_Category = "";
Source = "";
Sale_Flow = "";
Unit = "";
create_by = "";
create_time = "";
Update_by = "";
Update_tiem = "";
Product_code = "";
Volume_Per_Day = "";
if (!rdr.IsDBNull(0))
{
Year = rdr.GetString(0);
}
if (!rdr.IsDBNull(1))
{
Month = rdr.GetString(1);
}
if (!rdr.IsDBNull(2))
{
Product_Name = rdr.GetString(2);
}
if (!rdr.IsDBNull(3))
{
Product_type = rdr.GetString(3);
}
if (!rdr.IsDBNull(4))
{
Product_Category = rdr.GetString(4);
}
if (!rdr.IsDBNull(5))
{
Source = rdr.GetString(5);
}
if (!rdr.IsDBNull(6))
{
Sale_Flow = rdr.GetString(6);
}
if (!rdr.IsDBNull(7))
{
Unit = rdr.GetString(7);
}
if (!rdr.IsDBNull(8))
{
Product_code = rdr.GetString(8);
}
if (!rdr.IsDBNull(9))
{
Volume_Per_Day = rdr.GetString(9);
}
InsertToSql(Year, Month, Product_Name, Product_type, Product_Category, Source, Sale_Flow, Unit, Product_code, Volume_Per_Day, create_by, create_time, Update_by, Update_tiem);
}
i += 1;
}
}
}
}
catch (Exception ex)
{
}
finally
{
connection.Close();
}
}
private void btnSearch_Click(object sender, EventArgs e)
{
string sql = "SELECT * from PROPERTIES ";
bool searchFilters = true;
//Search using the Property Reference Number
if (!string.IsNullOrEmpty(txtPropRef.Text))
{
sql += "WHERE [Numb] = " + txtPropRef.Text.ToString() + "";
searchFilters = false;
}
if (searchFilters)
{
//Searching by Agent SQL statement
if ((radioAgent.Checked) && !(radioProperty.Checked))
{
if (cboEmployee.Text == "")
{
MessageBox.Show("Please, select an Agent as search cirteria!", "Select a Remax Agent", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
sql += "WHERE ReferEmp = " + cboEmployee.SelectedValue.ToString() + "";
if (cboType.Text != "")
{
sql += " AND [Type] = '" + cboType.Text.ToString() + "'";
}
if (cboCity.Text != "")
{
sql += " AND [City] = '" + cboCity.Text.ToString() + "'";
}
if (cboBedrooms.Text != "")
{
if (cboBedrooms.Text == "4+")
{
sql += " AND [Bedrooms] >= 4";
}
else
{
sql += " AND [Bedrooms] = " + cboBedrooms.Text.ToString() + "";
}
}
if (cboBathrooms.Text != "")
{
if (cboBathrooms.Text == "4+")
{
sql += " AND [Bathrooms] >= 4";
}
else
{
sql += " AND [Bathrooms] = " + cboBathrooms.Text.ToString() + "";
}
}
}
//Searching by Property SQL statement
if ((radioProperty.Checked) && !(radioAgent.Checked))
{
sql += "WHERE ReferEmp >= 0 ";
if (cboType.Text != "")
{
sql += " AND [Type] = '" + cboType.Text.ToString() + "'";
}
if (cboCity.Text != "")
{
sql += " AND [City] = '" + cboCity.Text.ToString() + "'";
}
if (cboBedrooms.Text != "")
{
if (cboBedrooms.Text == "4+")
{
sql += " AND [Bedrooms] >= 4";
}
else
{
sql += " AND [Bedrooms] = " + cboBedrooms.Text.ToString() + "";
}
}
if (cboBathrooms.Text != "")
{
if (cboBathrooms.Text == "4+")
{
sql += " AND [Bathrooms] >= 4";
}
else
{
sql += " AND [Bathrooms] = " + cboBathrooms.Text.ToString() + "";
}
}
}
}
//Displaying the results
OleDbCommand myCmd = new OleDbCommand(sql, clsGlobal.myCon);
OleDbDataReader myReader = myCmd.ExecuteReader();
DataTable Table = new DataTable();
Table.Load(myReader);
gridResult.DataSource = Table;
gridResult.Columns[0].Visible = false;
gridResult.Columns[8].Visible = false;
gridResult.Columns[9].Visible = false;
if (gridResult.Rows.Count == 1)
{
MessageBox.Show("Sorry, no Property is available for you under these conditions.", "Remax Search", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public static List <ServiceManual> GetListSVMData(string fullPath)
{
List <ServiceManual> listData = new List <ServiceManual>();
try
{
DataTable dtResult = new DataTable();
int totalSheet = 0;
using (OleDbConnection objConn = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fullPath + ";Extended Properties='Excel 12.0;HDR=NO;IMEX=1;';"))
{
objConn.Open();
OleDbCommand cmd = new OleDbCommand();
OleDbDataAdapter oleda = new OleDbDataAdapter();
DataSet ds = new DataSet();
DataTable dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = string.Empty;
if (dt != null)
{
var tempDataTable = (from dataRow in dt.AsEnumerable()
where !dataRow["TABLE_NAME"].ToString().Contains("FilterDatabase")
select dataRow).CopyToDataTable();
dt = tempDataTable;
totalSheet = dt.Rows.Count;
sheetName = dt.Rows[0]["TABLE_NAME"].ToString();
}
cmd.Connection = objConn;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM [" + sheetName + "]";
oleda = new OleDbDataAdapter(cmd);
oleda.Fill(ds, "excelData");
dtResult = ds.Tables["excelData"];
objConn.Close();
}
if (dtResult.Rows.Count > 0 && dtResult.Columns.Count == 6)
{
var listSVMData = (from table in dtResult.AsEnumerable()
select new ServiceManual
{
Indoor_Model_Name = table["F1"].ToString(),
Outdoor_Model_Name = table["F2"].ToString(),
Issue_Date = table["F5"].ToString(),
MDC_Code = table["F4"].ToString(),
SVM_Remark = table["F6"].ToString(),
SVM_FileName = table["F3"].ToString()
}).ToList();
listSVMData.RemoveAt(0);
listData = listSVMData;
Utility.WriteLog("Read : " + fullPath, "Success");
return(listData);
}
else
{
return(listData);
}
}
catch (Exception ex)
{
Utility.WriteLog(ex.Message.ToString(), "Error");
return(listData);
}
}
private void registerWrite_Click(object sender, EventArgs e)
{
connection.Open();
OleDbCommand command = new OleDbCommand(); // Establishes that a command is going to be used, usually a SQL statement.
command.Connection = connection; // States the command should be associated with the current connection.
command.CommandText = "select * from userAccount where Username='******'";
// SQL statement is stated here, used to detect if the username has already been used.
// username1 and password1 refer to the text boxes used in the login form.
OleDbDataReader reader = command.ExecuteReader(); // Goes through each record and tries to find appropriate matches with that above SQL.
int count = 0; // Stores a local variable to this method to allow the use of a loop.
while (reader.Read())
{
count++; // Implements variable count by 1 when a match is found.
}
if (count == 1)
{
MetroMessageBox.Show(this, "Username is already used, try another name.", "Error", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
// detects if username and passwords have duplicates, will be avoided in registration process to allowevery usernamne to be unique.
connection.Close();
}
else if (passwordRegister.Text == passwordRepeat.Text)
// Checks if the password repeated and the original matches, as to disallow mistakes to be made when entering the data in the value.
{
if (emailRegister.Text.Contains("@") && emailRegister.Text.Contains("."))
// Checks if the email contains the appropriate symbols for a valid email address
{
try
{
OleDbCommand command1 = new OleDbCommand();
command1.Connection = connection;
command1.CommandText = "insert into userAccount([Username],[Password],[EmailAddress],[FirstName],[SecondName]) values('" + usernameRegister.Text + "','"
+ passwordRegister.Text + "','" + emailRegister.Text + "', '" + firstNameReg.Text + "', '" + secondNameReg.Text + "')";
command1.ExecuteNonQuery();
MetroMessageBox.Show(this, "Account Registered successfully, login now available.", "Account Registered", MessageBoxButtons.OKCancel, MessageBoxIcon.Asterisk);
connection.Close();
//This opens the database connection using the OleDb method first stated in 'using...'.
// It then establishes a new command wants to be executed using command, and the command 'text' is an SQL statement that will insert data
// into the appropriate collumns within the database, userAccount() refers to the collumns in the table, and
// values() captures the database from the textbox that is being used.
//Finally, the connection is closed as to not damage the database being used.
this.Hide();
login loginPage = new login();
loginPage.Show();
}
catch (Exception ex)
{
MetroMessageBox.Show(this, "Error" + ex, "Error Registering Account", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
connection.Close();
}
}
else
{
MessageBox.Show("Email formatted incorrectly... ");
}
// Shows a message if the email is formatted incorrectly, to allow the user to ammend the data to make it appropriate.
}
else
{
MetroMessageBox.Show(this, "Ensure your repeated password matches with password", "Error", MessageBoxButtons.OKCancel, MessageBoxIcon.Warning);
connection.Close();
// shows an error
}
connection.Close();
}
public int connectEdit(Student x, string editchoice, string editdata)
{
OleDbConnection connection = new OleDbConnection();
try
{
connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
Jet OLEDB:Database Password=MyDbPassword; Data Source=C:\Users\franc_000\Documents\College\StudentRegfinal\StudentReg\studentregdb.accdb; Persist Security Info = false; ";
//var cmd = new OleDbCommand("UPDATE Student SET "+"@editchoice"+" = '"+"@entrydata"+"' WHERE "+"@a"+" = @searchdata;"); //need correct reference
var cmd = new OleDbCommand("UPDATE Student SET FirstName = 'bananak' WHERE Id = '123456';"); //need correct reference
connection.Open();
cmd.Parameters.AddRange(new[] {
new OleDbParameter("@entrytype", editchoice),
new OleDbParameter("@entrydata", x.Id),
new OleDbParameter("@a", "Id"),
new OleDbParameter("@searchdata", x.Id)
});
cmd.Connection = connection;
Console.WriteLine(editchoice);
Console.WriteLine(editdata);
Console.WriteLine(x.Id);
cmd.ExecuteNonQuery();
Console.WriteLine("Student edited successfully.");
}
catch(Exception ex)
{
Console.WriteLine("Error " + ex);
}
connection.Close();
return 0;
}
private void satın_al_btn_Click(object sender, EventArgs e)
{
alim_gerceklesti = false;
harcanan_para = 0;
alinan_miktar = 0;
gonderilen_para = 0;
istek_miktar = Convert.ToInt32(textBox1.Text);
istek_ürün = dataGridView1.CurrentRow.Cells["UrunAdi"].Value.ToString();
baglanti.Open();
OleDbCommand komut = new OleDbCommand();
komut.Connection = baglanti;
komut.CommandText = ("Select *from Satis where UrunAdi = '" + istek_ürün + "' AND NOT KullaniciAdi='" + alici_kullanici_adi + "'ORDER BY UrunFiyat ASC");
OleDbDataReader oku = komut.ExecuteReader();
while (oku.Read())
{
if (istek_miktar <= Convert.ToInt32(oku["sUrunMiktar"].ToString()) && Convert.ToInt32(oku["sUrunMiktar"].ToString()) > 0)
{
alinan_fiyat = Convert.ToInt32(oku["UrunFiyat"].ToString());
gonderilen_para = istek_miktar * alinan_fiyat;
harcanan_para += gonderilen_para;
satici_kullanici_adi = oku["KullaniciAdi"].ToString();
urun_birim = oku["UrunBirim"].ToString();
if (Convert.ToInt32(para) >= harcanan_para)
{
Para_gonder(satici_kullanici_adi, gonderilen_para);
satistan_cikar(istek_ürün, satici_kullanici_adi, istek_miktar, alinan_fiyat);
istek_miktar += alinan_miktar;
alim_gerceklesti = true;
MessageBox.Show("Satın Alım Gerçekleştirilmiştir", "Tamam");
textBox1.Clear();
break;
}
else
{
MessageBox.Show("Bakiyeniz Yeterli değildir!", "Tamam");
break;
}
}
else if (istek_miktar > Convert.ToInt32(oku["sUrunMiktar"].ToString()) && Convert.ToInt32(oku["sUrunMiktar"].ToString()) > 0)
{
alinan_miktar = Convert.ToInt32(oku["sUrunMiktar"].ToString());
satici_kullanici_adi = oku["KullaniciAdi"].ToString();
alinan_fiyat = Convert.ToInt32(oku["UrunFiyat"].ToString());
gonderilen_para = alinan_miktar * alinan_fiyat;
harcanan_para += gonderilen_para;
if (Convert.ToInt32(para) >= harcanan_para)
{
urun_birim = oku["UrunBirim"].ToString();
Para_gonder(satici_kullanici_adi, gonderilen_para);
satistan_cikar(istek_ürün, satici_kullanici_adi, alinan_miktar, alinan_fiyat);
istek_miktar -= alinan_miktar;
}
else
{
MessageBox.Show("Bakiyeniz Yeterli değildir!", "Tamam");
break;
}
}
}
if (alim_gerceklesti)
{
urun_ekle(alici_kullanici_adi, istek_miktar.ToString(), istek_ürün, urun_birim);
Para_cikar(alici_kullanici_adi, harcanan_para);
}
baglanti.Close();
}
public int connectRemove(Student x, string idremove)
{
OleDbConnection connection = new OleDbConnection();
try
{
connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccessFile.accdb;
Jet OLEDB:Database Password=MyDbPassword; Data Source=C:\Users\franc_000\Documents\College\StudentRegfinal\StudentReg\studentregdb.accdb; Persist Security Info = false; ";
var cmd = new OleDbCommand("DELETE FROM Student WHERE Id = @a;"); //need correct reference
connection.Open();
cmd.Parameters.AddRange(new[] {
new OleDbParameter("@a", x.Id)
});
cmd.Connection = connection;
cmd.ExecuteNonQuery();
Console.WriteLine("Student removed successfully.");
}
catch(Exception ex)
{
Console.WriteLine("Error " + ex);
}
connection.Close();
return 0;
}
private void urun_ekle(string Kullanici_adi, string alinan_miktar, string urunAd, string Birim)
{
OleDbCommand komut = new OleDbCommand("insert into kUrun(UrunAdi,UrunMiktar,KullaniciU,UrunBirim) values('" + urunAd + "','" + alinan_miktar + "','" + Kullanici_adi + "','" + Birim + "')", baglanti);
komut.ExecuteNonQuery();
}
/// <summary>
/// 将数据导出至Excel文件
/// </summary>
/// <param name="Table">DataTable对象</param>
/// <param name="ExcelFilePath">Excel文件路径</param>
public static bool OutputToExcel(DataTable Table, string ExcelFilePath)
{
if (File.Exists(ExcelFilePath))
{
throw new Exception("该文件已经存在！");
}
if ((Table.TableName.Trim().Length == 0) || (Table.TableName.ToLower() == "table"))
{
Table.TableName = "Sheet1";
}
//数据表的列数
int ColCount = Table.Columns.Count;
//用于记数，实例化参数时的序号
int i = 0;
//创建参数
OleDbParameter[] para = new OleDbParameter[ColCount];
//创建表结构的SQL语句
string TableStructStr = @"Create Table " + Table.TableName + "(";
//连接字符串
string connString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelFilePath + ";Extended Properties=Excel 8.0;";
OleDbConnection objConn = new OleDbConnection(connString);
//创建表结构
OleDbCommand objCmd = new OleDbCommand();
//数据类型集合
ArrayList DataTypeList = new ArrayList();
DataTypeList.Add("System.Decimal");
DataTypeList.Add("System.Double");
DataTypeList.Add("System.Int16");
DataTypeList.Add("System.Int32");
DataTypeList.Add("System.Int64");
DataTypeList.Add("System.Single");
//遍历数据表的所有列，用于创建表结构
foreach (DataColumn col in Table.Columns)
{
//如果列属于数字列，则设置该列的数据类型为double
if (DataTypeList.IndexOf(col.DataType.ToString()) >= 0)
{
para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.Double);
objCmd.Parameters.Add(para[i]);
//如果是最后一列
if (i + 1 == ColCount)
{
TableStructStr += col.ColumnName + " double)";
}
else
{
TableStructStr += col.ColumnName + " double,";
}
}
else
{
para[i] = new OleDbParameter("@" + col.ColumnName, OleDbType.VarChar);
objCmd.Parameters.Add(para[i]);
//如果是最后一列
if (i + 1 == ColCount)
{
TableStructStr += col.ColumnName + " varchar)";
}
else
{
TableStructStr += col.ColumnName + " varchar,";
}
}
i++;
}
//创建Excel文件及文件结构
try
{
objCmd.Connection = objConn;
objCmd.CommandText = TableStructStr;
if (objConn.State == ConnectionState.Closed)
{
objConn.Open();
}
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
throw exp;
}
//插入记录的SQL语句
string InsertSql_1 = "Insert into " + Table.TableName + " (";
string InsertSql_2 = " Values (";
string InsertSql = "";
//遍历所有列，用于插入记录，在此创建插入记录的SQL语句
for (int colID = 0; colID < ColCount; colID++)
{
if (colID + 1 == ColCount) //最后一列
{
InsertSql_1 += Table.Columns[colID].ColumnName + ")";
InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ")";
}
else
{
InsertSql_1 += Table.Columns[colID].ColumnName + ",";
InsertSql_2 += "@" + Table.Columns[colID].ColumnName + ",";
}
}
InsertSql = InsertSql_1 + InsertSql_2;
//遍历数据表的所有数据行
for (int rowID = 0; rowID < Table.Rows.Count; rowID++)
{
for (int colID = 0; colID < ColCount; colID++)
{
if (para[colID].DbType == DbType.Double && Table.Rows[rowID][colID].ToString().Trim() == "")
{
para[colID].Value = 0;
}
else
{
para[colID].Value = Table.Rows[rowID][colID].ToString().Trim();
}
}
try
{
objCmd.CommandText = InsertSql;
objCmd.ExecuteNonQuery();
}
catch (Exception exp)
{
string str = exp.Message;
}
}
try
{
if (objConn.State == ConnectionState.Open)
{
objConn.Close();
}
}
catch (Exception exp)
{
throw exp;
}
return(true);
}
public static void executeNonQuery(OleDbConnection conn, string insertStatement)
{
OleDbCommand comm = new OleDbCommand(insertStatement, conn);
comm.ExecuteNonQuery();
}
