Making Calculations In A Business Layer Vs Sql Server

Jan 29, 2008

Lead information fields (name, address, etc.)

 

promo_ID: the id of the web form that site visitors filled out to send us their information

PPC_source: If this lead is a result of a Pay-per-click campaingwe capture the name of the search engine

Status_ID: Shows if the lead has been contacted, quoted, sold, bad lead, etc.

I was asked to come up with a summary report calculating the number of leads under each status ID - for each PPC_source

Example:





 


Total leads   |   


Google   |   


Yahoo



Status = pending


 


 


 



Status = contacted


 


 


 



Status = quoted


 


 


 



Status = Sold


 


 


 

Looping over each record (12,000 and counting) to get the sums of each status for each pay-per-click campaign takes a while (not all campaigns / status are listed above).

The question: Is it better to retrieve all records in a dataset and do the math in the business layer or is there a way to do the math in a SQL Procedure, or function.

Thanks.

View 2 Replies


ADVERTISEMENT

Should SQL Server Act As A Business Layer In The 3-tier Application Layout.

Mar 7, 2007

Hello Everyone

I'm looking for outside opinion on how to implement the business layer of an application. The straight forward idea I know of would be to develop a component that encapsulates all the business rules under objects. Client applications would access this component, and the business component would talk directly to the database. This was great for me when I used Access databases.

Now I have been introduced to SQL server and the stored procedures. I think stored procedures are one of the best features because these procedures can enforce rules as sort of a "last line of defence". So this is where my question comes in. Where would the best place be to implement the business layer? Through a component, such as XML services that allow other clients to access the business rules OR stored procedures.

I do understand that components/dlls provide MUCH more flexibility then stored procedures.

But, if I do take the component route:

- Other then SQL queries that get called multiple times, is there any use for stored procedures?
- For some case scenarios such as web sites that access a Business Layer, should the business layer use ONE sql server login/role to access the data or create a sql login/role for each major segment/service? E.g. one login for the accounting side, one login for the
web site services, one side for HR and the business layer will have to decide which one to use depending on the service requested.
- Would it be in the best interest of security that I allow the business components to send SQL queuries such as Insert/Delete/Update? I loved the idea of stored procedures because if for some reason, someone steals the business layer userId/password, they are only exposed to the assigned stored procedures and cannot do "Delete From Table". Unfortunately, this practice has led to an abundance of stored procedures (the list is HUGE).

If I do take the Sql stored procedure route:

- If someone steals any user Id/password, they will only be exposed to the assigned stored procedures and THOSE stored procedures contain the business rules. Thus the damage can be minimized.

- If some developer makes a mistake down the road or forgets some sort of business aspect (and is not going through the business layer), again, stored procedures would offer a last "line of defense". Lets face it, there are some applications that do not necessary require a business layer

I'd like to get everyone's opinion on how they partition the business rules with SQL server? Do you use SQL server stored procedures to only enforce formating rules (such as upper case, lower, etc) while the business rules are stored in a component?

Do you use stored procedures to enforce all business rules? None?

Thank you for your time,

Adrian

View 7 Replies View Related

Installing VS2005 And Business Intelligence Report Projects On A Vista Business Workstation

Oct 23, 2007

Does anyone have a successful prescribed sequence for installing VS2005 and Business Intelligence Reports Projects on a Vista Business workstation to be used to create reports for a server?

I've looked through everything I can find here and I don't seem to see a clear solution without a lot of trial and error.

Fact is, I've not been successful getting just the reports to install on a plain XP box. Of course, the report creation looks fine on the server but I don't want to work directly on the server.


Thank you

View 1 Replies View Related

Synchronization Of Business Contacts In Outlook With Small Business Accounting

Jun 10, 2006

Can anyone take me through synchronization of contacts within Business Contacts Outlook into Microsoft Small Business Accounts?

I run a stand alone PC with NO network. When SBA came SQL was also installed. Apparently you can synchronise Contacts within Business Contacts with SBA but both SBA & Outlook should work through the same SQL server.

Has anyone tried this?

Can someone walk me through the process?

Thanks

Debbie

View 1 Replies View Related

Calculations In SQL Queries Using SQL Server Compact

Feb 1, 2008

