C# and T-SQL command-line Utility

Question

Group,

Part 1: I'm currently working on a command line utility that can take args and update a local database. The only issue I have is once i established a "Data connection"..how can I use those args for queries and searches.

For example: ~//arrInput.exe "parm1" "pram2" "pram3"

Part 2: I would like to take in command line args and use them as input parms for a "stored proc". Once finished execution....used the same inputs crate a log file.

For example output file:

mm-dd-yyyy hh:mm:ss - pram1,pram2,...

pram1: updated/failed

pram2: update/failed

Thanks,

Chad

What code have you gotten so far - can you post it? What problems are you having? — Oded
– Oded, Commented Jun 17, 2010 at 18:44
Are you aware of the sqlcmd utility? msdn.microsoft.com/en-us/library/ms162773.aspx — P.Brian.Mackey
– P.Brian.Mackey, Commented Jun 17, 2010 at 18:46
sorry guys for the miss understanding....what i'm trying to do is write a .exe and make a log file. use those prams as inputs and then log the results. — AAH-Shoot
– AAH-Shoot, Commented Jun 17, 2010 at 20:11

Community · Accepted Answer · 2017-02-08 14:13:27Z

2

Here is the SQL end of things, as far as making an output file, thats pretty basic so Ill leave that code up to you. There are other options for executing the query, depends on what you need in your implementation.

edit Can you be a little more specific in what you are looking for? Ill add the code for making a log as well I suppose and write out everything.

edit 2 For an accdb file you will need to use an OleDB connection not a SQL connection as shown in this diagram. I have edited the code below to work with an accdb file. I am not as familiar with this so I can't promise it will work 100% but the only issues should be syntax. By the way none of the inputs are sanitized and it may be better to parametrize the inputs, but since it sounds like you are running this yourself it shouldnt be necessary. If this answers your questions please mark as accepted :)

using System.Data.OleDB;
using System.IO;

static int Main(string[] args)
{
    //Make a list to hold your params
    List<string> paramList = new List<string>();

    //Populate the list based on args
    for (int i = 0; i < args.Length(); i++) 
    {
        paramList.Add(args[i]);
    }

    //Create a new oledb connection
    string path = "c:\\yourfilepath\\file.accdb";
    OleDbConnection myConnection = 
        new SqlConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" +
        path + "';");
    myConnection.Open();

    //Run the stored proc on each param and output result to file
    using (System.IO.StreamWriter file = new System.IO.StreamWriter(@"filepath\filename"))
    {
        foreach (string param in paramList)
        {
            //myCommand.CommandText is the query you want to run against your database
            //i.e. the stored proc
            OleDbCommand myCommand = myConnection.CreateCommand();
            myCommand.CommandText = "stored_proc " + param;
            OleDbDataReader myReader = myCommand.ExecuteReader();
            if (myReader.HasRows)
            {
                file.WriteLine("pass: " + param);
            } 
            else 
            {
                file.WriteLine("fail: " + param);
            }
            myReader.Close();
        }
    }

    myConnection.Close();
}

edited Feb 8, 2017 at 14:13

CommunityBot

11 silver badge

answered Jun 17, 2010 at 19:22

FlyingStreudel

4,5044 gold badges36 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

AAH-Shoot Over a year ago

Thanks for the edit.....this will open up a lot more functionality to my program.

AAH-Shoot Over a year ago

all i have to do now "i think"...is crate a suitable log file that outputs the information i won't.....

FlyingStreudel Over a year ago

In the 'if' statement with myCommand.ExecuteNonQuery() it is writing to a file that will end up looking like: pass: param1 pass: param2 fail: param3 etc... If thats not what you need you can edit the strings :) If this is a suitable solution wanna mark it as accepted? :D

FlyingStreudel Over a year ago

Oops stupid formatting... the pass: param1 stuff will each be on a new line.

AAH-Shoot Over a year ago

FlyingStreudel or group...can this logic work for ".accdb" files as well. If so. How?

Collectives™ on Stack Overflow

C# and T-SQL command-line Utility

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related