ASP.NET

Re: Only Save Contentpage

4-Jul-11 2:19

This won't work for the OP. There is no printing involved, only display.

I know the language. I've read a book. - _Madmatt

Shameel4-Jul-11 7:44

Shameel

4-Jul-11 7:44

My bad, I did not read the question properly, thanks for pointing out.

Linq to SQL dbml file

indian1431-Jul-11 13:19

1-Jul-11 13:19

Hi,

I have three tables in a dbml file and two Linq queries to get the data using Linqs. The first linq query gets some data from a table and then on this same collection we apply 2nd query. Eventhough after I check for the null value on the variable I get table not found exception.

So I want to check whether the table we applied the first linq has the required table or not. I am giving the code block below.

I want to check a specific table parallelDataContext.teQUTUDatas in dbml exists in database or not?

private int? GetTeQUTUData(int quarterlyUpdateJobId, int quarterlyUpdateSubscriptionId)
{
    int? tuReportBinaryDataID = null;

    if ((parallelDataContext != null) && (parallelDataContext.teQUTUDatas != null))
    {
                return parallelDataContext.teQUTUDatas.Where(d => d.QuarterlyUpdateJobID == quarterlyUpdateJobId &&
                d.QuarterlyUpdateSubscriptionID == quarterlyUpdateSubscriptionId).
                Select(a => a.TUReportBinaryDataID).FirstOrDefault();
    }
    else
                return tuReportBinaryDataID;
}

Thanks & Regards,

Abdul Aleem Mohammad
St Louis MO - USA

Philippe Mori1-Jul-11 13:56

1-Jul-11 13:56

Typically all tables should be created before using the database. Normally tables are created when the application is installed, updated or used for the first time thus a "table not found" would not occurs.

Philippe Mori

indian1431-Jul-11 14:54

1-Jul-11 14:54

Actually in my case there are two different jobs that create the tables and fill the data. But when I am showing the view, I am showing all.

So when job1 runs it will not have some tables and when job2 the same it will not create some tables.

For every run all the tables will be dropped and then created and loads the data.

In that case I want to check before hand that whether the table exists or not. If it fails I want to return null or handle the situation.

Thanks & Regards,

Abdul Aleem Mohammad
St Louis MO - USA

Philippe Mori1-Jul-11 16:01

1-Jul-11 16:01

Is there a reason to drop the table instead of simply deleting all rows ?

By the way if you have 2 independant jobs, there will always be a risk that the table get dropped between the time you make the check and the time you try to access the data.

Anyway, I would say that in this case (if you do drop the tables) the best solution is not to do the test the existance of the table but catch and handle the exception (you might check the error code or if the error message contains the name of the table and only handle the error if it is expected).

If the view run independly of the jobs, handling exception is a must as otherwise you will sometime have a bad timing where the table would be dropped just after you check its existance.

I think that you should avoid dropping and recreating the table as you will be able to avoid to deal with that problematic situation.

Philippe Mori

indian1431-Jul-11 19:12

1-Jul-11 19:12

Hi,

No I cant stop dropping and creating the tables. They are actually old and new jobs so they run. I need to handle it in good way.
I thought if I could check the db before I run the get method, I can easily pass through this without exception.
I dont want it to go till exception, before that only I want to handle using if else.

Is there any way that either by using the context object or by using the dbml table object on the context, I can check for existence of any table.

Any help is welcome. Thanks in advance.

Thanks & Regards,

Abdul Aleem Mohammad
St Louis MO - USA

Philippe Mori2-Jul-11 2:58

2-Jul-11 2:58

I don't know if Linq to SQL make this information available but you can always test it "directly" if you need to. Check online help or code to see if a method exists to check for the existance.

I think that in my case I was doing an SQL query to check the existance of columns in a table when doing database upgrade. For that, you should do a google search.

As I said, handling the exception would be in my opinion the way to handle that... as even if you check the existance of the table, there is a slim chance that the table is deleted by the job between the check and the actual use of the table.

Also checking the existance explicitly will required twice as must request to update your view except if you instead write SQL stored procedure but then you won't be able to use Linq to SQL as you do.

IEnumerable<MyData> GetData()
{
  IEnumerable<MyData> enumerable = null;
  try
  {
    var query = 
      from m in dataContext
      select m;

    enumerable = query.AsEnumerable();
  }
  catch (SQLException ex)
  {
    if (ex.SomeCheck)
    {
       // Empty enumerator
       var list = new List<MyData>();
       enumerable = list;
    }
    else
    {
       // Unknown problem --- rethrow the exception
       throw;
    }
  }

  return enumerable;
}

You might also check : http://stackoverflow.com/questions/4520209/how-to-check-if-table-exists-in-model-but-not-in-database[^]

Or

http://leedumond.com/blog/check-if-a-table-exists-in-a-sql-server-database/[^]

Or

http://stackoverflow.com/questions/167576/sql-server-check-if-table-exists[^]

