Search Results

Search found 28900 results on 1156 pages for 'sql 2005'.

Page 588/1156 | < Previous Page | 584 585 586 587 588 589 590 591 592 593 594 595  | Next Page >

• Improving Performance of Cross-Partition Queries

  Table partitioning was added in SQL Server 2005 and increases performance in most cases but decreases it in others.

  Read the article

• How to use SQL file streaming win32 API and support WCF streaming

  - by Mahesh

  I'm using Sql server file stream type to store large files in the backend. I'm trying to use WCf to stream the file across to the clients. I'm able to get the handle to the file using SQLFileStream (API). I then try to return this stream. I have implemenetd data chunking on the client side to retrive the data from the stream. I'm able to do it for regular filestream and memory stream. Also if i convert then sqlfilestream in to memorystream that also works. The only think that doesn't work is when I try to return sqlfilestream. What am I doing wrong. I have tried both nettcpbinding with streaming enabled and http binding with MTOM encoding. This is the error message am getting : Socket connection was aborted. This could be caused by an error processing your mesage or a receive timeout being exceeded by the remote host, or an underlying network issue.. Local socket timneout was 00:09:59.... Here is my sample code RemoteFileInfo info = new RemoteFileInfo(); info.FileName = "SampleXMLFileService.xml"; string pathName = DataAccess.GetDataSnapshotPath("DataSnapshot1"); SqlConnection connection = DataAccess.GetConnection(); SqlTransaction sqlTransaction = connection.BeginTransaction("SQLSileStreamingTrans"); SqlCommand command = new SqlCommand(); command.Connection = connection; command.Transaction = sqlTransaction; command.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()"; byte[] transcationContext = command.ExecuteScalar() as byte[]; SqlFileStream stream = new SqlFileStream(pathName, transcationContext, FileAccess.Read); // byte[] bytes = new byte[stream.Length]; // stream.Read(bytes, 0, (int) stream.Length); // Stream reeturnStream = stream; // MemoryStream memoryStream = new MemoryStream(bytes); info.FileByteStream = stream; info.Length = info.FileByteStream.Length; connection.Close(); return info; [MessageContract] public class RemoteFileInfo : IDisposable { [MessageHeader(MustUnderstand = true)] public string FileName; [MessageHeader(MustUnderstand = true)] public long Length; [MessageBodyMember(Order = 1)] public System.IO.Stream FileByteStream; public void Dispose() { if (FileByteStream != null) { FileByteStream.Close(); FileByteStream = null; } } } ANy help is appreciated

  Read the article

• Calculate year for end date: PostgreSQL

  - by Dave Jarvis

  Background Users can pick dates as shown in the following screen shot: Any starting month/day and ending month/day combinations are valid, such as: Mar 22 to Jun 22 Dec 1 to Feb 28 The second combination is difficult (I call it the "tricky date scenario") because the year for the ending month/day is before the year for the starting month/day. That is to say, for the year 1900 (also shown selected in the screen shot above), the full dates would be: Dec 22, 1900 to Feb 28, 1901 Dec 22, 1901 to Feb 28, 1902 ... Dec 22, 2007 to Feb 28, 2008 Dec 22, 2008 to Feb 28, 2009 Problem Writing a SQL statement that selects values from a table with dates that fall between the start month/day and end month/day, regardless of how the start and end days are selected. In other words, this is a year wrapping problem. Inputs The query receives as parameters: Year1, Year2: The full range of years, independent of month/day combination. Month1, Day1: The starting day within the year to gather data. Month2, Day2: The ending day within the year (or the next year) to gather data. Previous Attempt Consider the following MySQL code (that worked): end_year = start_year + greatest( -1 * sign( datediff( date( concat_ws('-', year, end_month, end_day ) ), date( concat_ws('-', year, start_month, start_day ) ) ) ), 0 ) How it works, with respect to the tricky date scenario: Create two dates in the current year. The first date is Dec 22, 1900 and the second date is Feb 28, 1900. Count the difference, in days, between the two dates. If the result is negative, it means the year for the second date must be incremented by 1. In this case: Add 1 to the current year. Create a new end date: Feb 28, 1901. Check to see if the date range for the data falls between the start and calculated end date. If the result is positive, the dates have been provided in chronological order and nothing special needs to be done. This worked in MySQL because the difference in dates would be positive or negative. In PostgreSQL, the equivalent functionality always returns a positive number, regardless of their relative chronological order. Question How should the following (broken) code be rewritten for PostgreSQL to take into consideration the relative chronological order of the starting and ending month/day pairs (with respect to an annual temporal displacement)? SELECT m.amount FROM measurement m WHERE (extract(MONTH FROM m.taken) >= month1 AND extract(DAY FROM m.taken) >= day1) AND (extract(MONTH FROM m.taken) <= month2 AND extract(DAY FROM m.taken) <= day2) Any thoughts, comments, or questions? (The dates are pre-parsed into MM/DD format in PHP. My preference is for a pure PostgreSQL solution, but I am open to suggestions on what might make the problem simpler using PHP.) Versions PostgreSQL 8.4.4 and PHP 5.2.10

  Read the article

• LINQ InsertOnSubmit Required Fields needed for debugging

  - by Derek Hunziker

  Hi All, I've been using the ADO.NET Strogly-Typed DataSet model for about 2 years now for handling CRUD and stored procedure executions. This past year I built my first MVC app and I really enjoyed the ease and flexibility of LINQ. Perhaps the biggest selling point for me was that with LINQ I didn't have to create "Insert" stored procedures that would return the SCOPE_IDENTITY anymore (The auto-generated insert statements in the DataSet model were not capable of this without modification). Currently, I'm using LINQ with ASP.NET 3.5 WebForms. My inserts are looking like this: ProductsDataContext dc = new ProductsDataContext(); product p = new product { Title = "New Product", Price = 59.99, Archived = false }; dc.products.InsertOnSubmit(p); dc.SubmitChanges(); int productId = p.Id; So, this product example is pretty basic, right, and in the future, I'll probably be adding more fields to the database such as "InStock", "Quantity", etc... The way I understand it, I will need to add those fields to the database table and then delete and re-add the tables to the LINQ to SQL Class design view in order to refresh the DataContext. Does that sound right? The problem is that any new fields that are non-null are NOT caught by the ASP.NET build processes. For example, if I added a non-null field of "Quantity" to the database, the code above would still build. In the DataSet model, the stored procedure method would accept a certain amount of parameters and would warn me that my Insert would fail if I didn't include a quantity value. The same goes for LINQ stored procedure methods, however, to my knowledge, LINQ doesn't offer a way to auto generate the insert statements and that means I'm back to where I started. The bottom line is if I used insert statements like the one above and I add a non-null field to my database, it would break my app in about 10-20 places and there would be no way for me to detect it. Is my only option to do a solution-side search for the keyword "products.InsertOnSubmit" and make sure the new field is getting assigned? Is there a better way? Thanks!

  Read the article

