How to display records in datagridview in case of currentrea

How to display records in datagridview in case of currentreading in excel is less than current reading in invoice table for same unitcode

I have excelsheet as following

unitcode CurrentReading
12 2000
14 4500

and I have table Invoice in database in sql server 2012 has following fields

serial unitcode CurrentReading year month
1 12 1000 2018 3
3 14 5000 2018 3

so that how to display unit code 14 in datagridview .

Actually what i need is when currentreading for unit code in excel sheet is less than currentreading

in table invoice display the values in datagridview .
the formula as following
if(currentreading in excel for unitcode < currentreading in table invoice for unitcode
display these wrong values in dat gridview as
unitcode currentrading
first function get data from excel

public System.Data.DataTable Showdataprint()
{
string connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", txtpath.Text);
OleDbConnection con = new OleDbConnection(connectionString);
con.Open();
string str = @"SELECT [UnitCode],[CurrentMeterReading] FROM [Sheet5$] ";
OleDbCommand com = new OleDbCommand();
com = new OleDbCommand(str, con);
OleDbDataAdapter oledbda = new OleDbDataAdapter();
oledbda = new OleDbDataAdapter(com);
DataSet ds = new DataSet();
ds = new DataSet();
oledbda.Fill(ds, "[Sheet5$]");
con.Close();
System.Data.DataTable dt = new System.Data.DataTable();
dt = ds.Tables["[Sheet5$]"];
return dt;
}
for selecting currentreading from Invoice Table
public System.Data.DataTable GetDataPreviousMonthes(string UnitCode)
{
sqlquery = @"select top 1 CurrentMeterReading,RequiredAmount,CurrentReadingDate,LastReadingDate,UnitCode from WAHInvoice where UnitCode=" + UnitCode + " order by year,Serial desc";
System.Data.DataTable tb = DataAccess.ExecuteDataTable(sqlquery);
return tb;
}
public static DataTable ExecuteDataTable(string sql, DbParameter[] dbprmParameters = null)
{
return ExecuteDataTable(sql, null, dbprmParameters);
}
public static DataTable ExecuteDataTable(string sql, IDbConnection dbConnection, DbParameter[] @params = null)
{
if (sql == "") return new DataTable();
DataSet ds = new DataSet();
#region New Connection
#endregion
#region Old Connection
lock (synObj)
{
sql = AnalyizeBooleanFields(sql);
cmd.CommandText = sql;
cmd.Parameters.Clear();
if (@params != null)
{
for (int i = 0; i < @params.Length; i++)
{
cmd.Parameters.Add(@params[i]);
}
}
if (dbConnection == null)
{
if (WithTransaction)
dbConnection = BeginTransaction();
else
dbConnection = InitializeConnection();
}
if (dbConnection.State != ConnectionState.Open) dbConnection.Open();
if (WithTransaction) cmd.Transaction = _transaction;
cmd.Connection = dbConnection;
IDbDataAdapter dbCurrentDataAdapter = InitializeDataAdapter();
dbCurrentDataAdapter.SelectCommand = cmd;
dbCurrentDataAdapter.Fill(ds);
if (!WithTransaction) dbConnection.Close();
}
return ds.Tables[0];
#endregion
}

Now How to compare currentreading from excel and currentreading from invoice table
and if currentreading in excel is less than invoice table
show record in datagridview
as example above
unitcode CurrentReading
14 4500

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Prateek Singla8y ago
Make oledb connection
There are two methods:-
1) Just read the values from excel column and compare it and according to and make dataset of result .
https://www.aspsnippets.com/Articles/Read-and-Import-Excel-File-into-DataSet-or-DataTable-using-C-and-VBNet-in-ASPNet.aspx

2) Change make a table of values from excel sheet compare the values using stored procedure and bind the data and according to me 2nd one better and easy to implement.

https://www.aspsnippets.com/Articles/Read-and-Import-Excel-Sheet-using-ADO.Net-and-C.aspx

Note: One thing more also install database access engine for oledb connection.

if any queries let me know :)
+1