CODEWITHSUNDEEP
c#windows-servicesasp.net-mvc-5
wajira000
wajira000

Reputation: 389

Take row count using windows service

I'm new to ASP.Net. I have a recent project need to read database using windows service. I need to check the database if record is exist before I write that record to database. I have no idea how to do that. If anyone can provide simple sample code it is great.

string strConn = ConfigurationManager.ConnectionStrings["MyDBConfig"].ConnectionString;

        SqlConnection oSqlConnection = new SqlConnection(strConn);
        oSqlConnection.Open();

        string commandText = @"SELECT COUNT (*) FROM csvs WHERE Agency='BBB'";

        using (var command = new SqlCommand(commandText, oSqlConnection))
        {
            command.ExecuteNonQuery();
        }

Upvotes: 1

Views: 712

Answers (2)

Dirk Trilsbeek
Dirk Trilsbeek

Reputation: 6033

your code is almost there. You just need the part that actually reads the result from your SQL Query. ExecuteNonQuery just executes the query but doesn't return a result (it does return the number of affected rows for UPDATE, INSERT and DELETE, but a simple select doesn't affect any rows).

this code executes the query and checks if the return value is greater than zero.

    bool recordExists = false;
    string strConn = ConfigurationManager.ConnectionStrings["MyDBConfig"].ConnectionString;

    SqlConnection oSqlConnection = new SqlConnection(strConn);
    oSqlConnection.Open();

    string commandText = @"SELECT COUNT (*) FROM csvs WHERE Agency='BBB'";

    using (var command = new SqlCommand(commandText, oSqlConnection))
    {
        int recCnt = Convert.ToInt32(command.ExecuteScalar());
        if (recCnt > 0)
             recordExists = true;
    }

note: ExecuteScalar() returns an object of type System.Object. The actual type returned depends on the query, in your case some number type depending on the OleDb driver. Convert.ToInt32() converts the return value into an int which you then can check in your code. Convert.ToInt32() is preferred to an explicit cast with (int), because Convert.ToInt32() also works on other number types. In this specific case an explicit cast would probably work exactly the same.

Upvotes: 2

user240141
user240141

Reputation:

Efficient way is to use StoredProcedure. You should learn using StoredProcedures for complex/multi line queries. Since you are new to .net and all those so in your scenario here, your code should look something like this:-

string strConn = ConfigurationManager.ConnectionStrings["MyDBConfig"].ConnectionString;

        SqlConnection oSqlConnection = new SqlConnection(strConn);
        oSqlConnection.Open();

var sql= "if not exists(Select count(id) from csvs where Agency=@agency) begin 
insert into csvs(columnA,columnB) values('abcd','efgh') end";


        using (var command = new SqlCommand(sql, oSqlConnection))
        {
command.Parameters.AddWithValue("@agency",agency);
            command.ExecuteNonQuery();
        }

Upvotes: 0

Related Questions