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?
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
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.
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.
HiI'm having problems following the tutorial on creating a data access layer - http://www.asp.net/learn/dataaccess/tutorial01cs.aspx?tabid=63 - when I try to compile in Visual Studio 2005 I get namespace could not be found. I followed exactly the tutorial - I created a dataset and added this code in my aspx page. <asp:GridView ID="GridView1" runat="server" CssClass="DataWebControlStyle"> <HeaderStyle CssClass="HeaderStyle" /> <AlternatingRowStyle CssClass="AlternatingRowStyle" />In my C# file I added these lines... using NorthwindTableAdapters; <<<<<this is the problem - where does this come from? protected void Page_Load(object sender, EventArgs e) { ProductsTableAdapter productsAdapter = new ProductsTableAdapter(); GridView1.DataSource = productsAdapter.GetProducts(); GridView1.DataBind(); }Thanks in advance
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.
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
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.
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.
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
I am taking my first stab at a 3 tier architecture within ASP.Net. I have been adding datasets and creating a new Insert to add certain parts to a table. I am trying to add a date field called 'DateAdded' and is setup in SQL as a DateTime. When Visual Studio auto created the dataset, the Insert function is not "DateAdded as Datetime" as I would have expected, but it is "DateAdded as System.Nullable(Of Date)". There is a space in between 'Of' and 'Date'. If I keep the space in there the insert function shows an error that says "Arguement not specified for parameter DateAdded of funtion( etc. etc.). If I take the space out, the error on the insert function goes away but there is an error within the "OfDate" that says "Array bound cannot appear in type specifiers". I am confused on why the date format changed and how I can get a date to go into the database using the autogenerated datasets from Visual Studio. Any help would be appreciated. Thanks, Mike
The query fails to execute and returns an error: String[1]: the Size property has an invalid size of 0. If I change the SqlDbType.Text parameter type to SqlDBType.Varchar, 100 (or any other fixed varchar length), it works but limits the length my unlimited field text. Any suggestions on how I can use db type text or varchar(max)? The field I need to retrieve is string characters of unlimited length and hence the datatype varchar(max).
I'm about to embark on writing some code in perl or VBscript that automatically synchronises a constantly updated Access database with an MSSQL database.
I know MSSQL has an import tool built into Enterprise manager but I'm wondering if theres a stored procedure that does this?
The way I'm thinking of doing it is to read the all the access tables into separate hash arrays and then INSERTing them into the MSSQL database after checking for any duplicates. This all sounds a bit time consuming (there are a large number of tables) and processor intensive.
If anyones done anything like this before, I'd love to hear their views......!
create table #datatable (id int, name varchar(100), email varchar(10), phone varchar(10), cellphone varchar(10), none varchar(10) ); insert into #datatable exec ('select * from datatable where id = 1') select * from #datatable
I still want to retrieve the data and present it in the software's presentation layer but I still want to remove the temp table in order to reduce performance issue etc.
If I paste the code DROP
TABLE #datatable after insert into #datatable exec ('select * from datatable where id = 1')
Does it gonna work to present the data in the presentation layer after removing the temp table?
The goal is to retrieve the data from a stored procedure and present it in the software's presentation layer and I still need to remove the temptable after using it because I will use the SP many time in production phase.
I am working on two versions of an application, one of which will be awindows forms application (which will need to be redistributable) andthe other will be a web application.I have MS Visual Studio 2005 (along with the developer's edition of MSSQL Server), but not MS Access. I also have MySQL, PostgreSQL, Sun'sapplication server, Tomcat and Apache web server. I am working onWindows XP Pro, and have installed the .NET 3 SDK and all relevantrelated products I could find (e.g. 2 extensions packages for VisualStudio).I have one MS Access database, to which my users should have read onlyaccess. I have, and have used, a tool for importing MS Accessdatabases into MySQL. I expect that SQL Server has a similar utilityhidden somewhere (where I haven't yet looked, though I HAVE beenlooking - obviously in the wrong places). I have located a similarutility for importing MS Access databases into PostgreSQL. I have notyet decided which servers to use for the web version, but that isanother story, for which I may raise another thread in due course (butI welcome suggestions which may reduce the effort required givenrequired effort for the windows forms app).My problem is for the windows form aplication (intended for use by asingle family). I expect to use ADO.NET. The question is, should Iimport the Access database into MS SQL, and redistribute it, along withMS SQL Server Express (or is that necessary), or distribute it just asan Access database and use the jet engine to access it. A relatedquestion is, "Does ADO.NET support creating new databases for a givenengine?" Imagine a recipe database. It is easy enough to create a SQLscript that creates all the required tables, indices, foreign keys,&c., but can I submit that SQL script to an ADO.NET object, along witha file name, and have it create, e.g., an Access database with thesupplied name. Or do I have to create a database file with nothing init other than the schema?I have more questions, but they'll have to wait.ThanksTed
I have an application that uses Acces as a backend and VBA as front end. Application is secured and is supplied on a CD with setup.exe.
Can I use VB 2005 and MS SQL to achieve the same?
Would I be able to package my application with all the neccessery files (assuming that client does not have any e.g. SQL server) so that multiple front ends can access data from common source?
Would I be able to secure such an application using only VS 2005?
What would I need to quickly learn how to achieve the above ( any books you can suggest maybe)?
I have 2 flat files to load into a datamart via SSIS. Need to implement:- 1. How can I prevent loading of same file again? 2. If by chance wrong data has been loaded how can I rollback? Kindlt guide asap as I have to implement these. JigJan
I have to retrieve data from a KSAM (kerridge) database and can only use a file dsn ODBC connection to connect to the database.
I can get access to the database through excel and thereby see the tables but when I try and open a connection through the development environment, it keeps my machine busy for what seems to be an eternity whith no result.
I want to use SSIS to extract the data to a SQL 2005 database but will need to get my connections/connection managers to work.
Is there any advice that anyone can give me on perhaps the best approach on the data extract? Regards Mike
Hi,Is there a programmatic wasy to convert the results of a sql data set to xls, csv, etc. Ideally a user would be able to make a selection to view the data (result set has, E.g. make, model, year, condition viewed in a datagrid or similar control) and then be able to export the file to the format they choose, and have a download box popup from the browser to download the file.E.g. Export this data to: __ XLS __ CSV __TXT . I know DTS can do this but any advice on how to encapsulate this in a C# web app woudl be greatly appreciated! Thanks!
I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend. As a result, I'm going to write a number of Stored Procedures to replace the MS Access code. My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-
USE [VMSProd] GO /****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
Hello,I'm looking into offering a custom data driven web app that I wrote for an organization that I'm apart of to other similar organizations. I would be hosting the data and web application code on my dedicated server. This application is using the membership api supplied in .NET 2.0 and also has my own custom data tables within it.My question is what would be the best way to add clients to this? Should I simply create a new database for each new client like so: ACME_Database, ABC_Database, AAA_Database etc. Or should I add some sort of client "Tag" (tag meaning column within each datatable) to these databases and then update my SQL queries to process them accordingly. I imagine I could do both but I guess I need some advice from people that already had experiance with providing this kind of service. Thanks!Jason
I have recently been task with rewriting a database that holds large volumes of data, whilst ensuring that query can be run in optimal time. Having never really delved into this sort of thing before, I hoped you guys might be able to offer some advice and guidance.
The design I have inherited is based around 2 main tables:
[captured_varbinds] [id] [int] IDENTITY (1, 1) NOT NULL [captured_trap_id] [int] NOT NULL [varbind_oid] [varchar] (500) [varbind_text] [varchar (500)
The relationship between the two tables is on the "captured_traps (id)" to "captured_varbinds (captured_trap_id)". Currently the "captured_traps" table contains around 350 million rows, the "captured_varbinds" table contains around 900 million rows.
Now as you can probably gather this model runs like a....well it sort of hobbles more than runs hence the need to redesign.
My current thoughts on this are:
- Normalising all varchars - there is alot of duplicate values in most of the varchar fields. - Full Text Indexing
However beyond that I am not sure which route to go down. After googling for most of today I have come across a number of "solutions" however I do not want to go steaming down the track of one of these to discover that it is fatally flawed somewhere.
Hello everyone, Here's my situation... I'm running a web service which involves 51 seperate servers and databases. There are fifty licensee servers (One for each US state) and one corporate server. Each night I need to upload sales and membership data from the licensee's databases to the corporate database to compile reports. The application platform I'm using is ASP.NET 2.0 and the the database is SQL2005 express. I want this process to be run automatically, so I believe it's a scheduled windows service I need to setup up in .NET to make the data transfers. If anyone has already set something up like this, or knows the steps to take? I would love to have your input. Thanks in advance, Robert
I have an application that automatically reads a lot of data from a third-party application into my database, via XML. For example, I might read a couple thousand rows-worth of XML data, one row at a time in a foreach loop. To reduce the load on their server and database, I thought about putting a 2 second delay in between each of my automatic requests. Would this really help much, or is there enough overhead (setting up/tearing down connections, etc) with each request that it wouldn't reduce server load much anyway? Is 2 seconds enough? Too little or too much?
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?
I have a client who has SSMS installed on her laptop. She is able to connect to the SQL server via SSMS in the office and query data on the server.
She needs to be out of site often and doesn't have internet access. She asks if the data tables can be "backed up" or saved on her laptop, so she can look at them without worrying connecting to the server. I am not sure if this can be achieved, as SSMS is built for accessing a server, not a desktop. Myself never have this need. If I really need it, I would go to Microsoft Access and create an ODBC connection to the datatables. But this client thinks that Microsoft Access is beneath her.
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?
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.