Hi Folks,

I am looking for some help on how to do a calculation for each row returned in a query.

For example, I have the following query:

Select Name, Supplier, Colour, Cost, [Cost Inc Vat] from products;

The idea is that Cost and a variable, @vat included in part of the SQL statement (I assume), will be be used to calculate the value of the column [Cost Inc Vat] for each row, but I dont know how to do this.

Is is possible in SQL Server Compact and how can it be done?

Many thanks,

Morrislgn

View 3 Replies View Related

Multi-user Access Through A Data-access Layer/remoting Server

Oct 30, 2007

Hi guys,

I've been developing desktop client-server and web apps and have used Access and SQL Server Standard most of the time.
I'm looking into using SQL CE, and had a few questions that I can't seem to get a clear picture on:

- The documentation for CE says that it supports 256 simultaneous connections and offers the Isolation levels, Transactions, Locking, etc with a 4GB DB. But most people say that CE is strictly a single-user DB and should not be used as a DB Server.
Could CE be extended for use as a multi-user DB Server by creating a custom server such as a .NET Remoting Server hosted through a Windows Service (or any other custom host) on a machine whereby the CE DB would run in-process with this server on the machine which would then be accessed by multiple users from multiple machines??
Clients PCs -> Server PC hosting Remoting Service -> ADO.NET -> SQL CE

- and further more can we use Enterprise Services (Serviced Components) to connect to SQL CE and further extend this model to offer a pure high-quality DB Server?
Clients PCs -> Server PC hosting Remoting Service -> Enterprise Services -> ADO.NET -> SQL CE

Seems quite doable to me, but I may be wrong..please let me know either ways

Thanks,
CP

View 3 Replies View Related

SQL Server Admin 2014 :: Transaction Log And TempDB Calculations

Mar 13, 2014

Is there a formula for calculating how expensive a transaction will be in terms of disk space used before its run. I dont want it accurate to the MB, but rough enough so I can determine how much additional space to assign to a transaction log or SAN volume.

Currently we're reindexing ~25billion rows, nothing too wide, say 12 columns consisting of 1 varchar(50) and the rest ints, bits and money.Roughly speaking if I reindex the clustered index on an int indetity, (with sort_in_tempdb) how would I calculate the the disk space used?

View 2 Replies View Related

Floating Point - Precision Of Arithmetic Calculations On Server

Dec 4, 2013

I am trying to understand why SQL Server gives me significantly lower precision than many of the other sources that have tried when using the POWER function. My environment is 2008 R2 SP2 (10.50.4000.0 X64, Standard edition) on Windows 2008 X64 SP1

DECLARE @x FLOAT(53) = 1.0004;
DECLARE @y FLOAT(53) = 1.0/12.0;
SELECT POWER(@x,@y)-1; -- Answer: 3.33270904724348E-05
GO
DECLARE @x FLOAT(24) = 1.0004;
DECLARE @y FLOAT(24) = 1.0/12.0;

[URL] ....

Answer: 3.33272237835747E-05

I also tried using Windows Calculator.Answer:3.3327223783495255846580902358195e-5

And an online "high-precision" calculator from [URL] ...

Answer: 3.332722378349525584658E-5

The best SQL results compare only to the fourth digit with other results. Everything else agrees to the 10th or 12th digit.

Is the precision of arithmetic calculations on SQL Server that bad, or am I doing something wrong here? Is there another more precise alternative?

I did the following. The results speak for themselves

DECLARE @x FLOAT(53) = 1.0004;
DECLARE @y FLOAT(53) = 1.0/12.0;
DECLARE @z FLOAT(53) = POWER(@x,@y)-1; -- Answer: 3.33270904724348E-05
SELECT POWER((1+@z),12); -- 1.00039999839968