• Access Database using do.cmd openform where clasue - returning all values

  - by primus285

  DoCmd.OpenForm "Database Search", acFormDS, , srcLastName & "AND " & srcFirstName This is only a small sample of the where clause - there are many more terms. First, there is a set of If, Then type tings up top that set the variable srcLastName and srcFirstName to some value. These are not the problem and work just fine. The trouble is getting them to return all values (for instance if you only want to search by one, on neither(return full database list)) Thus far I have settled for (in the if then section): srcLastName = "[Lastname] =" & Chr(34) & cboLastName & Chr(34) - to search for something and srcLastName = "[Lastname] <" & Chr(34) & "Nuthin" & Chr(34) - to return everything (not equal to an absurd and mispelled database term.) The trouble is that data that is null is also not returned. If I have a null firstname, it will not show up in any search period. is there a term I can set [lastname] and [firstname] equal to that will return EVERYTHING (null, open, data, numbers, wierd stuff and otherwise) in a search an SQL form of "give me everything shes got scotty" if you will. the real issue here comes from the data entry - if I could just know that the people would enter everything 100% of the time, this code would work. but forget to enter the persons age or whatever, and it wont return that entry. So far, the only other solution I have come up with is to put a counter in each if then statement. The count will go up by one for each thing that is being searched by. Then if the count is = 1, then I can search by something like just DoCmd.OpenForm "Database Search", acFormDS, , srcLastName or DoCmd.OpenForm "Database Search", acFormDS, , srcFirstName then revert back to the DoCmd.OpenForm "Database Search", acFormDS, , srcLastName & "AND " & srcFirstName when the count is 2 or more truoble here is that it only works for one (unless I so wanted to create a custon list of 2 combined, 3 combined, 4 combined, but that is not happening)

  Read the article

• Possible uncommitted transactions causing "System.Data.SqlClient.SqlException: Timeout expired" erro

  - by Michael

  My application requires a user to log in and allows them to edit a list of things. However, it seems that if the same user always logs in and out and edits the list, this user will run into a "System.Data.SqlClient.SqlException: Timeout expired." error. I've read comments about increasing the timeout period but I've also read a comment about it possibly caused by uncommitted transactions. And I do have one going in the application. I'll provide the code I'm working with and there is an IF statement in there that I was a little iffy about but it seemed like a reasonable thing to do. I'll just go over what's going on here, there is a list of objects to update or add into the database. New objects created in the application are given an ID of 0 while existing objects have their own ID's generated from the DB. If the user chooses to delete some objects, their IDs are stored in a separate list of Integers. Once the user is ready to save their changes, the two lists are passed into this method. By use of the IF statement, objects with ID of 0 are added (using the Add stored procedure) and those objects with non-zero IDs are updated (using the Update stored procedure). After all this, a FOR loop goes through all the integers in the "removal" list and uses the Delete stored procedure to remove them. A transaction is used for all this. Public Shared Sub UpdateSomethings(ByVal SomethingList As List(Of Something), ByVal RemovalList As List(Of Integer)) Using DBConnection As New SqlConnection(conn) DBConnection.Open() Dim MyTransaction As SqlTransaction MyTransaction = DBConnection.BeginTransaction() Try For Each SomethingItem As Something In SomethingList Using MyCommand As New SqlCommand() MyCommand.Connection = DBConnection If SomethingItem.ID > 0 Then MyCommand.CommandText = "UpdateSomething" Else MyCommand.CommandText = "AddSomething" End If MyCommand.Transaction = MyTransaction MyCommand.CommandType = CommandType.StoredProcedure With MyCommand.Parameters If MyCommand.CommandText = "UpdateSomething" Then .Add("@id", SqlDbType.Int).Value = SomethingItem.ID End If .Add("@stuff", SqlDbType.Varchar).Value = SomethingItem.Stuff End With MyCommand.ExecuteNonQuery() End Using Next For Each ID As Integer In RemovalList Using MyCommand As New SqlCommand("DeleteSomething", DBConnection) MyCommand.Transaction = MyTransaction MyCommand.CommandType = CommandType.StoredProcedure With MyCommand.Parameters .Add("@id", SqlDbType.Int).Value = ID End With MyCommand.ExecuteNonQuery() End Using Next MyTransaction.Commit() Catch ex As Exception MyTransaction.Rollback() 'Exception handling goes here End Try End Using End Sub There are three stored procedures used here as well as some looping so I can see how something can be holding everything up if the list is large enough. Other users can log in to the system at the same time just fine though. I'm using Visual Studio 2008 to debug and am using SQL Server 2000 for the DB.

  Read the article

