Using interface to connect to SQL Server database

Question

interface IDbInteraction
{
    int GetValueAsInt(string sql);
}

class SQLDbInteraction : IDbInteraction
{
    private string _connStr;

    public SQLDbInteraction (string connStr)
    {
         _connStr = connStr;
    }

    public int GetValueAsInt(string sql)
    {
        int value;

            using(SqlConnection conn=new SqlConnection(_connStr))
            {
                SqlCommand command = new SqlCommand(sql, conn);
                conn.Open();
                value = Convert.ToInt32(command.ExecuteScalar());

            }


        return value;
    }
}

This is a simple example on how to program an interface when access to a SQL Server database is needed.

It was offered to me as advice to replace an old code that I've presented in a question.

So ..., as I am fresh developer and new to the interface concept / approach / methodology, and as I don't really connect to the term Repository I've renamed that...

Could you please give a little broad example on how would you use these two : class & interface above ?

And also if you could please offer alternative to IRepository (original name)

What would you replace it with (if you don't mind)?

As the point of this post, is about moving from the unsafe and kinda obsolete technique I was using, and now while trying to move on to "next generation" coding methodology,

I could use your help on this sample code to learn usage.

update

what i did now was researching the web.. and still, i have this issue in question :

is the way to call/invoke GetValueAsInt("select aColumn ...")

now...when having an interface added to the "equation" ,

did it change anything as far as the invocation act cares ?

do you still just call GetValueAsInt() as u did till now?

is it true that in this manner, nothing realy changed.. as for the actual usage

do you now call

IDbInteraction.GetValueAsInt("select aColumn from...")

or

SQLDbInteraction.GetValueAsInt("select aColumn from...")

or calling both ways is now availble ?

The Unculled Badger · Accepted Answer · 2012-12-08 19:45:26Z

1

Interface based programming is basically used to define a contract that a class must adhere to when being used.. Since you are new to this sort of terminology I would read up on Object Oriented Programming and Design Patterns where you will find out about implementations of the repository pattern.. IRepository is just an interface name you can name an interface whatever you like. Repository is a design pattern which is quite commonly used in business logic to data access layers. I am not sure that I would define this code as unsafe and obselete but you may decide to implment this logic using a design pattern and using object oriented techniques.

answered Dec 8, 2012 at 19:45

The Unculled Badger

7604 silver badges18 bronze badges

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

2 Comments

LoneXcoder Over a year ago

i did not mean to say that code above is obsolete it is the other way around, the code I was using is the one that's obsolete, then i've been offered by somone to use the code above as an alternative to my code , as my code was prone to injections

LoneXcoder Over a year ago

@T.U.B ... I've Updated my question, can you try and answer ?

LoneXcoder · Accepted Answer · 2012-12-08 23:56:06Z

1

ok as i was expecting an answer... while still searching i came to conclusion

this is what i am going to implement

(say subject of application is colors)

IDbInteraction colorsData = new SQLDbInteraction("server=(local);Initial Catalog=...");
int colorId= colorsData.GetValueAsInt("SELECT colorId FROM colors WHERE name='jade'"));

as i will continue this search for the correct answer .if you see something wrong in my implementation please post your answer , if it is correct could somone please just upvote or something ?

edited Dec 8, 2012 at 23:56

answered Dec 8, 2012 at 23:13

LoneXcoder

2,1837 gold badges40 silver badges78 bronze badges

3 Comments

The Unculled Badger Over a year ago

If you get the correct data from your query then I can't really see very much wrong with what you're doing. No point in overcomplicating what seems to be a very simple query. I may be tempted to put the DB string in a config file so it can be changed easily and the query in a const string but other than that it looks ok.

LoneXcoder Over a year ago

@TheUnculledBadger cheers mate I am now with already 2 classes ( i have many of them as helpers) moved to use interfaces, i guess i'll see the point later on while using it (as to why exactly do they exist... besides microsoft leting you feel in another way you can not leave "school" and relationsheep with new products)

The Unculled Badger Over a year ago

You will see the point when you start unit testing the functionality. Using interfaces will allow you to much more easily unit test any code.

Collectives™ on Stack Overflow

Using interface to connect to SQL Server database

update

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

update

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related