But I have warn you that if the jobs and the view run asynchronously, there is a slim chance that you could have an exception even if you do the check. That percentage will depend mainly on how often jobs are run and the duration of operation. If jobs run every few seconds and check are done at a similar rate, there is an high probabily to have an exception even with a check. On the other hand, if update are done every few hours it might take year before the problem would occurs once.

The best way is to first add the exception check and test the code. If it works and you still want to add a check anyway and don't bother with the extra SQL request, you can then add that the check being confident that the catch would get the exception in case of a bad timing.

Philippe Mori

Not Active1-Jul-11 15:12

Linq to SQL is taking more memory

1-Jul-11 15:12

Perhaps you should be posting these questions to the Linq forum.

I know the language. I've read a book. - _Madmatt

indian14330-Jun-11 14:06

Re: Linq to SQL is taking more memory [modified]

30-Jun-11 14:06

Hi All,

I have created few Linq Queries using Lambda expressions in my application, and I got feedback that my Linq Queries and Lambda expressions are taking more memory. I dont know how to see that, but he told me.
Can anybody please advice me how to create Linq and Lambda expressions by consuming very less memory. I am new to the Linq and Lambda. I am giving my code below please advice me any kind of help is greatly appreciated.

Either give me idea or links to write efficient linq and lambda's or give me any alternate solution if we have other that linq and lambda.
And one more thing is that we are using Linq to SQL to connect to the database.

Below is the Code:

        public string GetQuarterlyUpdateTUCDataXml( int quarterlyUpdateJobId, int quarterlyUpdateSubscriptionId)
        {
            string strQuarterlyUpdateDeltaReport = null;            

            var quarterlyUpdateSubscription = parallelDataContext.teQUTUDatas.Where(c => c.QuarterlyUpdateJobID ==
     quarterlyUpdateJobId && c.QuarterlyUpdateSubscriptionID == quarterlyUpdateSubscriptionId).ToList();

            if ((quarterlyUpdateSubscription != null) && 
(quarterlyUpdateSubscription.Count > 0))
            {
                byte[] encryptedBytes = (from a in quarterlyUpdateSubscription
                                         join
                                             b in parallelDataContext.teBinaryDatas on  

a.DeltaReportBinaryDataID equals b.BinaryDataID
select b.Data).FirstOrDefault().ToArray();

                strQuarterlyUpdateDeltaReport = (encryptedBytes != null) ?
                  objCommon.GetDecryptedReportString(encryptedBytes) : ""; //objCommon.GetDecryptedReport(encryptedBytes); //                       
            }
            
            return strQuarterlyUpdateDeltaReport;
        }

Thanks & Regards,

Abdul Aleem Mohammad
St Louis MO - USA

Not Active30-Jun-11 15:07

30-Jun-11 15:07

indian143 wrote:
I dont know how to see that, but he told me.

Who told you there were using too much memory? How did they know? Did you ask? More memory compared to what? Is it the query or the data that is taking up too much memory?

Linq does use more memory than traditional ADO.NET but there isn't much you can do about it and the trade-off is more beneficial in ease of use, maintenance, extensibility and in some cases performance.

from a in quarterlyUpdateSubscription
join b in parallelDataContext.teBinaryDatas on  
a.DeltaReportBinaryDataID equals b.BinaryDataID
select b.Data

Since we have no idea what data is behind this an accurate assessment can't be made. However, if there are large sets involved here it will of course use more memory to store and perform the query and join. If you can reduce the size of the set it may help.

I know the language. I've read a book. - _Madmatt

modified on Friday, July 1, 2011 12:19 AM

indian14330-Jun-11 17:58

30-Jun-11 17:58

Hi,

Thanks for the reply, its not compared to any thing. But its taking 1 GB memory my architect said. And one more thing it could be data only that is taking more memory.

Now, I will put the question in different way
Can I tune my Linq and Lambda query to select less data and use less memory so that I can improve my functions performance? Below is the code.

Thanks for any kind of help, thanks in advance.

 public string GetQuarterlyUpdateTUCDataXml( int quarterlyUpdateJobId, int quarterlyUpdateSubscriptionId)
        {
            string strQuarterlyUpdateDeltaReport = null;            
 
            var quarterlyUpdateSubscription = parallelDataContext.teQUTUDatas.Where(c => c.QuarterlyUpdateJobID ==
     quarterlyUpdateJobId && c.QuarterlyUpdateSubscriptionID == quarterlyUpdateSubscriptionId).ToList();
 
            if ((quarterlyUpdateSubscription != null) && 
(quarterlyUpdateSubscription.Count > 0))
            {
                byte[] encryptedBytes = (from a in quarterlyUpdateSubscription
                                         join
                                             b in parallelDataContext.teBinaryDatas on  
 
a.DeltaReportBinaryDataID equals b.BinaryDataID
select b.Data).FirstOrDefault().ToArray();
 
                strQuarterlyUpdateDeltaReport = (encryptedBytes != null) ?
                  objCommon.GetDecryptedReportString(encryptedBytes) : ""; //objCommon.GetDecryptedReport(encryptedBytes); //                       
            }
            
            return strQuarterlyUpdateDeltaReport;
        }

