Mixing with Linq and Traditional SqlConnection

Lee posted on Sunday, October 12, 2008

Hi All,

With CSLA3.5, I'm always using Linq DataContext for CUD with stored procedure and Linq2SQL for data retrieval same with PT sample. But for dynamic pivot stored procedure, I can't find a way to use Linq , so I used CSLA 2.x way to get result which produced as Dataset as follows;

             // Method which Win form client calls to get result 

              using (StatusBusy busy = new StatusBusy(true, MessageNames.QueryingData, WorkItem))
              {
                    // call method to retrieve dataset
                    result[0] = PeakCommands.GetPeakMainData(PeakCriteria, itemId);
               }

              // Command Object's Dataportal_execute which returns DataSet via Dynamic Pivot SP

             protected override void DataPortal_Execute()
            {

                 // various parms insert goes here to construct dynamic sql                 .

                 .

                 .

                // open sql connection as a traditional manner

               // connection string is same with Linq DataContext
                using (SqlConnection cn = new SqlConnection(NP.DalLinq.Database.ConnectionString))
                {
                    cn.Open();
                    using (SqlCommand cm = cn.CreateCommand())
                    {

                         .

                         .

                        // get result

                       SqlDataAdapter adaptor = new SqlDataAdapter(cm);
                        DataSet ds = new DataSet();
                        adaptor.Fill(ds);
                        _result = ds.Tables[0];

                   }

At first, project was deployed with n-Tier model  with Application server and  runs fine,

however, due to client's environment change, project should be ported to Client/Server model.

Thanks to the powerful architecture of CSLA, it's been so easy to change a few letter in config file.

My dev machine runs fine under test, and I deployed in client's P.C, but I found above funtion can't

run.

When I test with another P.C which above function failed, I found via SQL Profiler that he can't

reach in SQL DB server, I guess even connection open has not done.

But what makes me crazy is my dev p.c and another dev P.C in my office runs fine.

Though I depolyed some known P.C in another area, found failure.

If there is anyone who has same errors, pls enlighten me,

or let me know which is fine to call dynamic pivot sp.

 

RGDS

HK.Lee

 

 

 

 

 

 

 

Lee replied on Monday, October 13, 2008

I replyed myself<s>.

It was my mistake to set db configuration in Linq diffrent with app configuration.

I confused DataContext connection string comes from Linq DAL's configuration but from

app configuration setting.  How pitty this simple ignorance spend so many hours.

Anayway thank you for this wonderful framework.

 

RGDS

HK.Lee