public DataSet GetUserData(string username, string password)
{
try
{
string @query = String.Format(@"select UserId, UserName, UserPassword, UserFullName,ContactNumber, EmailId, RoleId
from [Users]
where UserName='******' and UserPassword='******'", username, password);
fun.OpenConnection();
if (fun.getConnection().State == ConnectionState.Open)
{
DataSet ds = fun.fillComboDataset(query);
temp.ExecuteQuery("Delete from [Users]");
temp.ExecuteQuery(String.Format(@"INSERT INTO [Users](UserId, UserName, UserPassword, UserFullName,ContactNumber, EmailId, RoleId)
VALUES({0},'{1}','{2}','{3}','{4}','{5}','{6}')", ds.Tables[0].Rows[0]["UserId"]
, ds.Tables[0].Rows[0]["UserName"]
, ds.Tables[0].Rows[0]["UserPassword"]
, ds.Tables[0].Rows[0]["UserFullName"]
, ds.Tables[0].Rows[0]["ContactNumber"]
, ds.Tables[0].Rows[0]["EmailId"]
, ds.Tables[0].Rows[0]["RoleId"]));
return(ds);
}
else
{
return(temp.fillComboDataset(query));
}
}
catch (Exception ex)
{
throw ex;
}
}
public DataSet GetOperations()
{
try
{
string query = "select OperationsId, OperationsName from Operations order by OperationsName";
fun.OpenConnection();
if (fun.getConnection().State == ConnectionState.Open)
{
return(fun.fillComboDataset(query));
}
else
{
return(temp.fillComboDataset(query));
}
}
catch (Exception ex)
{
throw ex;
}
}
public DataSet fillLocation()
{
try
{
string query = @"select LocId, LocName from Location";
fun.OpenConnection();
if (fun.getConnection().State == ConnectionState.Open)
{
return(fun.fillComboDataset(query));
}
else
{
return(temp.fillComboDataset(query));
}
}
catch (Exception ex)
{
throw ex;
}
}
public bool SyncCountry()
{
try
{
string query = @"SELECT name FROM sqlite_master WHERE type='table' AND name='Country';";
DataSet ds = temp.fillComboDataset(query);
if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
query = @"CREATE TABLE [Country](
[CountryId] [int] NOT NULL,
[CountryName] [varchar](500) NOT NULL,
[Region] [varchar](50) NOT NULL)" ;
temp.ExecuteQuery(query);
}
fun.OpenConnection();
if (fun.getConnection().State == ConnectionState.Open)
{
query = @"select CountryId, CountryName, Region from Country";
ds = fun.fillComboDataset(query);
if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
query = "Delete from Country";
temp.ExecuteQuery(query);
foreach (DataRow dr in ds.Tables[0].Rows)
{
query = String.Format(@"INSERT INTO Country(CountryId, CountryName, Region)
VALUES({0},'{1}','{2}')", dr["CountryId"].ToString(), dr["CountryName"].ToString(), dr["Region"].ToString());
temp.ExecuteQuery(query);
}
}
}
}
catch (Exception ex)
{
throw ex;
}
return(true);
}
public void CreateVendorTypeTable()
{
try
{
string query = @"SELECT name FROM sqlite_master WHERE type='table' AND name='VendorType';";
DataSet ds = temp.fillComboDataset(query);
if (ds == null || ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
query = @"CREATE TABLE [VendorType](
[VendorTypeId] [int] NOT NULL,
[VendorTypeName] [varchar](50) NOT NULL
)";
temp.ExecuteQuery(query);
}
}
catch (Exception ex)
{
throw ex;
}
}