2

I am working on a .net win forms project with SQLite as backend.I want to create some Stored procedures to be used form my project.These SP's would generally take in some parameter and after querying the database will return some values etc. But SQLite doesn't support Stored procedures. My question is are there alternatives to sp's or how can i run multiple queries taking some inputs and giving some outputs using c# ...Please advise in this regard.Thanks in advance.

gomesh munda
  • 838
  • 2
  • 14
  • 32

1 Answers1

2

SQLite does not have stored procedures or anything that would be the same.

Maybe I don't understand the question, but you could build a query string based on user input. Something like this example using check boxes to build an SQL query, and returns results respectively:

ArrayList fields = new ArrayList();
fields.Add("id");
if (CDomain.Checked)    { fields.Add(", domain"); }
if (CCPU.Checked)       { fields.Add(", cpu_speed"); }
if (CIP.Checked)        { fields.Add(", pc_ip"); }
if (CLastUser.Checked)  { fields.Add(", last_user"); }
if (CLoginDate.Checked) { fields.Add(", last_logon"); }
if (CUsers.Checked)     { fields.Add(", users"); }
if (CMonitors.Checked)  { fields.Add(", monitors"); }
if (CPrinters.Checked)  { fields.Add(", printers"); }
if (CPCName.Checked)    { fields.Add(", pc_name"); }
if (COS.Checked)        { fields.Add(", os"); }
if (CBit.Checked)       { fields.Add(", bitversion"); }
if (CPrograms.Checked)  { fields.Add(", programs"); }
if (CLicense.Checked)   { fields.Add(", license_key"); }
if (CCPU.Checked)       { fields.Add(", cpu_speed"); }
if (CRAM.Checked)       { fields.Add(", ram"); }
if (CAdapter.Checked)   { fields.Add(", adapter_speed"); }
if (CHardDrives.Checked){ fields.Add(", drives"); }

dataBase db = new dataBase();
try {                
    ArrayList v = db.queryDB(query, fields);
    if (v.ToArray().Length == 0) {
        MessageBox.Show("No results.");
    }
} catch (Exception er) { 
    MessageBox.Show("Somethign went wrong in the search function. " + er.ToString()); 
}

ReportViewer report = new ReportViewer();
report.Show();
this.Close();

This sounds like what you might be looking for, it seems like you want a dynamic SQL statement more than stored procedures.

Arsen Khachaturyan
  • 7,904
  • 4
  • 42
  • 42
Steve Byrne
  • 1,320
  • 1
  • 16
  • 29
  • Thanks Steven for your code snippet. Actually i want to execute multiple queries and return some values from those queries,,like begin SELECT COUNT(*) INTO answered FROM tblcomputer_t_questions_r WHERE t_id=tID AND mem_id=mId AND ckt_quest_answered_s !=0; SELECT COUNT(*) INTO not_answered FROM tblcomputer_t_questions_r WHERE t_id=tID AND mem_id=mId AND ckt_quest_notanswered_s !=0; end – gomesh munda Dec 30 '15 at 23:06
  • 1
    I want the 'answered ' and 'not_answered' values to be returned to my code – gomesh munda Dec 30 '15 at 23:08
  • So you're looking to select 2 fields (columns) and return them at the same time to the user? So if we had correct answers and incorrect answers they could see both? – Steve Byrne Dec 30 '15 at 23:09
  • yes..something like that – gomesh munda Dec 30 '15 at 23:10
  • Using C# I would look at string builder or a list to build the data results before returning them. You might have to get a bit creative but you could count the results to know when one ended and the next set starts, or put in some sort of delimiter like an entry in the list that says "Changing" then delete it before giving the user the results. If you elaborate on your goal a bit more I might be of more help – Steve Byrne Dec 30 '15 at 23:15
  • I am building an exam software where users can give exams on various subjects like computers maths etc.Suppose the user is given 50 questions from computer section in the exam, then after attempting a question a panel would show him/her the status of number of questions completed so far and the number of question remaining to be attempted.Hence i need multiple queries to execute and get multiple values from database – gomesh munda Dec 30 '15 at 23:22
  • Unless I am missing the core idea here couldn't you just query how many questions there are available and then have a counter that removes 1 every time he/she submits an answer? Is the test dynamic, so for example on the accuplacer exams if you get one right you keep going, if you get to many wrong it ends the test, is that the idea? – Steve Byrne Dec 30 '15 at 23:48
  • Yes i can always do that...Thanks steven – gomesh munda Dec 31 '15 at 00:07