ADO.NET :: Working With Sqldataadapter & Dataset Between Layers?
Jan 14, 2011
I have a theoretical question.i have a function in DAL that does a select sql query. and these are my questions:
I was told that it's not appropriate to pass datasets between layers. so how should i pass the results? which value should i return to the BLLBAL (i thought of a List<>).? and to the presentation layer.
currently my implemention is like that:
SqlCommand cmd = new SqlCommand("getRequestById", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@requestId", rId));
SqlDataAdapter datAdp = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
datAdp.Fill(ds,"getRequestById");
List<String> rs = new List<string>();
for (int i=0; i<11; i++)
{
rs.Add((Convert.ToString(ds.Tables[0].Rows[0].ItemArray.GetValue(i))));
}
return (rs);
is it better to create xsd file in VS2010 with only fill (all other implemtation i'll do on my own) property.
I added New Item, DataSet, over that i added tableadapter with stored procedure. i declared that it will do only the fill property(without returning the data). i want to implement all the other properties by demand in my way.
i wrote in the dal test.getRequestById1DataTable dt1 = new test.getRequestById1DataTable() how can i get the data and pass it between the layers?
This issue has stumped me for a while. Hopefully someone here can give me some insight.
When my site runs the following code it works just fine 99% of the time.
[Code]....
The command is calling a SQL Server 2005 stored procedure that takes 20 parameters and returns 6 tables of about 5-50 rows each. The paramters are all NVARCHAR, INT, or BIT data types. Only one of the parameters is Input/Output. There is a total of about 100 rows returned. Normally this takes a fraction of a second, but for some queries, it times out after 30 seconds.
When I run the exact same query in the Management Studio query window it takes 1 second.
what I can do to get the same performance from .Net as I am getting from Management Studio?
I mean, what is the faster way to get as fast as I can more than one table with stored procedure? Is there any study what is faster and why? There is a big problem getting more than one table at once with Entities, so the only way is DataSet. But I was told DataSet work very slow. Is that true?
i try to update the gridview i keep on getting the error like objectdatasource could not find a non generic method 'update' which has parameters a,b,c etc.i have done everything right but i cant seem to figure this one out.i am using objectdatasource,a class file and a dataset.xsd file.
I'm a bit new to VB .NET programming. I was given a project to build a web page for our Shipping department to pull up some order & invoice numbers, based on an order number parameter.
Basically, what I'm trying to do is, when the user enters an order number, I want the web page to display the invoices & invoice dates that are associated with the order number.
I've put together some code already, but not sure it is actually working. The web page comes up in Debugging mode and it acts like it is pulling data from the Oracle database, but nothing shows up in the Gridview (which I bound to the dataset... I think).
[Code]....
Being new to VB, I'm guessing my code may not be totally correct. I've tried lots of things and done tons of searching on Google, but nothing I've tried seems to work. At this point, I'm not even sure if the dataset is being populated correctly or at all either. I'm a bit lost right now and could use some expert thoughts.
In case it helps, my dataset has one data table in it (dtShipData). My dataset class is sdDataSet. I added 3 columns to the data table and the gridview, manually. Not sure if should do that or not. I don't have any SQL behind the datatable itself...
after creating a xsd dataset in one machine when i try to use the another in a application in another system though i can use it for fetching records & all i am not able to confidure it or preview data.i already have the connectin string in web config.what changes needs to be done so that the dataset from system 1 can work in ssytem2
I was working with SSRS Â in windows application,when I got this error "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." I have made the property of Dataset(.xsd) EnforceConstrints to False and error gone. Whether it will impact anywhere in the application in the future or it wont have any impact.
I have a Listview control that DataBind programatically... ie I am NOT using SqlDataSource or such... I am attempting to sort a column... clicking on a link button that fires the "Sorting" event. So, after cliking on the column heading... I end up in this routine, where I hardcoded the .Sort = values (just for testing). The code is executed... but the RESULT show via Listview remains the SAME. No change!
How to fix this? I've seen some other posts examples that uses this event (ListView1_Sorting) and then sort via LINQ. I am not ready to try that...
Just want to know why dsPaymentList.Tables[0].DefaultView.Sort does not work?
I have the following sub that I need help with an error on. When run error message is saying that the parameter called by the stored proc was not supplied. Am I missing something? I can't seem to find it.....
Is this necessary to open and close connection when i am using SqlDataAdapter and DataSet to get data from  from table from  databse in sql server? Which one will be better to use in the following code. Code1 or Code2.
Code: 1 With open and colse connection
public DataSet GetFAcadSlidingImage() { SqlConnection con = GetConnection(); cmd = new SqlCommand("Pro_GetFAcadSlidingImage", con); cmd.CommandType = CommandType.StoredProcedure;
[Code].....
Code:2 Without open and colse connection
public DataSet GetFAcadSlidingImage() { SqlConnection con = GetConnection(); cmd = new SqlCommand("Pro_GetFAcadSlidingImage", con); cmd.CommandType = CommandType.StoredProcedure;
how the WCF, DAO and DTO layers communicate with each other? I would appreciate if someone can specify which layer comes first and then how it interacts with the next layer
I have a form with mulptile panels hidden behind others. It is impossible to edit them in design mode without moving them to the side so that all panels are visible, so that is what I do. I use JavaScript to position the panels during runtime, but it is an innefecient way to do it.
Is there a way to make a panel or layer visible or invisible while working in design mode, kind of like autocad works
While designing (and then implementing) a layered application:
Is it correct to use the same ORM objects accross all layers? (which would go against encapsulation).
Or the presentation, business and data layer should each have their own objects? (which would lead to lots of code repetition).
e.g. (just to illustrate the question): if one uses Linq to SQL in the Data Layer and Visual Studio's O/R designer to generate the ORM objects, are those objects supposed to be used in the Business and Presentation Layers as well.
i.e.: Are the objects associated with the entities that the application handles a crosscutting issue?
Split off from http://forums.asp.net/t/1618613.aspx
I would like to get your advise on Dependency Inversion..Should i include it as a layer between Business Layer and Repository Layer?
I think its really great when different team members work on DAL n BAL etc or when your database will be changed in future. It actually removes the dependency of the Data provider but over the expense of additional coding overhead. My question is , is it really worth it? If i suggest this layer to my PM and give him the above two reasons then i dnt think he will accept it since most of the time each developer works on his DAL/BAL's, and Change in DB wud be the least of his concerns.
RAD in every layer..
I mean if there are any auto generation tools available for generating repositories etc.I read about MVCContrib Grid and i think its a good option to use for Grid.
If i use Spark ViewEngine then can i still use T4 templates to auto create custom views by customizing templates?
Would anyone know how to pass a variable to SqlDataAdapter. Here is my code.
TextBox1.Text = 1 Dim textboxval As String textboxval = TextBox1.Text connection.Open()Using connection As New SqlConnection("Data Source=CHRIS-PCSQLEXPRESS;Initial Catalog=TorGHL;Integrated Security=True") Dim dapubcb As New SqlDataAdapter("SELECT [BarName], [MenuSubId], [Address], [PhoneNo], [BarURL], [Email] [BarDescription], [Id], [bg_long], [bg_lat] FROM [BarDetails] WHERE [MenuSubId] = texboxval ", connection)
I am trying to use a SqlDataAdapter to fill a DataTable, which I use as a data source to a DataGrid. The query is fine, at least when I run it manually in SSMSE. When I do the fill operation, the table gets the right columns, but no rows. The most frustrating part of this is that the code is identical (with a different query) on a different page. I cannot post the query, but my initialization code looks like this:
SqlCommand areaDAC = new SqlCommand (areaQuery, connection); areaDAC.Parameters.Add (new SqlParameter ("@param", System.Data.SqlDbType.NVarChar, 50)); m_areaDataAdapter = new SqlDataAdapter (areaDAC);
At this point, table has the right number of columns and no rows. I know the parameters are being added correctly, I know that data exists for the given query.
I´m having a code making some txt-files out of a query for each af a dropdownlist.
I need to name the the output file to a name including the distinct data of a column in my query.
[Code]....
The output file name should include the data from the query of agent_input36. It could be several data. I´m trying to make an order file. The orderfile name (.txt) should include the possible names of agent_input36. It could look like. YYY_"agent_input36"_Order_"Date"_CCC.txt. Can I retrieve the data of this column from a sqlDataAdapter into a stringvalue?
I am trying to create a method which will access the database via a SqlDataAdapter. I have passed two parameters month and year. When I run it, I get the error "Object reference not set to an instance of an object". I have posted the complete code below. Please take a look..
public DataTable GetOrderId() { SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnection"].ToString()); MyConnection oconnection = new MyConnection(); DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter("select OrderID from Order", connection); for (int i = 0; i <= dt.Rows.Count; i++) { da.Fill(dt); } return dt; }Â
I have three layers like UI, business and data access in my asp.net web application project. If I pass say a company code from the UI to the business to the data access and I query the company details like company name, address, phone etc from the database using sqlhelper executedataset() which wil return me a dataset. Then how do I pass these company details back to the UI layer. Is it correct to just pass the datatable object to the business and UI.
I'm trying to create my first Asp.Net application from scratch. Tried to create this post in some of the other forums but I couldn't find a better place.
So, as the title says, I'm having trouble trying to model my application. I'm like an OOP newbie. I'll show my classes first and then I'll write my questions.
Model Layer
public class User { private int id; public int Id { get { return id; } set { id = value; } } private string login; public string Login { get { return login; } set { login = value; } } private string password; public string Password { get { return password; } set { password = value; } } } Data Access Layer public class BasicDAL { private SqlConnection connection; //>>Returns a new connection to the database public SqlConnection GetConnection() { //>>If connection is active close it before starting a new one if (connection != null) if (connecion.State != ConnectionState.Closed) connection.Close(); connection = new SqlConnection("MyConnectionStringHere"); return connection; } } public class UserDAL : BasicDAL { //>>Insert a new user public void Insert(User user); { /* Insert into DB */ } //>>Authenticate user login with its password public void Authenticate(User user); { /* Authentication code here */ } } Business Logic Layer
[Code]....
So, model layer is pretty simple. Just a user representation. On the Data Access Layer I have a basic class with a GetConnection method. All DAL classes will extend this one.
My first problem lies on Business Logic Layer. With the above scenario, I've placed the same methods that I placed at the DAL. BLL methods would call DAL. As simple as that but I believe It is not the best way to do it, is it? How can I improve those classes?
Also, I have to "try..catch" blocks. That's because I can't find good places for it. I mean, if I place a try..catch on GetConnection method, for instance, how my ASPX page would get this error? How my ASPX page can tell the difference between "database offline" and "sql syntax error" when executing "userBll.Insert(newUser);".
My problem is mainly placing the exception handlers. I understand I would probably have to change return type of some methods. I didn't change because I believe that will have something with the exception handlers.
Btw, please assume I can't use TableAdapters and stuff like that. I would like to create all layers by myself.