Thanks & Regards,

Abdul Aleem Mohammad
St Louis MO - USA

Not Active30-Jun-11 18:18

30-Jun-11 18:18

There was no need to post the code again.

As I stated, there may be a way to reduce the size but I have no idea of the data structure or your requirements. Maybe your architect would be the one to ask

I know the language. I've read a book. - _Madmatt

dasblinkenlight1-Jul-11 3:44

dasblinkenlight

1-Jul-11 3:44

For a method returning a single string, this is rather wasteful: you bring in a list of items in memory (with a call of ToList()), then run a join, but then you throw away all but the first item of the result!

You don't need a list of teQUTUData objects in memory: all you use is their DeltaReportBinaryDataID column, so select only that column in your first LINQ query (add .Select(a => a.DeltaReportBinaryDataID) to the first query).

Next, you don't need a join in your second query: it's an in-list lookup. Rewrite your query to run a select from parallelDataContext.teBinaryDatas, where quarterlyUpdateSubscription contains b.BinaryDataID.

This should do the trick. Good luck!

indian1431-Jul-11 8:43

1-Jul-11 8:43

Hi,

Your post has helped me greatly. I really appreciate your help. I am really greatfull for this help. You not only helped me in solving the problem but also helped me in understanding the usage of select in the linq. Thats where real puzzle there I think so.
Once select is understood then Linqs become easy I think.

In all the ways this help is really appreciable. Thanks a lot again.

Thanks & Regards,

Abdul Aleem Mohammad
St Louis MO - USA

dasblinkenlight1-Jul-11 9:02

dasblinkenlight

1-Jul-11 9:02

You are welcome!

Philippe Mori1-Jul-11 14:04

1-Jul-11 14:04

Download LINQPad a free tool that will allows you to test your queries.

This tool is really helpfull to optimize your query because it will display the time it takes to executed code. Also, it is possible to see the generated SQL. Anytime the SQL seems way too complex or the number of queries is large, you should try to see it it can be improved.

http://www.linqpad.net/[^]

Also since the tool nicely display the result, it is possible to validate visually that your queries make senses. For example if hte number of returned rows is bigger than expected, a WHERE clause might be missing.

This tool is a must have. It may it easy to compare alternative queries. Also a lot of sample come with the tool and show different thing like how to do grouping, how to test NULL value in a database and more.

Sometime a simple difference like exchanging SingleOrDefault() with FirstOrDefault() can make a huge difference in the query even though the result would be the same (apart that in one case it would have been check that a single item exists.

Also it will help to spot iterative requests. That is when a query would cause a first request then repeat another sub-request for each element. Sometimes, it might be preferable to explicitly make 2 simpler requests that LINQ to SQL would be able to translate in 1 SQL request each instead of a big request that would result in mlutiple request.

Philippe Mori

LinqToExcel in win 64 bit

Shameel3-Jul-11 20:35

Shameel

3-Jul-11 20:35

LINQ queries are executed only when results are required to be used in the code.

ToList()

and

ToArray()

force the queries to execute since the results are to be stored in the respective list and array. If the query is large enough, then it sure will consume huge memory. As dasblinkenlight already suggested, rewrite your query to select only the rows and columns that you actually require instead of loading the entire table and reading the required row/column.

Dhyanga30-Jun-11 8:10

Dhyanga

30-Jun-11 8:10

Hi,

I was using LinqToExcel.dll file before in my win 32 bit machine and was working fine. Now when i try my project(asp.net) in win 64 bit, it gave error because of this LinqToExcel.dll file. Is there any equivalent file for LinqToExcel.dll file for win64 bit? Does anybody have any ideas or suggestions??

suchita

Not Active30-Jun-11 8:13

30-Jun-11 8:13

SayamiSuchi wrote:
it gave error

How many times do we hear this? And the the response is the same.

What error!?! If you want help you must provide sufficient details.

I know the language. I've read a book. - _Madmatt

Dhyanga30-Jun-11 8:26

Dhyanga

30-Jun-11 8:26

in my local machine, since its 32 bit, i can't see the error. But because of that file, it is not running in 64 bit machine..

suchita

Parwej Ahamad30-Jun-11 9:11

Parwej Ahamad

30-Jun-11 9:11

Post here your exception message.

Parwej Ahamad
ahamad.parwej@gmail.com

Not Active30-Jun-11 9:39

30-Jun-11 9:39

If you can't see an error then how can you be sure it is this file? What debugging or diagnostics steps have you taken to isolate this file as the problem?

I know the language. I've read a book. - _Madmatt