public string GetString(this OleDbDataReader reader, string colName)
{
string result = "";
if (!string.IsNullOrEmpty(colName))
{
int pos = reader.GetOrdinal(colName);
result = reader.GetString(pos);
}
return(result);
}
public int GetOrdinal(string name)
{
if (SDR != null)
{
return(SDR.GetOrdinal(name));
}
else
{
return(ODR.GetOrdinal(name));
}
}
public static string getString(OleDbDataReader dr, string cn)
{
try
{
return(dr.GetString(dr.GetOrdinal(cn)));
}
catch (InvalidCastException ice)
{
return("");
}
}
public static bool getBool(OleDbDataReader dr, string cn)
{
try
{
return(dr.GetBoolean(dr.GetOrdinal(cn)));
}
catch (InvalidCastException ice)
{
return(false);
}
}
static void readListOfConversation(int id, List <string> answer)
{
readListCommand.CommandText = String.Format(readListString, id);
OleDbDataReader reader = readListCommand.ExecuteReader();
while (reader.Read())
{
selectUserCommand.Parameters[0].Value =
reader.GetInt32(reader.GetOrdinal("UserId"));
OleDbDataReader readerUser = selectUserCommand.ExecuteReader();
readerUser.Read();
Human friend = readUserFromReader(readerUser, null);
answer.Add(friend.ToString() + " "
+ reader.GetInt32(reader.GetOrdinal("NumberOfUnread")));
readerUser.Close();
}
reader.Close();
}
public void Reset()
{
try
{
con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + lblpath.Text + "';");
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = "SELECT MAX(PURCHASEMASTERID) as PURCHASEMASTERID FROM PurchaseMaster ";
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
if (rdr.IsDBNull(rdr.GetOrdinal("PURCHASEMASTERID")))
{
txtinvoiceno.Text = Convert.ToString("1");
}
else
{
txtinvoiceno.Text = Convert.ToString(Convert.ToInt16(rdr["PURCHASEMASTERID"]) + 1);
}
}
if ((rdr != null))
{
rdr.Close();
}
con.Close();
supplierresete();
itemresete();
txtTotqty.Text = "";
txtunit.Text = "";
txtprice.Text = "";
txtGrandTotal.Text = "";
txtdiscper.Text = "0";
txtdiscount.Text = "0";
txtmrp.Text = "";
txttotdiscount.Text = "";
cmbitemname.Text = "";
cmbSupplier.Text = "";
txtitemno.Text = "";
txtqty.Text = "1";
txttax.Text = "";
txtBasicAmount.Text = "";
txtNetAmount.Text = "";
txtamount.Text = "";
txttaxname.Text = "";
txttotaltax.Text = "";
btnSave.Enabled = true;
btnDelete.Enabled = false;
cmbSupplier.Focus();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public void setMail(String Mail)
{
mail.Text = Mail;
string dbprovider = controler.m.getDBprovider();
String strSql = "SELECT * FROM [users] WHERE [email]= '" + Mail + "'";
OleDbConnection conn = new OleDbConnection(dbprovider);
MySearchList sl = new MySearchList(controler);
conn.Open();
OleDbCommand cmd = new OleDbCommand(strSql, conn);
OleDbDataReader reader = cmd.ExecuteReader();
reader.Read();
fn.Text = reader.GetString(1);
ln.Text = reader.GetString(2);
city.Text = reader.GetString(3);
street.Text = reader.GetString(4);
string t = reader.GetString(5);
string[] telArray = t.Split('-');
firBox.Text = telArray[0];
tel.Text = telArray[1];
age.Text = reader.GetInt32(6).ToString();
genbox.Text = reader.GetString(7);
meter.Text = reader.GetString(8);
pass.Text = reader.GetString(9);
string birth = reader.GetString(10);
string[] birthDate = birth.Split('/');
dayBox1.Text = birthDate[0];
monBox2.Text = birthDate[1];
year.Text = birthDate[2];
smok.Checked = reader.GetBoolean(reader.GetOrdinal("smoking"));
havePet.Checked = reader.GetBoolean(12);
org.Text = reader.GetInt32(13).ToString();
education.Text = reader.GetString(14);
dustAllergy.Checked = reader.GetBoolean(reader.GetOrdinal("dustellrgy"));
perBox4.Checked = reader.GetBoolean(reader.GetOrdinal("perfumellergy"));
mynote.Text = (reader["notes"].ToString());
conn.Close();
}
public void Reset()
{
try
{
con = new OleDbConnection(lblpath.Text);
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = "SELECT MAX(UNITID) as UNITID FROM Unit";
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
if (rdr.IsDBNull(rdr.GetOrdinal("UNITID")))
{
lblUnitId.Text = Convert.ToString("1");
}
else
{
lblUnitId.Text = Convert.ToString(Convert.ToInt16(rdr["UNITID"]) + 1);
}
}
if ((rdr != null))
{
rdr.Close();
}
con.Close();
con = new OleDbConnection(lblpath.Text);
con.Open();
String sql = "SELECT UNITID,UNITNAME FROM Unit";
cmd = new OleDbCommand(sql, con);
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dgwUnit.Rows.Clear();
while (rdr.Read() == true)
{
dgwUnit.Rows.Add(rdr[0], rdr[1]);
}
con.Close();
txtUnit.Text = "";
btnSave.Enabled = true;
btnDelete.Enabled = false;
btnUpdate.Enabled = false;
txtUnit.Focus();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
batch();
con = new OleDbConnection(lblpath.Text);
con.Open();
string cb = "insert into ItemMaster(ITEMID,ITEMNAME,GROUPID,GROUPNAME,PRODUCTCOMPANYID,PRODUCTCOMPANYNAME,UNITID,UNITNAME,TAXID,TAXNAME,PURCHASERATE,SALERATE,MRP,OPSTOCK,OPSTOCKVALUE,MINIMUMQTY,ALTUNITID,ALTUNITNAME,CONVERSIONFACTOR,MAINTAINBATCH) VALUES (" + lblitemid.Text + ",'" + txtItemName.Text + "'," + lblgroupid.Text + ",'" + cmbGroup.Text + "'," + lblcompanyid.Text + ",'" + cmbCompany.Text + "'," + lbUnit.Text + ",'" + cmbUnit.Text + "'," + lbltaxid.Text + ",'" + cmbTax.Text + "'," + txtPurchaseRate.Text + "," + txtSaleRate.Text + "," + txtMrp.Text + "," + txtOpStock.Text + "," + txtOpStockValue.Text + "," + txtMinimumQty.Text + "," + lblaltunit.Text + ",'" + cmbAltUnit.Text + "'," + txtConversionFactor.Text + ",'" + batchstatus + "')";
cmd = new OleDbCommand(cb);
cmd.Connection = con;
cmd.ExecuteReader();
con.Close();
if (Convert.ToDouble(txtOpStock.Text) > 0)
{
con = new OleDbConnection(lblpath.Text);
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = "SELECT MAX(INVENTORYID) as INVENTORYID FROM INVENTORY";
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
if (rdr.IsDBNull(rdr.GetOrdinal("INVENTORYID")))
{
INVID = Convert.ToString("1");
}
else
{
INVID = Convert.ToString(Convert.ToInt16(rdr["INVENTORYID"]) + 1);
}
}
if ((rdr != null))
{
rdr.Close();
}
con.Close();
con = new OleDbConnection(lblpath.Text);
con.Open();
string cb1 = "insert into INVENTORY(INVENTORYID,INVENTORYDATE,VOUCHERTYPE,VOUCHERNO,ITEMID,ITEMNAME,INQTY,OUTQTY) VALUES (" + INVID + ",'" + DateTime.Today + "','" + "OPENING STOCK" + "'," + lblitemid.Text + "," + lblitemid.Text + ",'" + txtItemName.Text + "'," + txtOpStock.Text + "," + "0" + ")";
cmd = new OleDbCommand(cb1);
cmd.Connection = con;
cmd.ExecuteReader();
con.Close();
}
MessageBox.Show("Successfully saved", "Tax", MessageBoxButtons.OK, MessageBoxIcon.Information);
Reset();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public TemplateFieldData GetTemplateField(int IdTemplateField)
{
TemplateFieldData result = null;
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
String sqlQuery = "SELECT ID, LABEL, ID_FIELD_TYPE FROM TEMPLATE_FIELD WHERE ID = " + IdTemplateField;
OleDbDataReader resultData = dataContext.Select(sqlQuery);
if (resultData.HasRows)
{
result = new TemplateFieldData();
int idxColumn = resultData.GetOrdinal("ID");
result.Id = resultData.GetInt32(idxColumn);
idxColumn = resultData.GetOrdinal("LABEL");
result.FieldName = resultData.GetString(idxColumn);
idxColumn = resultData.GetOrdinal("ID");
result.FieldType = (TemplateFieldType)resultData.GetInt32(idxColumn);
}
}
return(result);
}
public string lookup(string table, int id, string field = "name", string idField = "_id")
{
string value = "";
OleDbDataReader reader = query(table, $"{idField} = {id}");
if (reader.HasRows)
{
reader.Read();
value = reader.GetValue(reader.GetOrdinal(field)).ToString();
}
return(value);
}
public void Reset()
{
try
{
con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + lblpath.Text + "';");
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = "SELECT MAX(GROUPID) as GROUPID FROM ProductGroup";
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
if (rdr.IsDBNull(rdr.GetOrdinal("GROUPID")))
{
lblgroupid.Text = Convert.ToString("1");
}
else
{
lblgroupid.Text = Convert.ToString(Convert.ToInt16(rdr["GROUPID"]) + 1);
}
}
if ((rdr != null))
{
rdr.Close();
}
con.Close();
con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + lblpath.Text + "';");
con.Open();
String sql = "SELECT GROUPID,GROUPNAME FROM ProductGroup";
cmd = new OleDbCommand(sql, con);
rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
dgwGroup.Rows.Clear();
while (rdr.Read() == true)
{
dgwGroup.Rows.Add(rdr[0], rdr[1]);
}
con.Close();
txtGroupName.Text = "";
btnSave.Enabled = true;
btnDelete.Enabled = false;
txtGroupName.Focus();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void dgwItem_MouseClick(object sender, MouseEventArgs e)
{
try
{
con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + lblpath.Text + "';");
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = "SELECT ITEMID,ITEMNAME,GROUPID,GROUPNAME,PRODUCTCOMPANYID,PRODUCTCOMPANYNAME,UNITID,UNITNAME,TAXID,TAXNAME,PURCHASERATE,SALERATE,MRP,OPSTOCK,OPSTOCKVALUE,MINIMUMQTY,ALTUNITID,ALTUNITNAME,CONVERSIONFACTOR FROM ItemMaster where ITEMID =" + dgwItem.SelectedRows[0].Cells[0].Value.ToString() + "";
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
if (rdr.IsDBNull(rdr.GetOrdinal("ITEMID")))
{
MessageBox.Show("INVALID SELECTION");
}
else
{
txtItemName.Text = Convert.ToString(rdr["ITEMNAME"]);
cmbGroup.Text = Convert.ToString(rdr["GROUPNAME"]);
cmbCompany.Text = Convert.ToString(rdr["PRODUCTCOMPANYNAME"]);
cmbUnit.Text = Convert.ToString(rdr["UNITNAME"]);
cmbTax.Text = Convert.ToString(rdr["TAXNAME"]);
txtPurchaseRate.Text = Convert.ToString(rdr["PURCHASERATE"]);
txtSaleRate.Text = Convert.ToString(rdr["SALERATE"]);
txtMrp.Text = Convert.ToString(rdr["MRP"]);
txtOpStock.Text = Convert.ToString(rdr["OPSTOCK"]);
txtOpStockValue.Text = Convert.ToString(rdr["OPSTOCKVALUE"]);
txtMinimumQty.Text = Convert.ToString(rdr["MINIMUMQTY"]);
cmbAltUnit.Text = Convert.ToString(rdr["ALTUNITNAME"]);
txtConversionFactor.Text = Convert.ToString(rdr["CONVERSIONFACTOR"]);
btnSave.Enabled = false;
btnDelete.Enabled = true;
}
}
if ((rdr != null))
{
rdr.Close();
}
con.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void btnUpdate_Click(object sender, EventArgs e)
{
try
{
batch();
con = new OleDbConnection(lblpath.Text);
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = "SELECT INVENTORYID as INVENTORYID FROM INVENTORY WHERE VOUCHERTYPE='" + "OPENING STOCK" + "' AND VOUCHERNO=" + lblitemid.Text + " AND ITEMID=" + lblitemid.Text + " ";
rdr = cmd.ExecuteReader();
if (rdr.Read())
{
if (rdr.IsDBNull(rdr.GetOrdinal("INVENTORYID")))
{
MessageBox.Show("NO INVENTRY");
}
else
{
con = new OleDbConnection(lblpath.Text);
con.Open();
string cb = "update INVENTORY set ITEMNAME = '" + txtItemName.Text + "', INQTY =" + txtOpStock.Text + " where VOUCHERTYPE='" + "OPENING STOCK" + "' AND VOUCHERNO=" + lblitemid.Text + " AND ITEMID=" + lblitemid.Text + " ";
cmd = new OleDbCommand(cb);
cmd.Connection = con;
cmd.ExecuteReader();
con.Close();
}
}
if ((rdr != null))
{
rdr.Close();
}
con.Close();
con = new OleDbConnection(lblpath.Text);
con.Open();
string cb1 = "update ItemMaster set ITEMNAME = '" + txtItemName.Text + "', GROUPID =" + lblgroupid.Text + " , GROUPNAME= '" + cmbGroup.Text + "',PRODUCTCOMPANYID = " + lblcompanyid.Text + ", PRODUCTCOMPANYNAME ='" + cmbCompany.Text + "' , UNITID= " + lbUnit.Text + ",UNITNAME = '" + cmbUnit.Text + "', TAXID =" + lbltaxid.Text + " , TAXNAME= '" + cmbTax.Text + "',PURCHASERATE = " + txtPurchaseRate.Text + ", SALERATE =" + txtSaleRate.Text + " , MRP= " + txtMrp.Text + ",OPSTOCK = " + txtOpStock.Text + ", OPSTOCKVALUE =" + txtOpStockValue.Text + " , MINIMUMQTY= " + txtMinimumQty.Text + ",ALTUNITID = " + lblaltunit.Text + ", ALTUNITNAME ='" + cmbAltUnit.Text + "' , CONVERSIONFACTOR= " + txtConversionFactor.Text + ",MAINTAINBATCH = '" + batchstatus + "' where ITEMID = " + lblitemid.Text + "";
cmd = new OleDbCommand(cb1);
cmd.Connection = con;
cmd.ExecuteReader();
con.Close();
MessageBox.Show("Successfully updated", "ItemMaster", MessageBoxButtons.OK, MessageBoxIcon.Information);
Reset();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private object GetValue(OleDbDataReader reader, string columnName, object defaultValue)
{
int index = reader.GetOrdinal(columnName);
if (reader.IsDBNull(index))
{
return(defaultValue);
}
else
{
return(reader[columnName]);
}
}
private static void SetPrimaryKey(OleDbConnection connection, dynamic t, string tbl)
{
string sql = @"SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(constraint_name), 'IsPrimaryKey') = 1 AND table_name = '" + tbl + "'";
OleDbCommand cmd = new OleDbCommand(sql, connection);
OleDbDataReader r = cmd.ExecuteReader();
if (r.Read())
{
t.idProperty = r.GetString(r.GetOrdinal("column_name"));
}
r.Close();
}
/// <summary>
/// Swaps the values.
/// </summary>
/// <param name="databasePath">The database path.</param>
/// <param name="table">The table.</param>
/// <param name="column1">The column1.</param>
/// <param name="column2">The column2.</param>
/// <param name="errOut">The error out.</param>
/// <param name="usePassword">if set to <c>true</c> [use password].</param>
/// <returns><c>true</c> if XXXX, <c>false</c> otherwise.</returns>
/// <exception cref="System.Exception"></exception>
internal static bool SwapValues(string databasePath, string table, string column1, string column2, out string errOut, bool usePassword = true)
{
errOut = "";
bool bAns = false;
string sql = "";
try
{
OleDbConnection conn = new OleDbConnection(DatabaseConnectionString(databasePath, usePassword));
conn.Open();
OleDbCommand cmd = new OleDbCommand($"Select id,{column1},{column2} from {table}", conn);
using (OleDbDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
var id = dr.GetValue(dr.GetOrdinal("id"));
string col1Value = dr.GetValue(dr.GetOrdinal(column1)).ToString();
string col2Value = dr.GetValue(dr.GetOrdinal(column2)).ToString();
if (col2Value != null)
{
sql = $"UPDATE {table} set {column2}={col1Value} where id = {id}";
if (!RunSql(databasePath, sql, out errOut))
{
throw new Exception(errOut);
}
}
}
}
conn.Close();
bAns = true;
}
catch (Exception e)
{
errOut = $"{ErrorMessage("SwapValues", e)}. {Environment.NewLine} {Environment.NewLine}SQL - {sql}";
}
return(bAns);
}
static void doAsaReaderObj(OleDbCommand cmd)
{
OleDbDataReader rdr = null;
rdr = cmd.ExecuteReader();
//RS.AbsolutePage
int iCnt = 0;
while (rdr.Read())
{
string col = "System.Search.Rank";
Console.Write("{0}) {1}: {2} ", ++iCnt, col, rdr[rdr.GetOrdinal(col)]);
col = "System.ItemPathDisplay";
Console.Write("{0}: {1} ", col, rdr[rdr.GetOrdinal(col)]);
col = "System.Size";
Console.WriteLine("{0}: {1} ", col, rdr[rdr.GetOrdinal(col)]);
}
rdr.Close();
}
private List <Region> GetRegionsFromCmd(OleDbCommand cmd)
{
List <Region> regions = new List <Region>();
using (OleDbDataReader dbReader = cmd.ExecuteReader())
{
int _regionId = dbReader.GetOrdinal("id");
int _regionName = dbReader.GetOrdinal("region_name");
while (dbReader.Read())
{
var region = new Region();
region.id = dbReader.GetInt32(_regionId);
region.name = dbReader.GetString(_regionName);
regions.Add(region);
}
}
return(regions);
}
private static T GetData <T>(OleDbDataReader reader, string colName)
{
object value = reader.GetValue(reader.GetOrdinal(colName));
if (!DBNull.Value.Equals(value))
{
return((T)value);
}
else
{
return(default(T));
}
}
/// <summary>
/// 用户登陆
/// </summary>
/// <param name="userName"></param>
/// <param name="userPasswd"></param>
/// <returns></returns>
public static bool CheckUser(string userName, string userPasswd)
{
bool login = false;
string strPath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "\\" + DBName + ".accdb";
string conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data source=" + strPath;
OleDbConnection conn = new OleDbConnection(conStr);
conn.Open();
OleDbCommand command = new OleDbCommand("select * from UserList where userName = '******'" + "and passwd = '" + userPasswd + "'", conn);
OleDbDataReader dr = command.ExecuteReader();
if (dr.Read())
{
MessageBox.Show("登陆成功！", "message", MessageBoxButton.OK);
login = true;
string type = dr.GetString(dr.GetOrdinal("type"));
if (type == "manager")
{
GameListWindow gameListWindow = new GameListWindow();
gameListWindow.Show();
}
else
{
string nickname = dr.GetString(dr.GetOrdinal("nickName"));
string userID = dr.GetInt32(dr.GetOrdinal("ID")).ToString();
EventCenter.Broadcast <string, string>(EventType.Login, nickname, userID);
}
}
else
{
MessageBox.Show("用户名或密码错误！", "message", MessageBoxButton.OK);
}
conn.Close();
return(login);
}
//-----------------------------------------------------------------------------------
// Get Field Data from the Reader
//-----------------------------------------------------------------------------------
private string GetFieldDataStr(string DataFieldName, ref OleDbDataReader rdr)
{
int idx = rdr.GetOrdinal(DataFieldName);
if (idx >= 0)
{
return(rdr[idx].ToString());
}
else
{
return("");
}
}
private static Guid GetGuid(OleDbDataReader reader, string fieldName)
{
int ord = reader.GetOrdinal(fieldName);
if (ord < 0 || reader.IsDBNull(ord))
{
return(Guid.Empty);
}
else
{
return(reader.GetGuid(ord));
}
}
public int CreateCategory(Category categoryToCreate)
{
int categoryId = -1;
using (PikoDataContext dataContext = new PikoDataContext(PikoDataServiceApp.DatabasePath, "PikoServer"))
{
if (categoryToCreate.Id == -1)
{
String sqlQuery = "SELECT MAX(ID) FROM CATEGORIES";
OleDbDataReader queryData = dataContext.Select(sqlQuery);
if (queryData.HasRows && queryData.Read())
{
int idxColumn = queryData.GetOrdinal("ID");
categoryToCreate.Id = queryData.GetInt32(idxColumn) + 1;
queryData.Close();
sqlQuery = "INSERT INTO CATEGORIES(ID,LABEL) VALUES(" + categoryToCreate.Id + ",'" + categoryToCreate.Name + "')";
dataContext.Execute(sqlQuery);
}
}
else
{
//UPDATE
String sqlQuery = "UPDATE CATEGORIES SET " + Environment.NewLine;
sqlQuery += "LABEL='" + categoryToCreate.Name + "'" + Environment.NewLine;
sqlQuery += "WHERE ID=" + categoryToCreate.Id;
dataContext.Execute(sqlQuery);
}
if (categoryToCreate.TemplateFields != null && categoryToCreate.TemplateFields.Length > 0)
{
Category currentDbCategory = this.GetCategory(categoryToCreate.Id);
foreach (TemplateFieldData tfData in categoryToCreate.TemplateFields)
{
if (tfData.Id == -1)
{
if (!this.TemplateFieldExists(tfData.FieldName, tfData.FieldType))
{
this.AddTemplateField(tfData);
}
}
else
{
//Update template field
this.UpdateTemplateField(tfData.Id, tfData.FieldName, tfData.FieldType);
}
}
}
}
return(categoryId);
}
public void run()
{
Exception exp = null;
OleDbConnection con = new OleDbConnection(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
con.Open();
OleDbCommand cmd = new OleDbCommand("Select * From Customers", con);
OleDbDataReader rdr = cmd.ExecuteReader();
try
{
BeginCase("column REGION ordinal");
Compare(rdr.GetOrdinal("REGION"), 6);
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
try
{
BeginCase("column not exists");
try
{
int i = rdr.GetOrdinal("blabla");
}
catch (Exception ex) { exp = ex; }
Compare(exp.GetType().FullName, typeof(IndexOutOfRangeException).FullName);
exp = null;
}
catch (Exception ex) { exp = ex; }
finally{ EndCase(exp); exp = null; }
if (con.State == ConnectionState.Open)
{
con.Close();
}
}
void LoadProducts(string nameCutegory, string marka)
{
OleDbConnection connection = new OleDbConnection(connstring);
try
{
connection.Open();
OleDbCommand command = new OleDbCommand("SELECT * FROM [" + nameCutegory + "] WHERE ID_Марки = (SELECT ID_Марки FROM [Марки авто] WHERE Название = ?)", connection);
OleDbParameter parameter = new OleDbParameter("Название", OleDbType.VarChar);
parameter.Value = marka;
command.Parameters.Add(parameter);
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
Image image;
if (!reader.IsDBNull(reader.GetOrdinal("Изображение")))
{
MemoryStream memoryStream = new MemoryStream();
memoryStream.Write((byte[])reader["Изображение"], 0, ((byte[])reader["Изображение"]).Length);
image = Image.FromStream(memoryStream);
}
else
{
image = Properties.Resources.icons8_unsplash_80;
}
flowLayoutPanel1.Controls.Add(new Cart(lNameCategory.Text,
reader["ID"].ToString(),
reader["Название"].ToString(),
reader["Описание"].ToString(),
reader["Цена"].ToString(),
image,
reader["ID_Марки"].ToString(),
this)
);
}
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
connection.Close();
}
}
public List <PlayerViewModel> GetAllPlayers()
{
var players = new List <PlayerViewModel>();
using (OleDbCommand cmd = new OleDbCommand("SELECT list_name, player_nr FROM Player", db))
using (OleDbDataReader dbReader = cmd.ExecuteReader())
{
int _playerName = dbReader.GetOrdinal("list_name");
int _playerNumber = dbReader.GetOrdinal("player_nr");
while (dbReader.Read())
{
var player = new PlayerViewModel();
player.playerName = dbReader.GetString(_playerName);
player.playerNumber = dbReader.GetString(_playerNumber);
players.Add(player);
}
}
return(players);
}
private static List <ProductCategory> LoadProductCategoryListFromDataReader(OleDbDataReader reader)
{
var categories = new List <ProductCategory>();
while (reader.Read())
{
var category = new ProductCategory();
category.Id = reader.GetInt32(reader.GetOrdinal("CategoryID"));
category.ParentId = reader.GetNullableInt32(reader.GetOrdinal("ParentCategoryID"));
category.Name = reader.GetStringSafe(reader.GetOrdinal("CategoryName"));
category.SortOrder = reader.GetInt32(reader.GetOrdinal("Sequence"));
category.PictureFilename = reader.GetStringSafe(reader.GetOrdinal("WS_PictureFile"));
category.PictureUpdated = reader.GetBoolean(reader.GetOrdinal("PictureUpdated"));
category.TargetUrl = reader.GetStringSafe(reader.GetOrdinal("TargetURL"));
category.CreatedDttm = reader.GetNullableDateTime(reader.GetOrdinal("CreateDttm"));
category.UpdatedDttm = reader.GetNullableDateTime(reader.GetOrdinal("UpdateDttm"));
category.DeletedDttm = reader.GetNullableDateTime(reader.GetOrdinal("DeleteDttm"));
categories.Add(category);
}
return(categories);
}
public List <PlayersPerRegionPerSeason> GetPlayersPerRegionPerSeason()
{
var result = new List <PlayersPerRegionPerSeason>();
using (OleDbCommand cmd = new OleDbCommand(
"SELECT season_year, season_period, region_name, number_of_players, poule_category" +
" FROM PlayersPerRegionPerSeason, Season, Region" +
" WHERE PlayersPerRegionPerSeason.season_id=Season.id AND PlayersPerRegionPerSeason.region_id=Region.id" +
" ORDER BY season_year ASC, season_period ASC, region_name ASC", db))
using (OleDbDataReader dbReader = cmd.ExecuteReader())
{
int _seasonYear = dbReader.GetOrdinal("season_year");
int _seasonPeriod = dbReader.GetOrdinal("season_period");
int _regionName = dbReader.GetOrdinal("region_name");
int _playerCount = dbReader.GetOrdinal("number_of_players");
PlayersPerRegionPerSeason season = null;
while (dbReader.Read())
{
if (season == null || season.regionName != dbReader.GetString(_regionName) || season.seasonYear != dbReader.GetInt32(_seasonYear) || season.seasonPeriod != dbReader.GetInt32(_seasonPeriod))
{
season = new PlayersPerRegionPerSeason();
season.seasonYear = dbReader.GetInt32(_seasonYear);
season.seasonPeriod = dbReader.GetInt32(_seasonPeriod);
season.regionName = dbReader.GetString(_regionName);
season.playerCount = 0;
result.Add(season);
}
season.playerCount += dbReader.GetInt32(_playerCount);
}
}
return(result);
}
private void btn_search_Click(object sender, EventArgs e)
{
try
{
List <Studentdetails> queryResults = new List <Studentdetails>();
connection.Open();
OleDbCommand command = new OleDbCommand();
command.Connection = connection;
string query = "Select [AdmissionID], [Class], [StudentName], [FatherName] from [StudentDetails] where [StudentName] = '" + txt_s_studentname.Text + "' OR [AdmissionID] = '" + txt_s_AdmissionNo.Text + "' OR [Class] = '" + com_s_Class.Text + "'";
command.CommandText = query;
OleDbDataReader reader = command.ExecuteReader();
while (reader.Read())
{
queryResults.Add(new Studentdetails
{
// NoOFStudent = Convert.ToString(reader.GetInt32(reader.GetOrdinal("NFStudent"))),
studentname = reader.GetString(reader.GetOrdinal("StudentName")),
admissionid = reader.GetString(reader.GetOrdinal("AdmissionID")),
fathername = reader.GetString(reader.GetOrdinal("FatherName")),
classname = reader.GetString(reader.GetOrdinal("Class"))
});
}
this.dataGridView1.DataSource = queryResults;
//dataGridView1.EnableHeadersVisualStyles = false;
//dataGridView1.ColumnHeadersDefaultCellStyle = BlueDataGridHeaderStyle.GetInstance();
connection.Close();
}
catch (OleDbException ex)
{
MessageBox.Show(ex.Message);
connection.Close();
}
}