• Cube project doesn't work because of permissions

  - by sms

  I'm doing "Multidimensional Project" with MS SQL Server 2012 (Server Data Tools - Visual Studio 2010 Shell). I can't run (debug) it. If the data source's impersonation information is set to "use the service account", this error occures: Error 2 Internal error: The operation terminated unsuccessfully. 0 0 Error 3 OLE DB error: OLE DB or ODBC error: Login failed for user 'NT Service\MSSQLServerOLAPService'.; 28000. 0 0 Error 4 Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Data Warehouse', Name of 'Data Warehouse'. 0 0 Error 5 Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Items', Name of 'Items' was being processed. 0 0 Error 6 Errors in the OLAP storage engine: An error occurred while the 'Id' attribute of the 'Items' dimension from the 'Warehouse_MultidimensionalProject_Cube' database was being processed. 0 0 Error 7 Server: The current operation was cancelled because another operation in the transaction failed. 0 0 I guessed that this account has no premissions but (1) I coudn't even add this account (it seems that it doesn't exist) and (2) how is that even possible for it to not have built-it poremissions? When I'm setting impersonation to "use the credentials of current user" (which is the owner of the data source, btw.), another error occures: Error 2 Internal error: The operation terminated unsuccessfully. 0 0 Error 3 The datasource, 'Data Warehouse', contains an ImpersonationMode that is not supported for processing operations. 0 0 Error 4 Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of 'Data Warehouse', Name of 'Data Warehouse'. 0 0 Error 5 Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of 'Items', Name of 'Items' was being processed. 0 0 Error 6 Errors in the OLAP storage engine: An error occurred while the 'Id' attribute of the 'Items' dimension from the 'Warehouse_MultidimensionalProject_Cube' database was being processed. 0 0 Error 7 Server: The current operation was cancelled because another operation in the transaction failed. 0 0 Any help?

  Read the article

• Using VCL for the web (intraweb) as a trick for adding web interface to a legacy non-tiered (2 tiers

  - by user193655

  My team is maintaining a huge Client Server win32 Delphi application. It is a client/server application (Thick client) that uses DevArt (SDAC) components to connect to SQL Server. The business logic is often "trapped" in Component's event handlers, anyway with some degree of refactoring it is doable to move the business logic in common units (a big part of this work has already been done during refactoring... Maintaing legacy applications someone else wrote is very frustrating, but this is a very common job). Now there is the request of a web interface, I have several options of course, in this question i want to focus on the VCL for the web (intraweb) option. The idea is to use the common code (the same pas files) for both the client/server application and the web application. I heard of many people that moved legacy apps from delphi to intraweb, but here I am trying to keep the Thick client too. The idea is to use common code, may be with some compiler directives to write specific code: {$IFDEF CLIENTSERVER} {here goes the thick client specific code} {$ELSE} {here goes the Intraweb specific code} {$ENDIF} Then another problem is the "migration plan", let's say I have 300 features and on the first release I will have only 50 of them available in the web application. How to keep track of it? I was thinking of (ab)using Delphi interfaces to handle this. For example for the User Authentication I could move all the related code in a procedure and declare an interface like: type IUserAuthentication= interface['{0D57624C-CDDE-458B-A36C-436AE465B477}'] procedure UserAuthentication; end; In this way as I implement the IUserAuthentication interface in both the applications (Thick Client and Intraweb) I know that That feature has been "ported" to the web. Anyway I don't know if this approach makes sense. I made a prototype to simulate the whole process. It works for a "Hello world" application, but I wonder if it makes sense on a large application or this Interface idea is only counter-productive and can backfire. My question is: does this approach make sense? (the Interface idea is just an extra idea, it is not so important as the common code part described above) Is it a viable option? I understand it depends a lot of the kind of application, anyway to be generic my one is in the CRM/Accounting domain, and the number of concurrent users on a single installation is typically less than 20 with peaks of 50. EXTRA COMMENT (UPDATE): I ask this question because since I don't have a n-tier application I see Intraweb as the unique option for having a web application that has common code with the thick client. Developing webservices from the Delphi code makes no sense in my specific case, so the alternative I have is to write the web interface using ASP.NET (duplicating the business logic), but in this case I cannot take advantage of the common code in an easy way. Yes I could use dlls maybe, but my code is not suitable for that.

  Read the article

• how can I get data in the table cell that is checked after submit

  - by arnold

  can someone give me help, please. here's my basic html <form action="addSomething.php" method="POST"> <table> <tr> <th>Add Data</th> <th>Description</th> <th>Quantity</th> </tr> <tr> <td><input type="checkbox" name="data[]" value="sample1" /> </td> <td class="desc">Newbie</td> <td>2</td> </tr> <tr> <td><input type="checkbox" name="data[]" value="sample1" /> </td> <td class="desc">Pro</td> <td>1</td> </tr> <tr> <td><input type="checkbox" name="data[]" value="sample1"/> </td> <td class="desc" > Master </td> <td>1</td> </tr> <br/> <input type="submit" name="add" value="SUBMIT"/> ..... how can I get the one with the class "desc" and the column for quantity that is checked after submitting the form because the only I can add when querying in mysql is the value of checkbox but I want also the value of the data in "Description" column and "Quantity" column In my addSomething.php the code I have is if(isset($_POST['add'])) { foreach($_POST['data'] as $value) { $sql = "INSERT INTO tablename (column1) VALUES('$value');" //query stuff } } what I will do , Any hints guys?

  Read the article

• Error while rendering .rdl file into pdf format

  - by Arka Chatterjee

  Hi, I an generating reports using SQL Server reporting services. I have generated a report and have put .rdl report file in the "E" drive. Now, when I am going to render the .rdl report file into pdf format,I am getting the exception : - "An error occurred during local report processing." The stack trace is follows : - " at Microsoft.Reporting.WebForms.LocalReport.InternalRender(String format, Boolean allowInternalRenderers, String deviceInfo, CreateAndRegisterStream createStreamCallback, Warning[]& warnings)\r\n at Microsoft.Reporting.WebForms.LocalReport.InternalRender(String format, Boolean allowInternalRenderers, String deviceInfo, String& mimeType, String& encoding, String& fileNameExtension, String[]& streams, Warning[]& warnings)\r\n at Microsoft.Reporting.WebForms.LocalReport.Render(String format, String deviceInfo, String& mimeType, String& encoding, String& fileNameExtension, String[]& streams, Warning[]& warnings)\r\n at SaltlakeSoft.APEX2.Controllers.TestPageController.RenderReport() in E:\Documents and Settings\Administrator\Desktop\afetbuild15thmayapex2\apex2\Controllers\TestPageController.cs:line 1626\r\n at lambda_method(ExecutionScope , ControllerBase , Object[] )\r\n at System.Web.Mvc.ActionMethodDispatcher.<c_DisplayClass1.b_0(ControllerBase controller, Object[] parameters)\r\n at System.Web.Mvc.ActionMethodDispatcher.Execute(ControllerBase controller, Object[] parameters)\r\n at System.Web.Mvc.ReflectedActionDescriptor.Execute(ControllerContext controllerContext, IDictionary2 parameters)\r\n at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethod(ControllerContext controllerContext, ActionDescriptor actionDescriptor, IDictionary2 parameters)\r\n at System.Web.Mvc.ControllerActionInvoker.<c_DisplayClassa.b_7()\r\n at System.Web.Mvc.ControllerActionInvoker.InvokeActionMethodFilter(IActionFilter filter, ActionExecutingContext preContext, Func`1 continuation)" I am using the following code : - LocalReport report = new LocalReport(); report.ReportPath = @"E:\Report1.rdl"; List employeeCollection = empRepository.FindAll().ToList(); ReportDataSource reportDataSource = new ReportDataSource("dataSource1",employeeCollection); report.DataSources.Clear(); report.DataSources.Add(reportDataSource); report.Refresh(); string reportType = "PDF"; string mimeType; string encoding; string fileNameExtension; string deviceInfo ="" +"PDF" + "8.5in" + "11in" + "0.5in" +"1in" + "1in" +"0.5in" + ""; Warning[] warnings; string[] streams; byte[] renderedBytes; renderedBytes = report.Render(reportType,deviceInfo,out mimeType,out encoding, out fileNameExtension, out streams, out warnings); Response.Clear(); Response.ContentType = mimeType; Response.AddHeader("content-disposition", "attachment; filename=foo." + fileNameExtension); Response.BinaryWrite(renderedBytes); Response.End(); Please help me. Thanks in advance- Arka

  Read the article

• Long Running Stored Proc - Report Progress Using BackgroundWorker & Timer

  - by daveywc

  While a long running stored proc (RMR_Seek) is executing (called via a Linq-To-SQL data context) I am trying to call another stored proc (RMR_GetLatestModelMessage) to check a table for the latest status message. The long running stored proc updates the table in question with status messages as it executes. I want to display the status message on a message panel to advise the user of the status of the execution of Proc_A. For various reasons it is not possible to determine how long RMR_Seek will take to execute so a progress bar with percentage increments is not feasible. I thought I'd found the way to do it by calling the long running stored proc from in a BackgroundWorker process DoWork event handler. This worked fine and allowed me to update my message panel with some dummy status messages that were NOT obtained via Proc_B while Proc_A was running. However now that I have tried to implement this fully by calling Proc_B to obtain the status messages I am running into problems that seem to be related to the mix of the backgroundworker and my System.Windows.Forms.Timer. An extract of the code I am using is below. I have tried many different ways around this but each one seems to present its own set of problems. The code below is problematic in the bw_DoWork event. The RMR_Seek stored proc gets called but does not execute properly - it also seems to be inconsistent as to whether _IsCompleted gets set to true. I'm sure there is a better way to achieve what I am trying to do. private bool _IsCompleted; private void RunRevenueSeek() { if (_SelectedModel == null) { MessageBox.Show("Please select a model from the list and try again.", "Model Generation", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { var bw = new BackgroundWorker(); bw.DoWork += new DoWorkEventHandler(bw_DoWork); ProgressPanelControl.Visible = true; _IsCompleted = false; MessageTimer.Start(); // Has an interval of 3000 bw.RunWorkerAsync(); ProgressLabelControl.Text = "Refreshing Data"; this.Update(); ...more code goes here } } private void bw_DoWork(object sender, DoWorkEventArgs e) { using (var dc = new RevMdlrDataClassesDataContext()) { dc.CommandTimeout = 300; dc.RMR_Seek(_SelectedModel.ModelSet_ID); _IsCompleted = true; } } private void MessageTimer_Tick(object sender, EventArgs e) { string message = ""; if (_IsCompleted) { MessageTimer.Stop(); } else { using (var dc = new RevMdlrDataClassesDataContext()) { dc.CommandTimeout = 300; dc.RMR_GetLatestModelMessage(_SelectedModel.ModelSet_ID, ref message); ProgressLabelControl.Text = message; this.Update(); } } }

  Read the article

• How do I add a column that displays the number of distinct rows to this query?

  - by Fake Code Monkey Rashid

  Hello good people! I don't know how to ask my question clearly so I'll just show you the money. To start with, here's a sample table: CREATE TABLE sandbox ( id integer NOT NULL, callsign text NOT NULL, this text NOT NULL, that text NOT NULL, "timestamp" timestamp with time zone DEFAULT now() NOT NULL ); CREATE SEQUENCE sandbox_id_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER SEQUENCE sandbox_id_seq OWNED BY sandbox.id; SELECT pg_catalog.setval('sandbox_id_seq', 14, true); ALTER TABLE sandbox ALTER COLUMN id SET DEFAULT nextval('sandbox_id_seq'::regclass); INSERT INTO sandbox VALUES (1, 'alpha', 'foo', 'qux', '2010-12-29 16:51:09.897579+00'); INSERT INTO sandbox VALUES (2, 'alpha', 'foo', 'qux', '2010-12-29 16:51:36.108867+00'); INSERT INTO sandbox VALUES (3, 'bravo', 'bar', 'quxx', '2010-12-29 16:52:36.370507+00'); INSERT INTO sandbox VALUES (4, 'bravo', 'foo', 'quxx', '2010-12-29 16:52:47.584663+00'); INSERT INTO sandbox VALUES (5, 'charlie', 'foo', 'corge', '2010-12-29 16:53:00.742356+00'); INSERT INTO sandbox VALUES (6, 'delta', 'foo', 'qux', '2010-12-29 16:53:10.884721+00'); INSERT INTO sandbox VALUES (7, 'alpha', 'foo', 'corge', '2010-12-29 16:53:21.242904+00'); INSERT INTO sandbox VALUES (8, 'alpha', 'bar', 'corge', '2010-12-29 16:54:33.318907+00'); INSERT INTO sandbox VALUES (9, 'alpha', 'baz', 'quxx', '2010-12-29 16:54:38.727095+00'); INSERT INTO sandbox VALUES (10, 'alpha', 'bar', 'qux', '2010-12-29 16:54:46.237294+00'); INSERT INTO sandbox VALUES (11, 'alpha', 'baz', 'qux', '2010-12-29 16:54:53.891606+00'); INSERT INTO sandbox VALUES (12, 'alpha', 'baz', 'corge', '2010-12-29 16:55:39.596076+00'); INSERT INTO sandbox VALUES (13, 'alpha', 'baz', 'corge', '2010-12-29 16:55:44.834019+00'); INSERT INTO sandbox VALUES (14, 'alpha', 'foo', 'qux', '2010-12-29 16:55:52.848792+00'); ALTER TABLE ONLY sandbox ADD CONSTRAINT sandbox_pkey PRIMARY KEY (id); Here's the current SQL query I have: SELECT * FROM ( SELECT DISTINCT ON (this, that) id, this, that, timestamp FROM sandbox WHERE callsign = 'alpha' AND CAST(timestamp AS date) = '2010-12-29' ) playground ORDER BY timestamp DESC This is the result it gives me: id this that timestamp ----------------------------------------------------- 14 foo qux 2010-12-29 16:55:52.848792+00 13 baz corge 2010-12-29 16:55:44.834019+00 11 baz qux 2010-12-29 16:54:53.891606+00 10 bar qux 2010-12-29 16:54:46.237294+00 9 baz quxx 2010-12-29 16:54:38.727095+00 8 bar corge 2010-12-29 16:54:33.318907+00 7 foo corge 2010-12-29 16:53:21.242904+00 This is what I want to see: id this that timestamp count ------------------------------------------------------------- 14 foo qux 2010-12-29 16:55:52.848792+00 3 13 baz corge 2010-12-29 16:55:44.834019+00 2 11 baz qux 2010-12-29 16:54:53.891606+00 1 10 bar qux 2010-12-29 16:54:46.237294+00 1 9 baz quxx 2010-12-29 16:54:38.727095+00 1 8 bar corge 2010-12-29 16:54:33.318907+00 1 7 foo corge 2010-12-29 16:53:21.242904+00 1 EDIT: I'm using PostgreSQL 9.0.* (if that helps any).

  Read the article

• How to add an XML parameter to a stored procedure in C#?

  - by salvationishere

  I am developing a C# web application in VS 2008 which interacts with my Adventureworks database in my SQL Server 2008. Now I am trying to add new records to one of the tables which has an XML column in it. How do I do this? This is the error I'm getting: System.Data.SqlClient.SqlException was caught Message="XML Validation: Text node is not allowed at this location, the type was defined with element only content or with simple content. Location: /" Source=".Net SqlClient Data Provider" ErrorCode=-2146232060 Class=16 LineNumber=22 Number=6909 Procedure="AppendDataC" Server="." State=1 StackTrace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at ADONET_namespace.ADONET_methods.AppendDataC(DataRow d, Hashtable ht) in C:\Documents and Settings\Admin\My Documents\Visual Studio 2008\Projects\AddFileToSQL\AddFileToSQL\ADONET methods.cs:line 212 InnerException: And this is a portion of my code in C#: try { SqlConnection conn2 = new SqlConnection(connString); SqlCommand cmd = conn2.CreateCommand(); cmd.CommandText = "dbo.AppendDataC"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = conn2; ... sqlParam10.SqlDbType = SqlDbType.VarChar; SqlParameter sqlParam11 = cmd.Parameters.AddWithValue("@" + ht["@col11"], d[10]); sqlParam11.SqlDbType = SqlDbType.VarChar; SqlParameter sqlParam12 = cmd.Parameters.AddWithValue("@" + ht["@col12"], d[11]); sqlParam12.SqlDbType = SqlDbType.Xml; ... conn2.Open(); cmd.ExecuteNonQuery(); //This is the line it fails on and then jumps //to the Catch statement conn2.Close(); errorMsg = "The Person.Contact table was successfully updated!"; } catch (Exception ex) { Right now in my text input MDF file I have the XML parameter as: '<Products><id>3</id><id>6</id><id>15</id></Products>' Is this valid format for XML?

  Read the article

• Restoring multiple database backups in a transaction

  - by Raghu Dodda

  I wrote a stored procedure that restores as set of the database backups. It takes two parameters - a source directory and a restore directory. The procedure looks for all .bak files in the source directory (recursively) and restores all the databases. The stored procedure works as expected, but it has one issue - if I uncomment the try-catch statements, the procedure terminates with the following error: error_number = 3013 error_severity = 16 error_state = 1 error_message = DATABASE is terminating abnormally. The weird part is sometimes (it is not consistent) the restore is done even if the error occurs. The procedure: create proc usp_restore_databases ( @source_directory varchar(1000), @restore_directory varchar(1000) ) as begin declare @number_of_backup_files int -- begin transaction -- begin try -- step 0: Initial validation if(right(@source_directory, 1) <> '\') set @source_directory = @source_directory + '\' if(right(@restore_directory, 1) <> '\') set @restore_directory = @restore_directory + '\' -- step 1: Put all the backup files in the specified directory in a table -- declare @backup_files table ( file_path varchar(1000)) declare @dos_command varchar(1000) set @dos_command = 'dir ' + '"' + @source_directory + '*.bak" /s/b' /* DEBUG */ print @dos_command insert into @backup_files(file_path) exec xp_cmdshell @dos_command delete from @backup_files where file_path IS NULL select @number_of_backup_files = count(1) from @backup_files /* DEBUG */ select * from @backup_files /* DEBUG */ print @number_of_backup_files -- step 2: restore each backup file -- declare backup_file_cursor cursor for select file_path from @backup_files open backup_file_cursor declare @index int; set @index = 0 while(@index < @number_of_backup_files) begin declare @backup_file_path varchar(1000) fetch next from backup_file_cursor into @backup_file_path /* DEBUG */ print @backup_file_path -- step 2a: parse the full backup file name to get the DB file name. declare @db_name varchar(100) set @db_name = right(@backup_file_path, charindex('\', reverse(@backup_file_path)) -1) -- still has the .bak extension /* DEBUG */ print @db_name set @db_name = left(@db_name, charindex('.', @db_name) -1) /* DEBUG */ print @db_name set @db_name = lower(@db_name) /* DEBUG */ print @db_name -- step 2b: find out the logical names of the mdf and ldf files declare @mdf_logical_name varchar(100), @ldf_logical_name varchar(100) declare @backup_file_contents table ( LogicalName nvarchar(128), PhysicalName nvarchar(260), [Type] char(1), FileGroupName nvarchar(128), [Size] numeric(20,0), [MaxSize] numeric(20,0), FileID bigint, CreateLSN numeric(25,0), DropLSN numeric(25,0) NULL, UniqueID uniqueidentifier, ReadOnlyLSN numeric(25,0) NULL, ReadWriteLSN numeric(25,0) NULL, BackupSizeInBytes bigint, SourceBlockSize int, FileGroupID int, LogGroupGUID uniqueidentifier NULL, DifferentialBaseLSN numeric(25,0) NULL, DifferentialBaseGUID uniqueidentifier, IsReadOnly bit, IsPresent bit ) insert into @backup_file_contents exec ('restore filelistonly from disk=' + '''' + @backup_file_path + '''') select @mdf_logical_name = LogicalName from @backup_file_contents where [Type] = 'D' select @ldf_logical_name = LogicalName from @backup_file_contents where [Type] = 'L' /* DEBUG */ print @mdf_logical_name + ', ' + @ldf_logical_name -- step 2c: restore declare @mdf_file_name varchar(1000), @ldf_file_name varchar(1000) set @mdf_file_name = @restore_directory + @db_name + '.mdf' set @ldf_file_name = @restore_directory + @db_name + '.ldf' /* DEBUG */ print 'mdf_logical_name = ' + @mdf_logical_name + '|' + 'ldf_logical_name = ' + @ldf_logical_name + '|' + 'db_name = ' + @db_name + '|' + 'backup_file_path = ' + @backup_file_path + '|' + 'restore_directory = ' + @restore_directory + '|' + 'mdf_file_name = ' + @mdf_file_name + '|' + 'ldf_file_name = ' + @ldf_file_name restore database @db_name from disk = @backup_file_path with move @mdf_logical_name to @mdf_file_name, move @ldf_logical_name to @ldf_file_name -- step 2d: iterate set @index = @index + 1 end close backup_file_cursor deallocate backup_file_cursor -- end try -- begin catch -- print error_message() -- rollback transaction -- return -- end catch -- -- commit transaction end Does anybody have any ideas why this might be happening? Another question: is the transaction code useful ? i.e., if there are 2 databases to be restored, will SQL Server undo the restore of one database if the second restore fails?

  Read the article

• Proper way to use Linq with WPF

  - by Ingó Vals

  I'm looking for a good guide into the right method of using Linq to Sql together with WPF. Most guides only go into the bare basics like how to show data from a database but noone I found goes into how to save back to the database. Can you answer or point out to me a guide that can answer these questions. I have a separate Data project because the same data will also be used in a web page so I have the repository method. That means I have a seperate class that uses the DataContext and there are methods like GetAllCompanies() and GetCompanyById ( int id ). 1) Where there are collections is it best to return as a IQueryable or should I return a list? Inside the WPF project I have seen reccomendations to wrap the collection in a ObservabgleCollection. 2) Why should I use ObservableCollection and should I use it even with Linq / IQueryable Some properties of the linq entities should be editable in the app so I set them to two-way mode. That would change the object in the observableCollection. 3) Is the object in the ObservableCollection still a instance of the original linq entity and so is the change reflected in the database ( when submitchanges is called ) I should have somekind of save method in the repository. But when should I call it? What happens if someone edits a field but decides not to save it, goes to another object and edits it and then press save. Doesn't the original change also save? When does it not remember the changes to a linq entity object anymore. Should I instance the Datacontext class in each method so it loses scope when done. 4) When and how to call the SubmitChanges method 5) Should I have the DataContext as a member variable of the repository class or a method variable To add a new row I should create a new object in a event ( "new" button push ) and then add it to the database using a repo method. 6) When I add the object to the database there will be no new object in the ObservableCollection. Do I refresh somehow. 7) I wan't to reuse the edit window when creating new but not sure how to dynamically changing from referencing selected item from a listview to this new object. Any examples you can point out.

  Read the article

• Advice on software / database design to avoid using cursors when updating database

  - by Remnant

  I have a database that logs when an employee has attended a course and when they are next due to attend the course (courses tend to be annual). As an example, the following employee attended course '1' on 1st Jan 2010 and, as the course is annual, is due to attend next on the 1st Jan 2011. As today is 20th May 2010 the course status reads as 'Complete' i.e. they have done the course and do not need to do it again until next year: EmployeeID CourseID AttendanceDate DueDate Status 123456 1 01/01/2010 01/01/2011 Complete In terms of the DueDate I calculate this in SQL when I update the employee's record e.g. DueDate = AttendanceDate + CourseFrequency (I pull course frequency this from a separate table). In my web based app (asp.net mvc) I pull back this data for all employees and display it in a grid like format for HR managers to review. This allows HR to work out who needs to go on courses. The issue I have is as follows. Taking the example above, suppose today is 2nd Jan 2011. In this case, employee 123456 is now overdue for the course and I would like to set the Status to Incomplete so that the HR manager can see that they need to action this i.e. get employee on the course. I could build a trigger in the database to run overnight to update the Status field for all employees based on the current date. From what I have read I would need to use cursors to loop over each row to amend the status and this is considered bad practice / inefficient or at least something to avoid if you can??? Alternatively, I could compute the Status in my C# code after I have pulled back the data from the database and before I display it on screen. The issue with this is that the Status in the database would not necessarily match what is shown on screen which just feels plain wrong to me. Does anybody have any advice on the best practice approach to such an issue? It helps, if I did use a cursor I doubt I would be looping over more than 1000 records at any given time. Maybe this is such small volume that using cursors is okay?

  Read the article

• Datagridview error

  - by Simon

  I have two datagridviews. So for the second one, i just copy-pasted the code from the first and changed where the difference was. But i get an error at the secod data grid when i want to view the result of my sql code. Translated in english the error show something like that there was no value given to at least one required parameter. Please help! private void button1_Click(object sender, EventArgs e) { string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=save.mdb"; try { database = new OleDbConnection(connectionString); database.Open(); date = DateTime.Now.ToShortDateString(); string queryString = "SELECT zivila.naziv,(obroki_save.skupaj_kalorij/zivila.kalorij)*100 as Kolicina_v_gramih " + "FROM (users LEFT JOIN obroki_save ON obroki_save.ID_uporabnika=users.ID)" + " LEFT JOIN zivila ON zivila.ID=obroki_save.ID_zivila " + " WHERE users.ID= " + a.ToString(); loadDataGrid(queryString); } catch (Exception ex) { MessageBox.Show(ex.Message); return; } } public void loadDataGrid(string sqlQueryString) { OleDbCommand SQLQuery = new OleDbCommand(); DataTable data = null; dataGridView1.DataSource = null; SQLQuery.Connection = null; OleDbDataAdapter dataAdapter = null; dataGridView1.Columns.Clear(); // <-- clear columns SQLQuery.CommandText = sqlQueryString; SQLQuery.Connection = database; data = new DataTable(); dataAdapter = new OleDbDataAdapter(SQLQuery); dataAdapter.Fill(data); dataGridView1.DataSource = data; dataGridView1.AllowUserToAddRows = false; dataGridView1.ReadOnly = true; dataGridView1.Columns[0].Visible = true; } private void Form8_Load(object sender, EventArgs e) { } private void button2_Click(object sender, EventArgs e) { string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=save.mdb"; try { database = new OleDbConnection(connectionString); database.Open(); date = DateTime.Now.ToShortDateString(); string queryString = "SELECT skupaj_kalorij " + "FROM obroki_save " + " WHERE users.ID= " + a.ToString(); loadDataGrid2(queryString); } catch (Exception ex) { MessageBox.Show(ex.Message); return; } } public void loadDataGrid2(string sqlQueryString) { OleDbCommand SQLQuery = new OleDbCommand(); DataTable data = null; dataGridView2.DataSource = null; SQLQuery.Connection = null; OleDbDataAdapter dataAdapter = null; dataGridView2.Columns.Clear(); // <-- clear columns SQLQuery.CommandText = sqlQueryString; SQLQuery.Connection = database; data = new DataTable(); dataAdapter = new OleDbDataAdapter(SQLQuery); dataAdapter.Fill(data); dataGridView2.DataSource = data; dataGridView2.AllowUserToAddRows = false; dataGridView2.ReadOnly = true; dataGridView2.Columns[0].Visible = true; }

  Read the article

• Constructor or Explicit cast

  - by Felan

  In working with Linq to Sql I create a seperate class to ferry data to a web page. To simplify creating these ferry objects I either use a specialized constructor or an explicit conversion operator. I have two questions. First which approach is better from a readibility perspective? Second while the clr code that is generated appeared to be the same to me, are there situations where one would be treated different than the other by the compiler (in lambda's or such). Example code (DatabaseFoo uses specialized constructor and BusinessFoo uses explicit operator): public class DatabaseFoo { private static int idCounter; // just to help with generating data public int Id { get; set; } public string Name { get; set; } public DatabaseFoo() { Id = idCounter++; Name = string.Format("Test{0}", Id); } public DatabaseFoo(BusinessFoo foo) { this.Id = foo.Id; this.Name = foo.Name; } } public class BusinessFoo { public int Id { get; set; } public string Name { get; set; } public static explicit operator BusinessFoo(DatabaseFoo foo) { return FromDatabaseFoo(foo); } public static BusinessFoo FromDatabaseFoo(DatabaseFoo foo) { return new BusinessFoo {Id = foo.Id, Name = foo.Name}; } } public class Program { static void Main(string[] args) { Console.WriteLine("Creating the initial list of DatabaseFoo"); IEnumerable<DatabaseFoo> dafoos = new List<DatabaseFoo>() { new DatabaseFoo(), new DatabaseFoo(), new DatabaseFoo(), new DatabaseFoo(), new DatabaseFoo(), new DatabaseFoo()}; foreach(DatabaseFoo dafoo in dafoos) Console.WriteLine(string.Format("{0}\t{1}", dafoo.Id, dafoo.Name)); Console.WriteLine("Casting the list of DatabaseFoo to a list of BusinessFoo"); IEnumerable<BusinessFoo> bufoos = from x in dafoos select (BusinessFoo) x; foreach (BusinessFoo bufoo in bufoos) Console.WriteLine(string.Format("{0}\t{1}", bufoo.Id, bufoo.Name)); Console.WriteLine("Creating a new list of DatabaseFoo by calling the constructor taking BusinessFoo"); IEnumerable<DatabaseFoo> fufoos = from x in bufoos select new DatabaseFoo(x); foreach(DatabaseFoo fufoo in fufoos) Console.WriteLine(string.Format("{0}\t{1}", fufoo.Id, fufoo.Name)); } }

  Read the article

• LINQ Query Returning Multiple Copies Of First Result

  - by Mike G

  I'm trying to figure out why a simple query in LINQ is returning odd results. I have a view defined in the database. It basically brings together several other tables and does some data munging. It really isn't anything special except for the fact that it deals with a large data set and can be a bit slow. I want to query this view based on a long. Two sample queries below show different queries to this view. var la = Runtime.OmsEntityContext.Positions.Where(p => p.AccountNumber == 12345678).ToList(); var deDa = Runtime.OmsEntityContext.Positions.Where(p => p.AccountNumber == 12345678).Select(p => new { p.AccountNumber, p.SecurityNumber, p.CUSIP }).ToList(); The first one should hand back a List. The second one will be a list of anonymous objects. When I do these queries in entities framework the first one will hand me back a list of results where they're all exactly the same. The second query will hand me back data where the account number is the one that I queried and the other values differ. This seems to do this on a per account number basis, ie if I were to query for one account number or another all the Position objects for one account would have the same value (the first one in the list of Positions for that account) and the second account would have a set of Position objects that all had the same value (again, the first one in it's list of Position objects). I can write SQL that is in effect the same as either of the two EF queries. They both come back with results (say four) that show the correct data, one account number with different securities numbers. Why does this happen??? Is there something that I could be doing wrong so that if I had four results for the first query above that the first record's data also appears in the 2-4th's objects??? I cannot fathom what would/could be causing this. I've searched Google for all kinds of keywords and haven't seen anyone with this issue. We partial class out the Positions class for added functionality (smart object) and some smart properties. There are even some constructors that provide some view model type support. None of this is invoked in the request (I'm 99% sure of this). However, we do this same pattern all over the app. The only thing I can think of is that the mapping in the EDMX is screwy. Is there a way that this would happen if the "primary keys" in the EDMX were not in fact unique given the way the view is constructed? I'm thinking that the dev who imported this model into the EDMX let the designer auto select what would be unique. Any help would give a haggered dev some hope!

  Read the article

• MySQL Connection error in spring MVC

  - by nidhi

  i am working on spring MVC facing the following exception while connecting to my sql. Blocorg.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.) at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:572) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:636) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:665) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:673) at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:728) at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:744) at org.springframework.jdbc.core.JdbcTemplate.queryForInt(JdbcTemplate.java:775) at com.trackmeetings.dao.InboxDAOImpl.getNotificationsCount(InboxDAOImpl.java:51) at com.trackmeetings.service.InboxService.getNotificationsCount(InboxService.java:56) at com.trackmeetings.controller.HomePageController.getLogInUserDetails(HomePageController.java:80) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.springframework.web.bind.annotation.support.HandlerMethodInvoker.invokeHandlerMethod(HandlerMethodInvoker.java:175) at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.invokeHandlerMethod(AnnotationMethodHandlerAdapter.java:421) at org.springframework.web.servlet.mvc.annotation.AnnotationMethodHandlerAdapter.handle(AnnotationMethodHandlerAdapter.java:409) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:774) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:719) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:644) at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:560) at javax.servlet.http.HttpServlet.service(HttpServlet.java:710) at javax.servlet.http.HttpServlet.service(HttpServlet.java:803) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at com.trackmeetings.util.NoCacheFilter.doFilter(NoCacheFilter.java:56) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:175) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:844) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583) at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447) at java.lang.Thread.run(Thread.java:680) Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.) at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1225) at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:880) at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111) at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77) ... 38 more Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failurekquote how can i resolve this. please help,

  Read the article

