To use sqlite3 module, you must first create a connection object that represents the database and then optionally you can create a cursor object, which will help you in executing all the SQL statements. You do not need to install this module separately because it is shipped by default along with Python version 2.5.x onwards. It provides an SQL interface compliant with the DB-API 2.0 specification described by PEP 249. SQLite3 can be integrated with Python using sqlite3 module, which was written by Gerhard Haring. String kal=(10).In this chapter, you will learn how to use SQLite in Python programs. Iii) "SELECT * FROM ExcelData WHERE substr(DueDt,7)||substr(DueDt,4,2)||substr(DueDt,1,2) between 'aj' and 'kal'" Ii) "SELECT * FROM ExcelData WHERE substr(DueDt,7)||substr(DueDt,4,2)||substr(DueDt,1,2) between date('now') and date('now', '+10 days')" I've tried writing queries in 3 different ways shown below i) "SELECT * FROM ExcelData WHERE substr(DueDt,7)||substr(DueDt,4,2)||substr(DueDt,1,2) between strftime('%Y-%m-%d','now') and strftime('%Y-%m-%d','now','10 days')" Can your code be modified for that ?Īlso, update on doing the date filter as mentioned on my original post/question, I was able to get the desired output using the below query "SELECT * FROM ExcelData WHERE substr(DueDt,7)||substr(DueDt,4,2)||substr(DueDt,1,2) between '20220228' and '20220325'"īut when I try to input the startdate & enddate dynamically like say, filter dates between today and next 10 days I don't get any data. However I was looking for a solution without using Entity Framework and only using. Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this Liu-MSFT Thanks for posting your answer. If the response is helpful, please click " Accept Answer" and upvote it. Private void filter_Click(object sender, RoutedEventArgs e) SQLiteCommand sqlCom = new SQLiteCommand("select * from MyData where ( (substr(DueDate,1,2) between '01' and '25' ) and (substr(DueDate,4,2)='03') and (substr(DueDate,7) = '2022') ) ", m_dbConnection) Int occurrences = columnNames.Count(x => x.Equals(columnName)) ĬolumnName = columnName + "_" + occurrences Īdd the following code to the above code. Public static DataTable ExcelPackageToDataTable(ExcelPackage excelPackage)įoreach (var cell in worksheet.Cells)ĬolumnNames.Add("Header_" + currentColumn) ĭt.Columns.Add("Header_" + currentColumn) SQLiteDataAdapter sda = new SQLiteDataAdapter(sqlCom) ĭg.ItemsSource = ds.Tables.DefaultView SQLiteCommand sqlCom = new SQLiteCommand("Select * From MyData", m_dbConnection) SQLiteCommandBuilder cmdBuilder = new SQLiteCommandBuilder(sqliteAdapter) SQLiteDataAdapter sqliteAdapter = new SQLiteDataAdapter("SELECT * FROM MyData", m_dbConnection) SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection) įileInfo existingFile = new FileInfo(xlFile) ĮxcelPackage package = new ExcelPackage(existingFile) String sql = "create table MyData (Party varchar(20), BillNo varchar(20), BillDate varchar(20) ,Amount varchar(20) ,DueDate varchar(20),PaidOn varchar(20)) " SQLiteConnection m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite") SQLiteConnection.CreateFile("MyDatabase.sqlite") You could try to refer to the code below. I've even tried doing the date filter like SQLiteCommand command = new SQLiteCommand("SELECT * FROM ExcelData WHERE DueDt BETWEEN '' AND ''", conn) but that doesn't work either.Īlso, the amount column does not show 2 decimal points when I see it in Datagrid. SQLiteDataAdapter adap = new SQLiteDataAdapter(command) ĭataTable dt = new DataTable("ExcelData") SQLiteCommand command = new SQLiteCommand("SELECT * FROM ExcelData WHERE DueDt >= date('now') AND DueDt < date('now','100 days')", conn) My first question is whether the above method can be made faster/more efficient and less error prone like SQL injection issues and/or database read-write, sudden loss of connection to database error ?įurther, when I try to filter the database by date filter like below, I get a blank Datagrid : using(SQLiteConnection conn= new Source="+Path.GetFullPath("./TestDB.db")+" ")) I created a database TestDB.db with table ExcelData using DB browser for SQLite as below Using (ExcelPackage excelPackage = new ExcelPackage(new System.IO.FileInfo(xlFile), false))ĮxcelWorksheet worksheet = () įor (int i = +1 i row.Field(5)) Right now I'm using the below method(using EPPlus for workbook manipulation): public void Excel2DB() I want to import data from a excel workbook/worksheet to a SQLite database table.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |