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
ADVERTISEMENT
Randomness Better In SQL Or In Application Layer
Aug 17, 2007Instead 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?
Should SQL Server Act As A Business Layer In The 3-tier Application Layout.
Mar 7, 2007Hello 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
Internalization Of (generic) Application Layer Software To The Database TSQL
Jul 20, 2005Greetings to all database professionals and laymen,Let us make a bold assumption that we have developed a softwaretool for the SQL Server environment which simply acts as an interfacebetween an end-user in an organization and the database, throughthe exclusive use of stored procedures which are authored by theorganization or by software developers.All development work at the application software level may therebybe conducted within SQL, by the development of TSQL storedprocedures and their coordination across an organization.The question then needs to be asked what are the advantages of thisarrangement and what are the disadvantages. I would appreciateyour comments here, as it is difficult for folk heavily involved (eg: me)in something to obtain objective opinion otherwise.Potentially it is possible to construct an entire database applicationsoftware package using only TSQL stored procedures and thistool. When a database backup is conducted not only the data isbacked up but also all the "program development" suite, in theform of stored procedures (and their scheduling, for example).One of the advantages from my perspective is that this arrangementimplies the possibility that all software external to the database maybe made redundant (except this tool), and along with it, all the otherredundancies of managing application development (life-cycles) withinthe database and then coordinating these changes with softwareexternal the database (particulary loading it to the client environment)You see, I believe that (for example) any and every application writtenin VB, C, etc, etc, etc (external to rdbms) expressly for database software(ie: rdbms software such as SQL Server) involves a redundancy of datadefinitions required ---- once within the VB and once within the db.I would appreciate any feedback on any of the above issues, andwish everyone the compliments of the season.Pete BrownFalls CreekNSWOz~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~EDITOR:BoomerangOutPost: Mountain Man Graphics, Newport Beach, {OZ}Thematic Threading: Publications of Peace and Of Great SoulsWebulous Coordinates: http://www.mountainman.com.auQuoteForTheDay:"Consciousness is never experienced in the plural, only inthe singular. How does the idea of plurality (emphaticallyopposed by the Upanishad writers arise at all? .... theonly possible alternative is simply to keep the immediateexperience that consciousness is a singular of which theplural is unkown; that there *is* only one thing and thatwhat seems to be a plurality is merely a series of differentaspects of this one thing produced by deception (the Indianmaya) - in much the same way Gaurisankar and Mt Everest turnout to be the same peak seen from different valleys."- E. Schrodinger, "What is Life"--------------------------------------------------------------------
View 18 Replies View RelatedLaunching An Application From SSRS
May 1, 2008
Hi
I would like to launch an application (developed by my team) from a report in SSRS, just like how a web page can be launnched by setting the Navigation property.
Any ideas?
I tried this by writing a function in a custom assembly
public static myfunc()
{
Process.Start("c:myapp.exe")
}
and calling this function from the report. This is not working.
Iam guessing it has to do with setting permissions. How and where do i need to set this?
thanks
Chitra.
SSRS Integration With Application
Sep 24, 2007
To which degree can SSRS integrated with application? I know that we can integrate report viewer in to internal applications. With this we can view different reports within the application. Now, Is there a way we can drive from report viewer back to the application?
For example, when we display a report within an application, is it possible to select some of the records within that viewer and send it back to the application. For example, lets say within the application we display a report to display all students in a particular class. What we see is an embedded report within the application. Now, what I want is - I will select checkboxes for some of the students within that report. and when I click delete, it should read all those selected students from that report viewer and send it back to the application which in turn will delete them from that class.
Let me know if this is not clear enough....
Application Role And SSRS
Sep 7, 2006Hi dear reader
I made an application that uses a Sql Server 2005 Express DataBase.
In the database I made a application role.
When the user logs into my application I run this procedure:
If Not sqlConnectionCR Is Nothing Then
If Not sqlConnectionCR.State = ConnectionState.Open Then
sqlConnectionCR.Open()
SqlConnection.ClearAllPools()
ConsultasSqlCommand = New SqlCommand
ConsultasSqlCommand.CommandType = CommandType.Text
ConsultasSqlCommand.CommandText = "sp_setapprole 'appRole', 'drowssap"
ConsultasSqlCommand.Connection = sqlConnectionCR
ConsultasSqlCommand.ExecuteNonQuery()
End If
Else....
I understand that this procedure connects to my sqlserver database as my application role
Ok, so far no problems in reading and manipulating data.
The problem comes with the reports in my application. For example: I have a reportviewer with a serverreport but when I try to show the report gives an error about permissions and grant access....
I think that is because the Server Report uses the user account (domain/user) to read the database. No user (besides admin) has access permissions in the database (only admin and application role).
So, my cuestion is: How can I tell Report Server to use the application role to display reports?
Thank you for your time and help.
Giber
Is There An Option Of Adding Style Sheet For The SSRS Application.
Mar 27, 2007I want's to define my custom style for my report project.It should also be consistent.
I doesn't want to set it globally but on the project basis.
Is there any option of defining a style sheet for Sql server reporting services like we do for our web application.
SSRS 2005 - Server Error In '/Reports' Application.
Oct 18, 2006When trying to open the reports server \servername
eports we are getting an error page titled "Server Error in '/Reports' Application." The server has been restarted, and as this is a new deployment, there has been very little access for individuals to make changes.
Any ideas on how to resolve the problem?
Thanks in advace
Abstraction Layer
Nov 8, 2007Hello,
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!
Persistance Layer
Mar 15, 2007What is Persistance layer?
And is it part of Database layer or other name for database layer?
--Deepak
How To Use SCOPE_IDENTITY() In 4-layer Architecture
May 23, 2007I 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?
Using A Static Class As Sql Layer ?
Jun 12, 2008Can 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 RelatedData Access Layer Advice
Jun 19, 2007I'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?
Creating A Data Access Layer
Mar 10, 2008Hello, 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.
Creating Data Access Layer
Jun 11, 2008I 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.
SQL - System Table In Data Access Layer?
Apr 5, 2007How 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.
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;
}
What New Features Of .NET 2.0 Required In A Data Access Layer
Jan 10, 2008Hi 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
Making Calculations In A Business Layer Vs Sql Server
Jan 29, 2008Lead 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.
Deleting Using SqlDataAdapter Via A Data Access Layer
Feb 20, 2008I'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.
How To Rollback A Transaction In Data Access Layer
Jul 9, 2007Hi,
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.
EnterpriseLibrary 2006 DATA ACCESS LAYER
Sep 27, 2007in 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
Analysis :: Top N Rows Vary Depending On Reporting Layer?
Oct 16, 2015trivial example, i have a tabular model for orderlines.Each line has a product.A product fits under a hierarchy Category > Product.I need to display the top N rows by quantity under each category. My can break down as follows: Warehouse, Product category, Product, PurchaserID
If im to do the top N in excel i use pivot table functions to get the top N by whatever measure i pick from the dropdown:I cant do this in powerbi builder though, as it works very differently, or powerview which again is done differently.Do i have to take a different approach depending on the report platform, or is there a way to do this in the tabular model that will work on any platform?
Why You Can't Set Up In 'disabled Status' A Task In Data Flow Layer???
May 26, 2006In Control Flow you can do that.
View 10 Replies View RelatedError Trying To Add Information To DateTime Field Using Data Access Layer
Sep 20, 2006I 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
View 1 Replies View RelatedStored Procedure Output Parameter's Issue In Dataaccess Layer
Aug 27, 2007Hi,the stored procedure returns only the first letter of the ouput parameter i.e 'e'. Actually It must return 'error while updating data'. can anybody helpme to solve this issue.How to set the size of output parameter.spvr_ErrorDescription='error while updating data which I get from the output parameter of stored procedure. How to set the size of output parameter
in dataaccess layer. Below is the actual code.public string UserCostCenter_Upd(string spvp_Offering,string spvp_UserId,string spvp_CostCenter,DateTime spvp_StartDate,DateTime spvp_ExpiryDate,ref string spvr_ErrorDescription){// The instance ds will hold the result set obtained from the DataRequestDataSet ds = new DataSet();RequestParameter[] parms = new RequestParameter[7];parms[0] = new RequestParameter("@Return_Value", DbType.Int32, ParameterDirection.ReturnValue, null, true);parms[1] = new RequestParameter("@p_Offering", DbType.AnsiString);parms[1].Value = spvp_Offering;parms[2] = new RequestParameter("@p_UserId", DbType.AnsiStringFixedLength);if (spvp_UserId == null)parms[2].Value = DBNull.Value;elseparms[2].Value = spvp_UserId;parms[3] = new RequestParameter("@p_CostCenter", DbType.AnsiString);if (spvp_CostCenter == null)parms[3].Value = DBNull.Value;elseparms[3].Value = spvp_CostCenter;parms[4] = new RequestParameter("@p_StartDate", DbType.DateTime);if (spvp_StartDate == null)parms[4].Value = DBNull.Value;elseparms[4].Value = spvp_StartDate;parms[5] = new RequestParameter("@p_ExpiryDate", DbType.DateTime);if (spvp_ExpiryDate == null)parms[5].Value = DBNull.Value;elseparms[5].Value = spvp_ExpiryDate;parms[6] = new RequestParameter("@r_ErrorDescription", DbType.String, ParameterDirection.InputOutput , null, true);parms[6].Value = spvr_ErrorDescription;// Create an instance of DataSQLClientAirProducts.GlobalIT.Framework.DataAccess.DataSqlClient daSQL = new DataSqlClient(Constants.ApplicationName); ;// typDSRef holds the ref. to the strongly typed datasetDataSet typDSRef = (DataSet)ds;DataSet resultDataSet = daSQL.ExecuteDataSet("[ap_UserCostCenter_Upd]", ref parms);// Call DataHelper.MapResultSet function for mapping the result set obtained in ordinary DataSet to strongly typed DataSetDataHelper helper = new DataHelper();if (!helper.MapResultSet(ref typDSRef, resultDataSet))ds = null; // Returns a null reference if the DataHelper.MapResultSet is failed. //returnCode = (int)parms[0].Value;spvr_ErrorDescription = (string)((parms[6].Value == DBNull.Value) ? null : parms[6].Value);return spvr_ErrorDescription ;}
Error Using SQL Datatype Text As Output Parameter From C# Data Access Layer
May 19, 2008
Hello,
My datalayer of C# code is invoking a stored procedure that returns a varchar(max) SQL data type. In my ASP.NET code, I have:
SqlCommand myCommand = new SqlCommand("usp_GetTestString", myConnection);
myCommand.Parameters.Add(new SqlParameter("@TestString", SqlDbType.Text));
myCommand.Parameters["@TestString"].Direction = ParameterDirection.Output;
myConnection.Open();
myCommand.ExecuteNonQuery();
return Convert.ToString(myCommand.Parameters["@TestString"].Value);
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).
Transact SQL :: Present Data In Presentation Layer After Removing Temp Table?
May 21, 2015Inside of stored procedure you have the code
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.
[URL]
Distrib.exe Application Error , Application Failed To Initialize Properly(0xx0000142)
Apr 13, 2008have SQL Server 2005 std edition SP1 installed on Windows 2003 Std edition .Configured Transactional (single Publisher and no clustered environment.)
Replication past two months working fine, Now
1.Distrib.exe application err is coming.
Due to which my job is failing (Distributor to Subscriber).
Iam attaching thw file.
Thanks
Sandeep
Having Trouble Following Tutorial - Working With Data In ASP.NET 2.0 :: Creating A Data Access Layer
Nov 1, 2006HiI'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
View 6 Replies View RelatedMulti-user Access Through A Data-access Layer/remoting Server
Oct 30, 2007Hi 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
Tutorial: Creating A Data Access Layer...ceases To Be A Tutorial On Page 12
Apr 8, 2007Before page 12, step by step instructions work!
Then there is code for AllProducts.aspx that doesn't work if one inserts the code
into the DataTutorial project started on page 1. Yes I changed the name of the CodeFile!
The code given for AllProducts.aspx.cs doesn't compile.
I was doing better without the tutorial!
I can gleen out some concepts but that is all.
If that is all, why have a tutorial?