Need Advise. Problem With Connection Pool. Function Returning SqlDataReader.
Sep 6, 2007
Halo all
I have a problem with "timeout expired. Thei timeout periode elapsed prior to obtaining a connection from the pool. This may have occured because all pooled connections were use and max pool size was reached"
Then i explore and found out that i did not close my SqlDataReader, SqlDataAdapter, SqlCommand or my connection.
But i have a function that return a SqlDataReader.
Is this function will cause a connection problem?
Thanks in advance
Public Function GetDataReader(ByVal strSQL As String, ByVal DBCon As DB.DBConnection) As SqlDataReader
Dim MyCommand As SqlCommand = New SqlCommand(strSQL, DBCon.GetConnection())
If DBCon.GetConnection().State = ConnectionState.Closed Then
DBCon.GetConnection().Open()
End If
Dim dr As SqlDataReader = MyCommand.ExecuteReader()
hi i'm having this error on my application"cannot allocate more connection.connect pool is at maximum increase max pool size" the proble is when i do testing this error does not apply it only Appears when the application is been used by many people How can I resolve this? Thanks
I am opening a simple command against a view which joins 2 tables, so that I can return a column which is defined as a tinyint in one of the tables. The SELECT looks like this: SELECT TreatmentStatus FROM vwReferralWithAdmissionDischarge WHERE ClientNumber = 138238 AND CaseNumber = 1 AND ProviderNumber = 89 The TreatmentStatus column is a tinyint. When I execute that above SQL SELECT statement in SQL Server Management Studio (I am using SQL Server 2005) I get a value of 2. But when I execute the same SQL SELECT statement as a part of a SqlDataReader and SqlCommand, I get a return data type of integer and a value of 1. Why?
My query is as follows:Dim CurrentDate As DateCurrentDate = "09/02/2007" MyCommand = New SqlCommand("SELECT RegisterID FROM Registers WHERE RegisterDate = @RegisterDate AND IsMorningRegister = 1", MyConn)MyCommand.Parameters.Add("@RegisterDate", Data.SqlDbType.DateTime)MyCommand.Parameters("@RegisterDate").Value = CurrentDate My DB table is called RegisterDate and is of type DateTime. The record that should be matched is: Register ID: 13 RegisterDate: 09/02/2007 09:00:00IsMorningRegister: TrueIsAfternoonRegister: False But no records are returned. Any idea why?
I am/have been having an issue with Data Access from a Sql Server database. I have a class that contains a method called "GetDataReader" which takes in a string for the query. Occasionally, the DataReader returned has completely different columns thus resulting in an error when trying to read the data. Below is a small section of code that actually creates the datareader, opens the connection and executes the reader.SqlCommand cmdReader = new SqlCommand(); SqlDataReader drReturn = null; try { // Set the connection for the command object cmdReader.Connection = new SqlConnection(this._csbGlobal.ConnectionString); cmdReader.Connection.Open(); // Set the command type cmdReader.CommandType = Type; // Set the command text cmdReader.CommandText = Sql; // Set the command timeout cmdReader.CommandTimeout = _iTimeout; if (Parameters != null) { // Set the parameters for (int i = 0; i < Parameters.Count; i++) { cmdReader.Parameters.AddWithValue("@" + Parameters.GetKey(i), Parameters[i]); } }
// Get the return value drReturn = cmdReader.ExecuteReader(CommandBehavior.CloseConnection); // Dispose of the command object cmdReader.Dispose(); cmdReader = null; } catch (SqlException e) { this.HandleError(e); } catch (Exception e) { this.HandleError(e); } // Return the reader return drReturn;The data access class is created on each page and only used for that page and any usercontrols on that page. I have checked all the datareaders and they are all being closed. I have been fighting with this issue for about a month and a half now. It only seems to happen when there are a lot of people on the site.If anyone has experienced this or konws how to fix it please let me know. Thanks-Jason
I have a stored proc eg spProcA which does not start a transaction
If I call this proc from ado.net code without any transaction around it affecting the isolation level, the isolation level within the proc call is read committed, as expected
If I then call the same proc within a System.Transacstion.TransactionScope with a different isolation level eg Serializable, then i get the expected isolation level in the proc.
However, if i retry the proc without any transaction around it, the isolation level is now the same as the TransactionScope, (eg Serializable), and this persists until either the connection pool clears down (either because it becomes dormant or via an explicit ClearAllPools.
I can resolve this problem by setting the isolation level in every proc (or call to every proc), but i wondered if i am missing something more straight forward?
I build an asp.net 2.0 (VS 2005) web application and put it on the server. The installed SQL server is SQL server 2003. I get this error message when i keep the application running for 10 or 15 mins. "Timeout expired. The timeout period elapsed prior obtaining a connection from the pool. This may have occure because all pooled connections were in use and max pool size was reached." I made the server timeout unlimited but the error keeps coming up after some time of openning the application. The application session time out redirects the user to the login page and doesn't throw an error like the one i mentioned above. Any idea. Thanks in advance.
hello to all my problem is when in database it reaches nearly 100 active connections (i check it from detach database) , my application does not allow me to open new browser page and show me error like: time out expire. the max connection pool is reached.
System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
Hi guys! I have a dev server that has about 15-25 connections which is fine. ** Looking at performance counters: _global_ under SqlClient: Current # connection pools under SqlClient: Current # pooled connections under SqlClient: Current # pooled and nonpooled connections **
BUT when I look at staging and production I have only 4 or so... WHAT'S GOING ON?!?! The querystring doesn't specify the max pool size, pooling, nor timeout values. So I'm guessing it uses the defaults. ( I also tried putting in max pool size, but it didn't change anything! )
Does anyone know how I can increase the value? It is impacting the performance of the site considerably. Thank you!
From what I understand, some database drivers provide automatic database pooling, in the sense that when an application closes a connection, it is not dropped until after a certain timeout. If the same, or another application, request a connection then the "pooled" one is returned.
What's the situation with C#, .NET Compact Edition and SQLce? Is it a performance hit to open and close a connection, or is some kind of automatic pooling in effect?
Hi All,I'm having a bit of a problem with a c# program which creates and index file/table. The number of records I need to process is around 3500, the main loop. Each of thoes records I need to look at a 2 text fields and create an entry in an index table for each valid word found in thoes fields. I call a stored proc to save the key word. So this stored proc get called numerous time per record.What seems to be happening is proceese about 400 main rcords the errors out with the following error message: {"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached." }Has anyone had a similar problem? if so any suggestions on how to fix, I've been looking at this problem and recoding for the last 4 days! I need some help! Using SQL Server 2000 & .net v1.1Thanks in advance Davej
Hi All, Where Connection pool reside, is in client side(in .net framwork) memory or at server(in sql server) momory or at both side it maintains pool information? Best RegardsImran Zubair
I'm opening a large number of connections to a SQL server 2005 database using a SQL object based on the System.Data.SqlClient library. When I log out of the application, I call the ClearSQLConnectionPool method of the SQL object and this should clear the pool of these connections. This has worked in the past, but lately I have noticed some odd and very annoying behaviour.Very often I log out of the application and few, if any of the connections disappear. Just this morning I opened the application, logged out and all but one of the connections disappeared. Then I opened it again, logged out and none of the connections had gone. If I open the application again all of the past connections are still there plus the new ones.I know it works, its just very inconsistent. The problem may be with IIS, SQL or with library; I just can't tell which.Any ideas?Many thanks
Dear GroupSorry for posting this here. I'm desperate for a solution to thisproblem and thought some of you might have come across it with .NETand SQL Server.Let's assume I've the following code:Private Sub Button4_Click(ByVal sender As System.Object, ByVal e AsSystem.EventArgs) Handles Button4.ClickDim sqlConnection As New System.Data.SqlClient.SqlConnectionsqlConnection.ConnectionString = "workstation id=THEINTREPIDFOX;packetsize=4096;user id=sa;data source=""(local)"";p" & _"ersist security info=False;initial catalog=TestDB"sqlConnection.Open()sqlConnection.Close()sqlConnection = NothingEnd SubWhen I click 100 times on the button the page finally times out with:Timeout expired. The timeout period elapsed prior to obtaining aconnection from the pool. This may have occurred because all pooledconnections were in use and max pool size was reached.Of course I could increase the max pool size but this isn't thesolution to this problem. I wonder what's wrong with my code?According to MSDN, if the connection is closed it's released back intothe pool. Also tried to dispose but nothing helps. It looks like thatit keeps connections and doesn't release them. I'm grateful for anyhints, ideas, suggestions on this problem as I'm very desperate tosolve this.Thanks very much for your time & efforts!Martin
I have seen the threads already in here about connections and SQL Server but I too am having a connection pool problem.
I am explicitly making a connection, creating a command object, executing a datareader then closing the command and then disposing the connection. The connection just prior to the dispose is definately closed.
However when I do an sp_who in SQL Server the connection is still listed. I have a connection timeout of 30 seconds, so I am considering reducing this to 5 seconds but dont wish to. So therefore when a process is called repeatedly instead of using an existing connection in the pool, it thinks that this connection is still active and therefore creates a new connection. Because all of this is in an ascx, I cant code for one connection to stay open for all 30 instances of the control.
This is a problem because I am creating controls at run time and using page.databind and at that point, I exceed the connection pool and therefore the site crashes out to the "SQL Server Timeout" error.
I have recently installed SP2 for SQLServer 2005 and started to have connection pool issues. I have tried to explicitly mention the pool settings in connection string and yet pooling doesn't seem to work. I have one more box with no service packs applied and is pooling is perfect!. Has any one experienced this??
I am using SqlServer2005 as data base for my web application. I am able to connect to the database through DBCP connection pool . Its working fine for 2 user. When ever 2 users do transaction, it works fine. But 3 users do the transaction, its trowning error.Even i increase the pool size the behavior is same. error: com.microsoft.sqlserver.jdbc.SQLServerException: Server failed to resume the transaction, desc: 3f00000002.
For a change I wrote a connection pool to access the DB. I am getting the same error. i.e. Its working fine for 2 user and throws error for more than 2 users.
This code, when concurrently running via several threads, yields the following exception: "The connection was not closed. The connection's current state is open."
My questions are: 1. Why don't .Net allocate another connection from the pool (I try to only concurrently run 2 threads while there are 25 connections in the connection pool) ? 2. How can one explicitly allocate a connection? 3. How do you suggest to solve this problem without a mutex/monitor etc' on the 3 bold lines above and without BeginExequteNonQuery()?
I was wondering if someone could help explaining how the SQL Server handles all incoming connections, I and a friend started a discussion about using the connection pool in ADO.NET or not in a specific case.
Usually when creating like an ASP.NET Page it's recommended to use the connection pool, because it optimizes the performance, by using already created connection, so we don't have to recreate the connection all the time. I have always thought that the connection in the connection pool had an open connection on the server. So 5 connections in the connection pool, would be 5 open connections at the server. But after having the discussion with my friend I am not so sure anymore.
Say that I would create a client application (.NET using ADO.NET) that connects to the database and work with that data. If I then have 1.000 clients and each client have a connection pool with 5 connections in it (I think that the default numbers for the connection pool), then there would be like 5.000 open connections on the SQL Server, where most of the connections actually never do anything more than hanging around and waiting... And then the connectionpool is not 1 per client but 1 per connectionstring.
So if my client scenario would access data from 2 different databases, there would actually be 10.000 open connections at the SQL Server. So now I think that there must be a server connection pool or something to handle the connection from the clients. So that there would only be like 10-50 open connections at the server for the 1.000 Clients that was connected.
How is it? Is there 1 open connection in the server for every connection in the connection pool? If that's the case, it would be better for the SQL Server if I don't not use the connection pool in the client, but instead open the connection when we need it, and then close it, and taking that little performance hit every time, to help the performance on the SQL Server.
An application I developed normally works great, but it seems that when processing a certian record (and none of the others so far), SQL Server throws this error: "Invalid length parameter passed to the substring function."
Private Sub setControls(ByVal dr As SqlDataReader) If (dr.Read()) Then '<--*******problem line*******
The SqlDataReader (orderReader) doesn't blow up or anything until I call .Read() (and, as mentioned, this problem only occurs for one order). What could be happening here?
data source=RemoteHostName;initial catalog=myDb;password=sa;user id=sa; Max pool size = 200;
And now strange thing is happening ,, I am receiving error :
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached
The SqlServer Activity Manager is telling that only 100 connections are pooled, and I guess that the Max pool size is 100, It is not being changed by my Connection string. As I am trying to change the default 100 pool size value to 200.
Huh , So stucked up , how to increase the Max pool size.. Is there any way .
This is my code... public partial class test : System.Web.UI.Page {protected void Page_Load(object sender, EventArgs e) {SqlDatabase database = (SqlDatabase)DatabaseFactory.CreateDatabase("ConnectionString");DbCommand command = database.GetSqlStringCommand("SELECT UserName FROM Users WHERE UserID = '6264'"); using (IDataReader reader = database.ExecuteReader(command)) {if (reader.Read()) {lblTest.Text = "test"; } } } }
This is my error: Server Error in '/' Application.
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.Source Error:
Line 21: //DbCommand command = database.GetStoredProcCommand("CorrUsers_GetByUserId_s"); Line 22: DbCommand command = database.GetSqlStringCommand("SELECT UserName FROM Users WHERE UserID = '6264'"); Line 23: database.ExecuteReader(command); Line 24: using (IDataReader reader = database.ExecuteReader(command)) Line 25: {Source File: d:webwwwrootchacha078dev.cha.toplingo.com est.aspx.cs Line: 23 Stack Trace:
[InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.] System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +1261381 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 Microsoft.Practices.EnterpriseLibrary.Data.Database.GetNewOpenConnection() +195 Microsoft.Practices.EnterpriseLibrary.Data.Database.GetOpenConnection(Boolean disposeInnerConnection) +106 Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command) +62 test.Page_Load(Object sender, EventArgs e) in d:webwwwrootchacha078dev.cha.toplingo.com est.aspx.cs:23 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +33 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1436
Any help with this matter would be very much appreciated. Thank you for your time. Sincerely, Jeffrey Pham
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
Ok bear with me here :). I have a server that is getting about 1000000 hits per day with about 10,000 unique vistors. For the past 2 months I have been revamping my code trying to deal with the increase in traffic. I have 2 boxes, one for the website, and one for the SQL. They are connected through a secure backlink so that I dont have to worry about encrypting the data. So the problem started when I updated the MDAC components a few weeks ago. I started getting these freaking timeout errors, they are up to about 1000 errors per hour. I have been monitoring the connection pooling using the system monitor for about a week and have noticed that I am not maxing out the pooling but I am still dropping connections. I went into the web.config and turned my max connections to 1000. Unfortunately this didn't even put a dent in the problem. It doesn't matter if I have 12 or 212 open connections it still drops them. I am at my wits end with trying to debug this problem. I am wondering if anyone else has had similiar problems recently. Also on a side note I have checked all my connections to make sure that I am closing them. Not only do I close my datareaders but I also set them to nothing. In addition to closing and reseting my datareaders I am also disposing and reseting my connection objects as well as closing them. Any help would be greatly appreciated.
Hi to everyone, I'm facing a problem that drives me crazy. I've a web application that has the following problem. When I test the application on my developement machine all runs fine, but when I put into production server there is a problem in connection pooling. Look at the following image, the blue line is the number of connection in the pool and the violet line is the number of connection reclaimed. From the image it is clear that connection are returned on the pool only in block, maybe when the garbage collection pass and reclaim the object. The strange thing is that on my developement machine all is good, and also I'm using Enterprise Library and the connection are managed internally.- Thanks to everyone for any help.
IF @art <>'/' INSERT INTO @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track WHERE artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and artist.artist=@art Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @cd <>'/' insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cdtitle=@cd Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @tra <> '/' insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and track.track=@tra Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @gen <>'/' insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cdtype=@gen Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @cdate<>'01/01/1900' insert into @result SELECT dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cddate=@cdate Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf
if @label<>'/' insert into @result SELECT dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and label.label=@label Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf return end --------------------------------------------------------------------- upon running executing this function with valid values i am not getting any results. anything is wrong? thank you,
I have a SQL function which returns a varchar(max). This gets truncated when the length is greater than 8000. Could you let me know how do I get the return value in a function without it being truncated.
I have to work with some configuration data that is stored in rows as a comma separated values. Something like this:
Key1 A,1,Z,0;B,2,Y,9;C,,8,X;
Key2 Alpha,101;Beta,102;
Each group of data is separated by a semicolon and each value by a comma. The quantity of values may vary from one key to the other. Over this values sometimes I need to run some selects, so I went with the idea to get it as a table using CLR.
There I find the first problem: I didn't find a way to return a "variable" column with a CLR function, I had to create a SP. Ok, now I execute spGetConfigurationAsTable 'Key1' and I can obtain something like this:
A 1 Z 0
B 2 Y 9
C 3 X 8
But I'm faced with a second problem: How can I run a query over this? I didn't find a way to run a query over an output of a SP. And I can't INSERT the result into a temporary table because I cannot CREATE the table static (remember the columns may differ from one configuration to the other).
So, it seemed a pretty simple task and a neat solution, but I'm kinda stuck. Is there a way to run a query over the SP output? Or is there a way to have a variable table output from a CLR UDF?
Here is the code of the CLR SP I use to obtain the data, and also the (non-working) CLR user defined function.
THANKS!
Code Snippet public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void spGetConfigurationAsTable(string Key) { SqlConnection conn = new SqlConnection("Context Connection=true"); string SqlCmd = string.Format("SELECT Value FROM Configuracion WHERE [Key] = '{0}' ", Key); SqlCommand cmd = new SqlCommand(SqlCmd, conn); conn.Open(); string Value = Convert.ToString(cmd.ExecuteScalar()); if (Value.Length > 0) { char SeparatorRow = ';'; char SeparatorColumn = ','; if (Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length > 35) return; StringBuilder SqlCreate = new StringBuilder("DECLARE @Output TABLE ("); for (int i = 0; i < Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length; i++) { SqlCreate.AppendFormat("[{0}] varchar(50),", Convert.ToChar(65 + i)); } SqlCreate.Remove(SqlCreate.Length - 1, 1); SqlCreate.AppendLine(");"); StringBuilder SqlInsert = new StringBuilder(); foreach (string row in Value.Split(SeparatorRow)) { if (row.Length > 0) { SqlInsert.Append("INSERT INTO @Output VALUES ("); // busca las diferentes "columns" ~ Charly foreach (string column in row.Split(SeparatorColumn)) { SqlInsert.AppendFormat("'{0}',", column); } SqlInsert.Remove(SqlInsert.Length - 1, 1); SqlInsert.AppendLine(");"); } } string SqlSelect = "SELECT * FROM @Output;"; cmd.CommandText = SqlCreate.ToString() + SqlInsert.ToString() + SqlSelect; SqlDataReader reader = cmd.ExecuteReader(); SqlContext.Pipe.Send(reader); reader.Close(); reader.Dispose(); } conn.Close(); conn.Dispose(); cmd.Dispose(); } };
Code Snippet public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static IEnumerable fGetConfigurationAsTable(string Key) { SqlConnection conn = new SqlConnection("Context Connection=true"); string SqlCmd = string.Format("SELECT Value FROM Configuracion WHERE [Key] = '{0}' ", Key); SqlCommand cmd = new SqlCommand(SqlCmd, conn); conn.Open(); string Value = Convert.ToString(cmd.ExecuteScalar()); conn.Close(); conn.Dispose(); cmd.Dispose(); DataTable dt = new DataTable(); if (Value.Length > 0) { char SeparatorRow = ';'; char SeparatorColumn = ','; if (Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length > 35) { // throw exception } string ColumnName; for (int i = 0; i < Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length; i++) { ColumnName = string.Format("[{0}] varchar(50),", Convert.ToChar(65 + i)); dt.Columns.Add(ColumnName, Type.GetType("System.String")); } foreach (string row in Value.Split(SeparatorRow)) { if (row.Length > 0) { dt.Rows.Add(row.Split(SeparatorColumn)); } } } return dt.Rows; } };
I have this assignment where i have a table full of two digit exam scores and I have to write a function that eliminate x number of top values and x number of bottom values and return all the middle values. When the function is called, obviously a number is entered such as 3 and the top 3 and bottom 3 scores are not returned. i.e. SELECT * FROM GetMiddleValues (3);
If anyone has any ideas on how to accomplish this, that would be great.