• How to perform add/update of a model object that contains EntitySet

  - by David Liddle

  I have a similar concept to the SO questions/tags scenario however am trying to decide the best way of implementation. Tables Questions, QuestionTags and Tags Questions QuestionTags Tags --------- ------------ ---- QID QID TID QName TID TName When adding/updating a question I have 2 textboxes. The important part is a single textbox that allows users to enter in multiple Tags separated by spaces. I am using Linq2Sql so the Questions model has an EntitySet of QuestionTags with then link to Tags. My question is regarding the adding/updating of Questions (part 1), and also how to best show QuestionTags for a Question (part 2). Part 1 Before performing an add/update, my service layer needs to deal with 3 scenarios before passing to their respective repositories. Insert Tags that do not already exist Insert/Update Question Insert QuestionTags - when updating need to remove existing QuestionTags Here is my code below however started to get into a bit of a muddle. I've created extension methods on my repositories to get Tags WithNames etc. public void Add(Question q, string tags) { var tagList = tags.Split(new string[] { " " }, StringSplitOptions.RemoveEmptyEntries).ToList(); using (DB.TransactionScope ts = new DB.TransactionScope()) { var existingTags = TagsRepository.Get() .WithName(tagList) .ToList(); var newTags = (from t in tagList select new Tag { TName = t }).Except(existingTags, new TagsComparer()).ToList(); TagsRepository.Add(newTags); //need to insert QuestionTags QuestionsRepository.Add(q); ts.Complete(); } } Part 2 My second question is, when displaying a list of Questions how is it best to show their QuestionTags? For example, I have an Index view that shows a list of Questions in a table. One of the columns shows an image and when the user hovers over it shows the list of Tags. My current implementation is to create a custom ViewModel and show a List of QuestionIndexViewModel in the View. QuestionIndexViewModel { Question Question { get; set; } string Tags { get; set; } } However, this seems a bit clumsy and quite a few DB calls. public ViewResult Index() { var model= new List<QuestionIndexViewModel>(); //make a call to get a list of questions //foreach question make a call to get their QuestionTags, //to be able to get their Tag names and then join them //to form a single string. return View(model); } Also, just for test purposes using SQL Profiler, I decided to iterate through the QuestionTags entity set of a Question in my ViewModel however nothing was picked up in Profiler? What would be the reason for this?

  Read the article