-- using results from other sources (c#, windows calc, casio.com)
SELECT POWER((1+3.33272237835747E-05),12) -- 1.0004
SELECT POWER((1+3.3327223783495255846580902358195e-5),12)-- 1.0004
SELECT POWER((1+3.332722378349525584658E-5),12) -- 1.0004

It is not important what I am trying to do, but in case it will work, I am trying to calculate the monthly return for a 90-day T-bill given the compounded annual return. x is the annualized return.

View 7 Replies View Related

SQL Server 2014 :: Using Newly Created Column For Doing Further Calculations In SAME Query

Apr 12, 2015

I am new to SQL Programming. I am learning the basics. I am trying to create a simple query like this -

SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,

-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6

FROM Table_1
First 3 Columns are available within the Original Table_1
The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.

Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.

I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case, or am I doing something wrong here ?

If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution for me ?

I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me. And now I have to do various calculations that involve making use of both these type of columns.

View 8 Replies View Related

SQL Server 2012 :: Date / Time Calculations For Work-plan Times?

May 31, 2015

I have a table that stores working hrs, such as

RecID,StaffID,StartDate,EndDate
17,969,2015-05-18 00:00:00.000,2015-05-18 06:00:00.000
18,969,2015-05-18 18:00:00.000,2015-05-19 06:00:00.000
19,969,2015-05-19 18:00:00.000,2015-05-20 06:00:00.000
20,969,2015-05-20 18:00:00.000,2015-05-21 06:00:00.000
21,969,2015-05-21 18:00:00.000,2015-05-22 06:00:00.000
22,969,2015-05-22 18:00:00.000,2015-05-23 06:00:00.000
23,969,2015-05-23 14:00:00.000,2015-05-24 08:00:00.000
24,969,2015-05-24 22:00:00.000,2015-05-25 00:00:00.000

So working times can go over midnight, there can be more than one working period in a day etc.

For this staff member the summary of the weeks work will be

18/05/2015 - 12 hrs
19/05/2015 - 12 hrs
20/05/2015 - 12 hrs
21/05/2015 - 12 hrs
22/05/2015 - 12 hrs
23/05/2015 - 16 hrs
24/05/2015 - 10 hrs

Now for the complicated part, a person can take absence(sick,holiday,other) for any part of a day or whole day(s). For these absence periods only the worked time on that day needs to be negated off, not the whole period of time.

So for example

If this person

had a days holiday on the 22nd, shown in the HOLIDAY table as

StaffID,DateFrom, DateTo
969, 22/05/2015 00:00:00.000,22/05/2015 23:59:59.000

A Leave of Absence on the 20th, shown in the LEAVE table as

StaffID,DateFrom, DateTo
969,20/05/2015 12:00:00.000,20/05/2015 16:00:00.000

And was off sick on the morning of the 19th, shown in the SICKNESS Table as

StaffID,DateFrom, DateTo
969, 19/05/2015 00:00:00.000,19/05/2015 11:59:59.000

Now the Summary table should now show

18/05/2015 - 12 hrs
19/05/2015 - 6 hrs
20/05/2015 - 12 hrs
21/05/2015 - 12 hrs
22/05/2015 - 0 hrs
23/05/2015 - 16 hrs
24/05/2015 - 10 hrs

The 'Leave of Absence' on the 20th had no effect on the total for the day as it was between planned work times. how to do this within T-SQL, as simple as possible as I've got to had this code over to other staff members to maintain, who have not had much SQL experience yet?

I've tried doing it as a temp table, with dual insert/select commands, splitting the times over midnight, which partially worked but missed some of the combinations.

View 1 Replies View Related

?? On Making A CD For SQL Server Express

May 16, 2006

how do I go about getting SQL server express onto a CD? My sister in law wants SQL server express on her computer for her business but she does not have internet access so I want to put it on CD and install it on her machine? Thanks....



Andy

View 3 Replies View Related

Abstraction Layer

Nov 8, 2007

Hello,

Is anyone using an abstraction layer for the middle tier to interface with so the actual table design is hidden for the application? I have read several articles that it can be done using views http://www.sqlservercentral.com/articles/Database Design/61335/. However, I don't see myself creating a view ontop of every table and allowing dml modifications to happen through them. I think it would make the query optimizier throw up after a certain level of data and views are encountered. I know snonyms are available in 2005, but I have only seen what can be done not what should be done.

I don't have a fear of allowing my .NET developers (I work with very competent people) to create middle tier objects directly against the db tables for oltp dml operations. I don't want to create stored procedures for these because I think more flexibility exists within the middle tier for business logic. Then all select operations are performed against the db with stored procedures. Does anyone out there think I am crazy? Has anyone successfully created an abstraction layer strategy for their entire db?

Thanks!

View 6 Replies View Related

Persistance Layer

Mar 15, 2007

What is Persistance layer?

And is it part of Database layer or other name for database layer?

--Deepak

View 1 Replies View Related

Making A Connection To A Sql Server Via Webmatrix

Dec 7, 2004

Hi all,
i'm making a webpage via the walkthrough of webmatrix. I use the tutorial 'Build an End-to-End Application (with C#.NET) '. The problem is that this tutorial works with an access db. I'll try to start the same thing but using an sql server.

Nowhere i can't find (because i don't look at the right place i gess) how i have to make a sqlconnection via webmatrix with c# using a keyfield to filter my data and to fill out a datagrid.

can somebody help me please ?

thnx

View 3 Replies View Related

Making Database Changes Without Logging In To The Server.

May 1, 2008

Hi All,

I am a bit new to using MS-SQL, up till now my main adminstration was in Oracle.

I have a question, my developers make changes to my MS-SQL databases, however, they always login as a local admin or use their domain WIndows accounts - with the given privileges. Then they amend the database.
My idea is to avoid logging into an MS-SQL server without using a Remote Desktop. So they will need some kind of a client.

What is your experience and what would be the best way to do this? Meaning, what software can I use that does not require a license fee?

With Oracle I can install the Oracle client which has an SQLPLUS to login remotely. Does MS-SQL have something similar?

Thanks in advance.

I did use Google to search, I also have an MS-SQL Developer Edition, but this seems to come with another server itself.

Regards,
Richard.

View 4 Replies View Related

How To Use SCOPE_IDENTITY() In 4-layer Architecture

May 23, 2007

I have the following stored procedure:
INSERT INTO MyTable ( Value1, Value 2)
VALUES( @Value1, @Value2)
SELECT SCOPE_IDENTITY()
How do I put this sp in the DAL typed dataset, so I can get the Identity value in the Business Layer?
 

View 2 Replies View Related

Using A Static Class As Sql Layer ?

Jun 12, 2008

Can I use this class in ASP.NET web site with many visitors?SqlLayer.cs: public static class SqlLayer{ public static string ConnectionString = ConfigurationManager.ConnectionStrings["SomeConnectionString"].ConnectionString; public static int ExecuteNonQuery(string StoredProcedureName, string[] ParamNames, object[] ParamValues) { if (ParamNames.Length != ParamValues.Length) { throw new Exception("ParamNames.Length != ParamValues.Length"); } using (SqlConnection cSqlConnection = new SqlConnection(ConnectionString) { cSqlConnection.Open(); SqlCommand cSqlCommand = cSqlConnection.CreateCommand(); cSqlCommand.CommandType = CommandType.StoredProcedure; cSqlCommand.CommandText = StoredProcedureName; cSqlCommand.Parameters.Add("@ReturnValue", DbType.Int32); cSqlCommand.Parameters["@ReturnValue"].Direction = ParameterDirection.ReturnValue; for (int i = 0; i < ParamValues.Length; i++) { cSqlCommand.Parameters.AddWithValue(ParamNames[i], ParamValues[i]); } cSqlCommand.ExecuteNonQuery(); return (int) cSqlCommand.Parameters["@ReturnValue"].Value; } } public delegate void ActionForReader(SqlDataReader Reader); public static void ExecuteReader(string StoredProcedureName, string[] ParamNames, object[] ParamValues, ActionForReader cActionForReader) { using (SqlConnection SqlConnection1 = new SqlConnection(ConnectionString)) { SqlConnection1.Open(); SqlCommand SqlCommand1 = SqlConnection1.CreateCommand(); SqlCommand1.CommandType = CommandType.StoredProcedure; SqlCommand1.CommandText = StoredProcedureName; for (int i = 0; i < ParamValues.Length; i++) { SqlCommand1.Parameters.AddWithValue(ParamNames[i], (ParamValues[i] == null ? DBNull.Value : ParamValues[i])); } using (SqlDataReader Reader = SqlCommand1.ExecuteReader()) { if (cActionForReader != null) { //if (Reader.HasRows == false) throw new Exception("Reader has no rows"); //if (Reader.RecordsAffected == 0) throw new Exception("Reader RecordsAffected = 0"); while (Reader.Read()) { if(Reader!=null) cActionForReader(Reader); } } } } }}   Using: SqlLayer.ExecuteNonQuery("SomeStoredProcedure", "ID", ID);---------SqlLayer.ExecuteReader("SomeStoredProcedure", new string[]{"Param1","Param2"}, new object[]{Value1,Value2}, Action);...void ActionForForumCollection(SqlDataReader Reader) {LabelContent.Text += Reader[0].ToString()+" - "+Reader[1].ToString();}  

View 1 Replies View Related

Randomness Better In SQL Or In Application Layer

Aug 17, 2007

Instead of showing the same content (images) when customers visit our application, we want to show random images to make use of all the images that can be displayed.

My question is, is it better to handle randomness at SQL or in the application layer. In sql, we can achieve this by doing order by newid(). My understanding of this is, SQL will create a guid (which is random) for all the rows in the resultset and will sort by the guid. If this code is encapsulated in a SP and if it may be executed over say 200+ or say 1000+ times a day, isn't it better to handle the randomness in the app layer. Let the app layer use resources and sql just retrieve the data necessary.

Any thoughts?

View 1 Replies View Related

Application Layer For SSRS

Feb 19, 2008



Hello,

I'm searching for good documentation for building Application layer for Report (SSRS) using Visual Studio 2005. Kindly forward useful link if possible or walk me through.

Thanks,
Monika

View 11 Replies View Related

Data Access Layer Advice

Jun 19, 2007

I've been following Soctt Mitchell's tutorials on Data Access and in Tutorial 1 (Step 5) he suggests using SQL Subqueries in TableAdapters in order to pick up extra information for display using a datasource.
 I have two tables for a gallery system I'm building. One called Photographs and one called MS_Photographs which has extra information about certain images. When reading the MS_Photograph data I also want to include a couple of fields from the related Photographs table. Rather than creating a table adapter just to pull this data I wanted to use the existing MS_Photographs adapter with a query such as...1 SELECT CAR_MAKE, CAR_MODEL,
2 (SELECT DATE_TAKEN
3 FROM PHOTOGRAPHS
4 WHERE (PHOTOGRAPH_ID = MS_PHOTOGRAPHS.PHOTOGRAPH_ID)) AS DATE_TAKEN,
5 (SELECT FORMAT
6 FROM PHOTOGRAPHS
7 WHERE (PHOTOGRAPH_ID = MS_PHOTOGRAPHS.PHOTOGRAPH_ID)) AS FORMAT,
8 (SELECT REFERENCE
9 FROM PHOTOGRAPHS
10 WHERE (PHOTOGRAPH_ID = MS_PHOTOGRAPHS.PHOTOGRAPH_ID)) AS REFERENCE,
11 DRIVER1, TEAM, GALLERY_ID, PHOTOGRAPH_ID
12 FROM MS_PHOTOGRAPHS
13 WHERE (GALLERY_ID = @GalleryID)
This works but I wanted to know if there's a way to get all of the fields using one subquery instead of three? I did try it but it gave me errors for everything I could think of.Is using a subquery like above the best way when you want this many fields from a secondary table or should I be using another approach. I'm using classes for the BLL as well and wondered if there's a way to do it at this stage instead?

View 7 Replies View Related

Creating A Data Access Layer

Mar 10, 2008

Hello, everybody.

In my web application, i'm using 2 tabels; Users(Username(PK), Pwd, Name, Deptid(FK)) n Dept(Deptid(PK), Deptname)).
For creating a Data Access Layer 4 my project, I added dataset as new item n followed the wizard 2 create the required functions.
I have a function GetUser(@uname, @pwd), which takes username n password as input. M using this for authentication purpose.
While executing it poping an ConstrainException.
Plz help me out.

I've tried 2 as clear as possible here. OR u may ask me any other questions for clear picture of the scenario.
Thanks and Regards,
Sankar.

View 1 Replies View Related

Creating Data Access Layer

Jun 11, 2008

I request you plz tell how to create Data Access Layer. I mean DataAccess.dll. So that I can call stored procedure from dataaccess.dll as below.
DataAccess.SqlHelper.ExecuteDataset(DataAccess.DSN.Connection("DBConnectionString"), CommandType.StoredProcedure, "SP_GetEmpIds");
I request you how can I add this stored procedures to DataAccess.dll and function. I am not having any idea in this area. I request you plz give me some suggestions to work with task.

View 3 Replies View Related

Making ODBC Connection....Sql-server 2005 Driver

Jan 24, 2008

Hi everybody
I have installed SqlServer 2005 on my server but my Windows application on the client computers should connect to server via ODBC connection
so what should i install on the client computers (that don't have any sql server installed) to enable them to connect?
When i want to create ODBC connection on client computers through (Control panelAdministrative ToolsData Sources) i don't see SQL-Server 2005 ODBC driver in the list ....could anyone help me?
Kind Regards.

View 1 Replies View Related

SQL Server 2014 :: Making Dashboard In Excel From Queries?

Jul 20, 2015

Currently i have set of queries which i run for data extraction and result pasted in Excel table so that my pivot table and Chart gets populated along with the summary in Excel file.

I would like to automate this thing and want to know if i can achieve excel output for dashboard on a click of button.

View 2 Replies View Related

Problem Making Oracle Linked Server Work

Jul 23, 2005

Hi all,I set up our Oracle Financials as a linked server to one of my SQLServer boxes. On running a test query, I got the following errormessage:OLE DB provider 'MSDAORA' supplied inconsistent metadata for a column.Metadata information was changed at execution time.OLE DB error trace [Non-interface error: Column 'TEST_NUM'(compile-time ordinal 2) of object '"MYUSER"."TEST_LINK"' was reportedto have aDBTYPE of 130 at compile time and 5 at run time].The Oracle datatype of the column with the supposedly inconsistentmetadata was NUMBER, which according to the Oracle OLE DB documentationactually maps to 139. 130 is a null-terminated unicode characterstring, 5 is a float, and 139 is a variable-length, exact numeric valuewith a signed scale value. Oracle NUMBER is an all-purpose numerictype, apparently they use that instead of int, float, etc. The Oracleguy used it for this column in the test table because in OF it is usedin pretty much every table. (For starters it is the datatype of theiridentity columns.)There is something in the OLE DB spec about all datatypes having to beable to be expressed as DBTYPE_WSTR (130), but what I don't get is thatI can connect to the same Oracle instance using VB6 code and theMSDAORA provider and there is no problem at all interpreting the NUMBERcolumns. So why does it work from VB and not as a linked server? Andmuch more importantly, HOW DO I MAKE THE LINKED SERVER WORK?TIA

View 11 Replies View Related

SQL - System Table In Data Access Layer?

Apr 5, 2007

How do I get a System Table like 'Sysobjects' into the Data Access Layer?
My app generates tables on the fly, and has to check in the sysobjects table which tables are present.

View 5 Replies View Related

LINQ Layer - How To Invoke A Sort Function

Jun 5, 2007

 
 Dear all,
I'm trying to create a BLL for an objectdatasource to used for a sorted DataGrid. I am trying to learn about how to use LINQ and I'm sure this is a simple question.
var query = (from p in db.MP3Collections select p).OrderBy(?????????);
 
Now I want to invoke the following method  List<MP3Collection> GetAllMP3s(string sortColumns), which can contain a string name for the field in MP3Collection that is to be sorted (and optionally the "desc" tag).
Now I'm resorting tocoding the query on a case by case basis, which is very tiresome (see the partial code below). This works fine, but is very long and tedious coding. I'm not sure how to go about neatening the code, though am sure it involves the ?????? space above.
Any ideas?
 public static List<MP3Collection> GetAllMP3s(string sortColumns)
{System.Diagnostics.Debug.WriteLine("Pure sort expression: " + sortColumns);
bool descending = false;if (sortColumns.ToLowerInvariant().EndsWith(" desc"))
{
sortColumns = sortColumns.Substring(0, sortColumns.Length - 5);descending = true;
}string[] columnNames = sortColumns.Split(',');
 MP3DataClassesDataContext db = new MP3DataClassesDataContext();
List<MP3Collection> query = (from p in db.MP3Collections select p).OrderByDescending(p => p.fileName).ToList<MP3Collection>();foreach (string columnName in columnNames)
{switch (columnName.Trim().ToLowerInvariant())
{case "filename":if (descending)
{
query = (from p in db.MP3Collections select p).OrderByDescending(p => p.fileName).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filename descending");
}
else
{query = (from p in db.MP3Collections select p).OrderBy(p => p.fileName).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filename ascending");
}
break;case "filepath":if (descending)
{
query = (from p in db.MP3Collections select p).OrderByDescending(p => p.filePath).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filepath descending");
}
else
{query = (from p in db.MP3Collections select p).OrderBy(p => p.filePath).ToList<MP3Collection>();System.Diagnostics.Debug.WriteLine("Sorting filepath ascending");
}
break;
//OTHER CASES HEREdefault:
query = (from p in db.MP3Collections select p).OrderBy(p => p.filePath).ToList<MP3Collection>();
//throw new ArgumentException("SortColumns contains an invalid column name.");break;
}
}
 
//var query = (from p in db.MP3Collections select p).OrderBy(p=>p.fileName).ToList<MP3Collection>();return query;
}

View 1 Replies View Related

What New Features Of .NET 2.0 Required In A Data Access Layer

Jan 10, 2008

Hi Experts ! I want to use maximum feature of SQL
Server 2005 and ASP.Net 2.0  in making Data Access LayerSuggestions will be welcomed .Thank you
RegardsKAMRAN

View 3 Replies View Related

Deleting Using SqlDataAdapter Via A Data Access Layer

Feb 20, 2008

I've a management module (managing Products) currently being displayed on the aspx page using ObjectDataSource and GridView control.The datasource is taken from a class residing in my Data Access layer with custom methods such as getProducts() and deleteProduct(int productID)I'm currently using SqlDataAdapter as well as Datasets to manipulate the data and have no big problems so far.However, my issue is this, each time i delete a product using the deleteProduct method, I would need to refill the dataset to fill the dataset before i can proceed to delete the product. But since I already filled the dataset using the getProducts() method, is it possible to just use that dataset again so that I dont have to make it refill again? I need to know this cos my data might be alot in the future and refilling the dataset might slow down the performance. 1 public int deleteCompany(Object companyId)
2 {
3 SqlCommand deleteCommand = new SqlCommand("DELETE FROM pg_Company WHERE CompanyId = @companyId", getSqlConnection());
4
5 SqlParameter p1 = new SqlParameter("@companyId", SqlDbType.UniqueIdentifier);
6 p1.Value = (Guid)companyId;
7 p1.Direction = ParameterDirection.Input;
8
9 deleteCommand.Parameters.Add(p1);
10 dataAdapter.DeleteCommand = deleteCommand;
11
12 companyDS = getCompanies(); // <--- I need to refill this before I can delete, I would be iterating an empty ds.
13
14 try
15 {
16 foreach (DataRow row in companyDS.Tables["pg_Company"].Select(@"companyId = '" + companyId + "'"))
17 {
18 row.Delete();
19 }
20 return dataAdapter.Update(companyDS.Tables["pg_Company"]);
21 }
22 catch
23 {
24 return 0;
25 }
26 finally { }
27 }
I thank you in advance for any help here.

View 3 Replies View Related

How To Rollback A Transaction In Data Access Layer

Jul 9, 2007

Hi,

I am having a application in which from the front end i am saving details of three different things



i.Enquiry Details

ii.Parts Details

iii.Machine details



i am saving the Enquiry detail in a data table,Parts Details in a data table and machine detail in a data table and finally i am adding the three data tables into a single data set and passing the data set to data access layer there i have three insert command one for each data table in my case the enquiry data table will be saved first and then the next two details will be saved and i am saving the details in three different tables in the database, my problem is some times the enquiry details will save to the database and while saving the Parts details there may be some exception and i will throw an exception in that case the enquiry details will be saved and the remaining two details are not saved(Which are also part of the same Transaction).I wanted to know about how to call the transaction function in case of Data Access Layer.

View 4 Replies View Related

EnterpriseLibrary 2006 DATA ACCESS LAYER

Sep 27, 2007

in the class library i written the code name :customer is the lib name

using System;

using System.Collections.Generic;

using System.Text;

namespace Customer

{ class Entites

{

public int inTest;

public int inTest2;

}

}


Now in the Class1.cs i written the code

i am getting the data from the database by using enterprise lib 2006 connection function

now HOW TO BIND THE DATA TO LIST AND RETURN TYPE IS LIST
PLEASE CHECK THE CODE AND REDEFINE THE CODE


using System;

using System.Data ;

using System.Collections.Generic;

using System.Collections.Generic;

using System.Text;

using Microsoft.Practices.EnterpriseLibrary.Data;

using Microsoft.Practices.EnterpriseLibrary.ExceptionHandling;

using Microsoft.Practices.EnterpriseLibrary.Common.Configuration;

using System.Collections;

using System.Xml.Serialization;

using System.Data.Common;

using Customer;

namespace Customer

{

class Class1

{

public List<Entites> getdata(int id)

{

Database db = DatabaseFactory.CreateDatabase("mycon");

System.Data.Common.DbCommand cmd ;

cmd = db.GetStoredProcCommand("GET_CUSTOMER");

cmd.CommandType = CommandType.StoredProcedure;

db.AddInParameter(cmd,"@CID",System.Data.DbType.Int32,id);

List<Entites> objEntites = new List<Entites>();

using (IDataReader dr = db.ExecuteReader(cmd))

foreach (Entites obj in dr)

{

objEntites.inTest = obj.inTest;-----------------------------------------------ERROR LINE

// objEntites.Add(obj);

}

return objEntites;

}

}

}




Error 2 foreach statement cannot operate on variables of type 'System.Data.IDataReader' because 'System.Data.IDataReader' does not contain a public definition for 'GetEnumerator' D:KOTI_PRJSEnterpriseCustomerClass1.cs 34 13 Customer

View 1 Replies View Related

Send Mail From SQL Server 2000 Running On Small Business Server 2003

Nov 5, 2007

I'm trying to configure SQL server 2000 (standard edition) to send e-mail on a Small Business Server 2003. There's a great article on how to do this on a SBS 2000 server (KB304967), but it does not apply to SBS 2003. Can anyone point me to a article or white paper on how to configure SQL on a SBS 2003 server to send e-mail. Thanks.

David Neahusan

View 1 Replies View Related

Backup Fails In Windows Server 2003 For Small Business Server

Apr 23, 2008

Hi

MS Backup in Microsoft windows server 2003 for small business server reports:

<-- Volume shadow copy creation: Attempt 1.
"MSDEWriter" has reported an error 0x800423f4. This is part of System State. The backup cannot continue.

Error returned while creating the volume shadow copy:800423f4
Aborting Backup. -->


The VSS events that I get are 8193 & 6013
The MSSQL$SHAREPOINT events are 17055, 18452 & 18456


These are the main Errors that I get:

Event Type: Error
Event Source: VSS
Event Category: None
Event ID: 8193

Description:
Volume Shadow Copy Service error: Unexpected error calling routine IClientVirtualDevice::GetCommand. hr = 0x80770004.


Event Type: Error
Event Source: MSSQL$SHAREPOINT
Event Category: (2)
Event ID: 17055
User: NT AUTHORITYSYSTEM
Description:
18210 :
BackupVirtualDeviceFile:repareToFreeze: failure on backup device '{74A09E78-8845-47EB-9602-705E4406096C}1'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).



Event Type: Error
Event Source: VSS
Event Category: None
Event ID: 6013
User: N/A
Description:
Sqllib error: OLEDB Error encountered calling ICommandText::Execute. hr = 0x80040e14. SQLSTATE: 42000, Native Error: 3013
Error state: 1, Severity: 16
Source: Microsoft OLE DB Provider for SQL Server
Error message: BACKUP DATABASE is terminating abnormally.



Event Type: Error
Event Source: MSSQL$SHAREPOINT
Event Category: (2)
Event ID: 17055
User: NT AUTHORITYSYSTEM
Description:
18210 :
BackupVirtualDeviceFile:repareToFreeze: failure on backup device '{74A09E78-8845-47EB-9602-705E4406096C}5'. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).


I need to get the Backup System working again urgently.
Any advice will be greatly appreciated

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved