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?
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!
What is Persistance layer?
And is it part of Database layer or other name for database layer?
--Deepak
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 RelatedInstead 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?
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
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?
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.
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.
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.
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;
}
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
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.
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.
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.
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
trivial 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?
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
In Control Flow you can do that.
View 10 Replies View RelatedI 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 RelatedHi,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 ;}
Greetings 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 RelatedHello,
I want to kow if the following architecture is good :
Disque 1&2 ( Raid 1)
c: OS
d: sqlserver + system tables + log files
Disque 3&4&5 (Raid 5)
e: data
Hi,
Someone can tell me if the following architecture is good :
f:master db
g:soft db + log
where can we get to know the architecture of sqlserver 2005
View 4 Replies View Related
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).
Inside 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]
Using scope_identity
I am using SQL2005 and I need to insert a record and return ID. I am using scope_identity() in the stored procedure to return the ID for the record just inserted.
Do you see any problem with this when it comes to multi-user and multi-threaded environment.
Hi, i need the DiagnosisID from the Diagnosis table to be copied and insert it into DiagnosisID from DiagnosisManagement. I was told to use scope_identity(), but i'm not sure how to implement it. Below is my code behind in vb.net. pls help. Dim cmd1 As New SqlCommand("insert into Diagnosis(TypeID, SeverityID, UniBilateral, PatientID, StaffID) values ('" & typevalue & "','" & severityvalue & "','" & unibivalue & "','" & Session("PatientID") & "','" & Session("StaffID") & "')", conn) cmd1.ExecuteNonQuery() Dim i As Integer For i = 0 To hearingarray.Count - 1 Dim li As New ListItem li = hearingarray(i) Dim cmd As New SqlCommand("insert into DiagnosisManagement(ManagementID) values ('" & li.Value & "')", conn) //i need the DIagnosisID from the Diagnosis table to be copied and insert it into DiagnosisID from DiagnosisManagement here cmd.ExecuteNonQuery() Next
View 1 Replies View RelatedHi All,
I'm trying to return the last id entered via the following code, and I'm only getting '0' back.
1 using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString))
2 {
3 connection.Open();
4 using (SqlCommand command = new SqlCommand("REG_S_CustomerIDFromPhoneNumber", connection))
5 {
6 command.CommandType = CommandType.StoredProcedure;
7 command.Parameters.AddWithValue("@Mobile_Telephone", MobileNumber);
8
9 int test = Convert.ToInt32(command.ExecuteScalar());
10
11 Response.Write(test.ToString());
12
13
14 }
15 } My SP is as follows (I'm trying to use one that's already been written for me) 1 SET QUOTED_IDENTIFIER ON
2 GO
3 SET ANSI_NULLS ON
4 GO
5
6
7
8 ALTER PROCEDURE [dbo].[REG_I_CreateBlankCustomer]
9 @Mobile_Telephone varchar(255),
10 @CustomerID int OUTPUT
11
12 AS
13
14 INSERT INTO Customer (Mobile_Telephone)
15 VALUES (@Mobile_Telephone)
16
17 --SET @CustomerID = @@Identity
18 SELECT SCOPE_IDENTITY();
19
20
21 GO
22 SET QUOTED_IDENTIFIER OFF
23 GO
24 SET ANSI_NULLS ON
25 GO
26
27
28
when I'm running this via Query Analyser, I get the ID returned correctly, however as mentioned when ran via that code above - I get a 0 outputted to me. What am I doing wrong?
Thanks!
i have such a stored procedure.
but i dont know where to put that scope_identity to retrieve a value.
SELECT SCOPE_IDENTITY() AS [@Car_id]
GO
ALTER procedure [dbo].[insertuser](
@Make nchar(10),
@Model nchar(10),
@SellerID varchar(50),
@MileAge nchar(10),
@Year_Model int,
@Price money,
@Date_added datetime,
@Thumb_ID varchar(50),
@Image_id varchar(50),
@Car_id int
)
AS
INSERT INTO
dbo.tbcar
VALUES(@Make,@Model,@SellerID,@MileAge,@Year_Model,@Price,@Date_added);
INSERT INTO
dbo.tbimages
values
(@Thumb_ID,@Image_id,@Car_id)
I was wondering if its possible to use this function if your using a
SqlDataReader where I just run a stored procedure that just inserts a
row?
Hello altogether, my problem ist that I get following error message:
Create Stored ProcedureUnable to chances to the stored procedure.Error Details:'Scope_Identity' is not a recognized function name.
This is my Stored Procedure:
CREATE PROCEDURE sp_HyperSoftCustomer @Name varchar(25), @Adress varchar(250)as insert into HyperSoftCustomer(Name, Adress, Date) values (@Name, @Adress, GetDate()) Select SCOPE_IDENTITY() GO
I am using MSDE - MSSQLServer
I hope there is anybody who can help me?
Thanks, mexx