• How can I provide values for non-grouped columns in NHibernate?

  - by ddc0660

  I have a criteria query: Session.CreateCriteria<Sell043Report>() .SetProjection(.ProjectionList() .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.location)) .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.agent)) .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.cusip)) .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.SettlementDate)) .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.salePrice)) .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.foreignFx)) .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.batchNumber)) .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.origSaleDate)) .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.planName)) .Add(LambdaProjection.GroupProperty<Sell043Report>(r => r.dateTimeAdded)) .Add(LambdaProjection.Sum<Sell043Report>(r => r.shares)) .Add(LambdaProjection.Sum<Sell043Report>(r => r.netMoney)) .Add(LambdaProjection.Sum<Sell043Report>(r => r.grossMoney)) .Add(LambdaProjection.Sum<Sell043Report>(r => r.taxWithheld)) .Add(LambdaProjection.Sum<Sell043Report>(r => r.fees))) .List<Sell043Report>(); that generates the following SQL: SELECT this_.location as y0_, this_.agent as y1_, this_.cusip as y2_, this_.SettlementDate as y3_, this_.salePrice as y4_, this_.foreignFx as y5_, this_.batchNumber as y6_, this_.origSaleDate as y7_, this_.planName as y8_, this_.dateTimeAdded as y9_, sum(this_.shares) as y10_, sum(this_.netMoney) as y11_, sum(this_.grossMoney) as y12_, sum(this_.taxWithheld) as y13_, sum(this_.fees) as y14_ FROM MIS_IPS_Sell043Report this_ GROUP BY this_.location, this_.agent, this_.cusip, this_.SettlementDate, this_.salePrice, this_.foreignFx, this_.batchNumber, this_.origSaleDate, this_.planName, this_.dateTimeAdded however the Sell043Report table has additional columns than those listed in the SELECT statement so I'm receiving this error when attempting to get a list of Sell043Reports: System.ArgumentException: The value "System.Object[]" is not of type "xyz.Sell043Report" and cannot be used in this generic collection. I suspect the problem is that I'm not selecting all of the columns for a Sell043Report and so it doesn't know how to map the dataset to the object. I'm trying to achieve something like this: SELECT this_.location as y0_, this_.agent as y1_, this_.cusip as y2_, this_.SettlementDate as y3_, this_.salePrice as y4_, this_.foreignFx as y5_, this_.batchNumber as y6_, this_.origSaleDate as y7_, this_.planName as y8_, this_.dateTimeAdded as y9_, sum(this_.shares) as y10_, sum(this_.netMoney) as y11_, sum(this_.grossMoney) as y12_, sum(this_.taxWithheld) as y13_, sum(this_.fees) as y14_, '' as Address1, '' as Address2 // etc FROM MIS_IPS_Sell043Report this_ GROUP BY this_.location, this_.agent, this_.cusip, this_.SettlementDate, this_.salePrice, this_.foreignFx, this_.batchNumber, this_.origSaleDate, this_.planName, this_.dateTimeAdded How can I do this using NHibernate?

  Read the article

