Click here to Skip to main content
15,509,151 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, I have a really big project with more than 200 Entity classes and I am doing all the database access via direct connection.
Good thing is that I kept my database access layer separate in a project and that project would be referenced from WCF service.

C#
public bool Insert()
       {
           try
           {
               dc = new LinqDataContext1();
               dc.Connection.Open();
               using (dc.Transaction = dc.Connection.BeginTransaction())
               {
                   dc.InsertPayee(1, PayeeID, PayeeName, StreetAddress, City, State.StateID, ZipCode, PhoneNumber, EmailAddress, Notes, (Carrier == null ? null : Carrier.CarrierID), IsActive, GlobalModels.Register.RegisterID);

                   dc.Transaction.Commit();
                   return true;
               }
           }
           catch
           {
               //dc.Transaction.Rollback();
               throw;
           }
           finally
           {
               if (dc.Connection.State == ConnectionState.Open)
               {
                   dc.Connection.Close();
               }
           }
       }

public LinqDataContext1(string p_ConnectionString = "")
        {
            if (string.IsNullOrWhiteSpace(p_ConnectionString) == false)
            {
                connectionString = p_ConnectionString;
            }
            else
            {
                connectionString = GlobalModels.conString;
            }
            Connection = new MySqlConnection(connectionString);
           
        }


Currently every client application keeps it's database connection string in global variable but in case of WCF service I would have to send a new connection string every time I call Payee.Insert(). My question is that could it cause any problem in future when there are lots of clients calling same service method and multiple database queries are executed and what are the things I should be careful about when working in WCF?

What I have tried:

I have added reference to existing database access project to WCF service application.
Posted
Updated 20-Apr-16 4:46am
Comments
F-ES Sitecore 19-Apr-16 10:41am    
What does each client need their own connection string? Can't you use the same string for all clients and store that one in your WCF project?
Qadeer Ahmed Khan 19-Apr-16 11:01am    
There is a different database for each client.
F-ES Sitecore 19-Apr-16 11:57am    
Rather than passing the whole connection string you're better holding the possible strings somewhere your WCF code can access then making decisions based on which string\database to use based on the client. How you identify the calling client depends on if you use authentication.

1 solution

How do you logically have each database separated? Is it by name as in customer name or customer number? You could pass the customer name/id with each wcf method call and then generate the connection string, you definitely should not pass a connection string to your wcf method.
 
Share this answer
 
Comments
Qadeer Ahmed Khan 21-Apr-16 8:55am    
Each Customer is assigned a unique id like CSP644 which can be sent for each service call. These databases are deployed upon different servers and there is a central database which keeps record of connection information. e.g
CSPID = CSP644
Host = SomeHost
Database = DBName
Password = ThePassword

So I should load a SQL Server Compact database table by calling a web service which would return data from central database and keep them locally. Clients would send their CSPID and other details like Email and Password for authentication.

Also should I keep a connection open for each database or close them after executing query as I am currently doing. Also is it possible to handle requests asynchronously and what would be the best way to do it?
Jason Henderson 22-Apr-16 14:11pm    
Generally, you would not store authentication info (email, pwds, etc) in session variables. You take that info, authenticate with it, and then create a token with their CSPID and other identity info which could include how to connect to the database. The token could be an encrypted JSON string or XML (when passing it around) that you can decrypt on the server side to easily get out the info you need.

As far as keeping the connection open, I think you need to figure that out by your needs. I would say close it for every query, but it you need it multiple times in the same page or server side code, then cache it if instantiating the connection is too slow. You could try connection pooling as well.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900