• Multiple column subselect in mysql 5 (5.1.42)

  - by rubber boots

  This one seems to be a simple problem, but I can't make it work in a single select or nested select. Retrieve the authors and (if any) advisers of a paper (article) into one row. I order to explain the problem, here are the two data tables (pseudo) papers (id, title, c_year) persons (id, firstname, lastname) plus a link table w/one extra attribute (pseudo): paper_person_roles( paper_id person_id act_role ENUM ('AUTHOR', 'ADVISER') ) This is basically a list of written papers (table: papers) and a list of staff and/or students (table: persons) An article my have (1,N) authors. An article may have (0,N) advisers. A person can be in 'AUTHOR' or 'ADVISER' role (but not at the same time). The application eventually puts out table rows containing the following entries: TH: || Paper_ID | Author(s) | Title | Adviser(s) | TD: || 21334 |John Doe, Jeff Tucker|Why the moon looks yellow|Brown, Rayleigh| ... My first approach was like: select/extract a full list of articles into the application, eg.SELECT q.id, q.title FROM papers AS q ORDER BY q.c_year and save the results of the query into an array (in the application). After this step, loop over the array of the returned information and retrieve authors and advisers (if any), via prepared statement (? is the paper's id) from the link table like:APPLICATION_LOOP(paper_ids in array) SELECT p.lastname, p.firstname, r.act_role FROM persons AS p, paper_person_roles AS r WHERE p.id=r.person_id AND r.paper_id = ? # The application does further processing from here (pseudo): foreach record from resulting records if record.act_role eq 'AUTHOR' then join to author_column if record.act_role eq 'ADVISER' then join to avdiser_column end print id, author_column, title, adviser_column APPLICATION_LOOP This works so far and gives the desired output. Would it make sense to put the computation back into the DB? I'm not very proficient in nontrivial SQL and can't find a solution with a single (combined or nested) select call. I tried sth. like SELECT q.title (CONCAT_WS(' ', (SELECT p.firstname, p.lastname AS aunames FROM persons AS p, paper_person_roles AS r WHERE q.id=r.paper_id AND r.act_role='AUTHOR') ) ) AS aulist FROM papers AS q, persons AS p, paper_person_roles AS r in several variations, but no luck ... Maybe there is some chance? Thanks in advance r.b.

  Read the article

• Database Schema Versioning Strategies

  - by Jack Ryan

  I work on a project that uses a reasonably large database, the live version weighing in at somewhere around 60-80GB. The live database is the only real definitive source of our schema, and because of its size duplicating this database is too slow to be done often. This means we have ended up developing our database schema in a pretty ad hoc way, using sql compare to migrate changes from dev dbs to the live system, and only wiping our dev dbs every month or two. I am hoping to get some pointers on how to improve our database development work flow so that we have a little more control. Some things to think about: Currently nobody is really in charge of the database schema, all developers can change it if they need to, though generally these decisions are talked about before they are done. There are stored procedures, functions, and views in the database. These should probably be dumped to files so they can be reloaded on every build. Schema changes should probably be checked in as scripts. We have started to do this recently. However all our scripts must then be numbered (because there may be dependencies between them), and must be re runnable (because our build script currently runs them all in order). This makes them hard to read because they are full of conditionals that check whether tables or columns already exist. This is a step that is often forgotten by developers. Getting a new database should be quick and easy. This is currently a big problem, it takes several hours to get a copy of last nights backup and restore it onto a dev machine. Some mechanism needs to be in place to allow developers to update static data. We have tables that contain data that is never updated through the application, but does potentially need to be changed when we do a new release (often this drives dropdowns). The whole thing needs to be runnable as part of a build script. Are there any tools that can be used to help to do this? Eventually I would like to be at a point where a new DB can be built from scratch without copying any data from the live system. I don't mind writing some scripts to glue all the steps together but each part should be easily editable so that we continue to use it rather than make changes directly on DBs.

  Read the article

• Large transactions causing "System.Data.SqlClient.SqlException: Timeout expired" error?

  - by Michael

  My application requires a user to log in and allows them to edit a list of things. However, it seems that if the same user always logs in and out and edits the list, this user will run into a "System.Data.SqlClient.SqlException: Timeout expired." error. I've read comments about increasing the timeout period but I've also read a comment about it possibly caused by uncommitted transactions. And I do have one going in the application. I'll provide the code I'm working with and there is an IF statement in there that I was a little iffy about but it seemed like a reasonable thing to do. I'll just go over what's going on here, there is a list of objects to update or add into the database. New objects created in the application are given an ID of 0 while existing objects have their own ID's generated from the DB. If the user chooses to delete some objects, their IDs are stored in a separate list of Integers. Once the user is ready to save their changes, the two lists are passed into this method. By use of the IF statement, objects with ID of 0 are added (using the Add stored procedure) and those objects with non-zero IDs are updated (using the Update stored procedure). After all this, a FOR loop goes through all the integers in the "removal" list and uses the Delete stored procedure to remove them. A transaction is used for all this. Public Shared Sub UpdateSomethings(ByVal SomethingList As List(Of Something), ByVal RemovalList As List(Of Integer)) Using DBConnection As New SqlConnection(conn) DBConnection.Open() Dim MyTransaction As SqlTransaction MyTransaction = DBConnection.BeginTransaction() Try For Each SomethingItem As Something In SomethingList Using MyCommand As New SqlCommand() MyCommand.Connection = DBConnection If SomethingItem.ID > 0 Then MyCommand.CommandText = "UpdateSomething" Else MyCommand.CommandText = "AddSomething" End If MyCommand.Transaction = MyTransaction MyCommand.CommandType = CommandType.StoredProcedure With MyCommand.Parameters If MyCommand.CommandText = "UpdateSomething" Then .Add("@id", SqlDbType.Int).Value = SomethingItem.ID End If .Add("@stuff", SqlDbType.Varchar).Value = SomethingItem.Stuff End With MyCommand.ExecuteNonQuery() End Using Next For Each ID As Integer In RemovalList Using MyCommand As New SqlCommand("DeleteSomething", DBConnection) MyCommand.Transaction = MyTransaction MyCommand.CommandType = CommandType.StoredProcedure With MyCommand.Parameters .Add("@id", SqlDbType.Int).Value = ID End With MyCommand.ExecuteNonQuery() End Using Next MyTransaction.Commit() Catch ex As Exception MyTransaction.Rollback() 'Exception handling goes here End Try End Using End Sub There are three stored procedures used here as well as some looping so I can see how something can be holding everything up if the list is large enough. Other users can log in to the system at the same time just fine though. I'm using Visual Studio 2008 to debug and am using SQL Server 2000 for the DB.

  Read the article

< Previous Page | 584 585 586 587 588 589 590 591 592 593 594 595  | Next Page >