|
So up vote the answer in appreciation!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
How can i modify the locked stored Procedure.
I have create stored procedure from C# Sql Projects (clr) and now its Locked and i cant modify it.
|
|
|
|
|
Can you clarify 'locked' is it encrypted, is it read only is you source control stopping you from checking it out.
Never having used the CLR to create a stored proc we need more info to help.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have two sql tables with the following layout:
Table: Calls
ID - int, PK
ReportGroupID - int, FK -> ReportGroups.ID
Name - nvarchar(50)
PhoneNumber - nvarchar(50)
(other data items)
Table: ReportGroups
ID - int, PK
ReportGroupName - nvarchar(50)
I'm developing in C#, VS 2008. The windows forms interface has the following elements:
listbox - lists the Data in Calls.PhoneNumber
- I have this data bound to a dataset that has this data.
form controls - when a user selects an entry in the listbox, the data is displayed in these controls.
- Fairly straightforward data binding.
report groups combo box - this combo box will display the ReportGroupName based on the Calls.ReportGroupID of the selected item in the listbox.
- HELP!!
I managed to get this working to some degree at one point, but saving the data did not work - despite the dataset being persisted between changing of records. Here's what I really want help with:
1) I have not been able to find a good resource that goes in depth into the whole Dataset->DataAdapter->TableAdapter->TableAdapterManager relationship. Any pointers in the right direction either online or printed would be great. I feel like I'm just on the cusp of getting the right information out.
2) If there is a specific and simple way I am missing the implementation of this sort of behavior by using the Visual Studio designers, it would really help things along on my end.
Thanks!
|
|
|
|
|
patzerFish wrote: Dataset->DataAdapter->TableAdapter->TableAdapterManager relationship
Here, I believe, lies the cause of your problem. Once you start relying on the Adapters you are screwed, they are sufficient for simplistic solutions and most devs abandon them fairly early in their career.
Move to a properly implemented DAL (or even better build one). A simple one is fairly easy and should only take a couple of days, you need the CRUD methods that service DATATABLES only, rarely should you be loading a dataset (implies multiple tables returned) from a procedure.
Now you have your data in nice easy to use datatables or List<> you need to use BindingSource as the datasource for your controls, this will give you greater control over your UI.
Note: This is a personal opinion, however I have been using this design successfully for many years.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Thank you.
I'm a little tired of repeatedly stamping on these bloody adapters, I'm tempted to write and article/rant just so I can link to it as a response to this type of issue.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi Mycroft,
I wish someone did, I mean an article, not a rant. And I wouldn't mind at all if you were the one.
I would suggest a simple application with one or two DataGridViews showing some query results, and an add/edit mechanism with one or two Forms so one can add/modify the DB and see the results. These questions are indeed popping up all the time. How about a small company with employees and some company cars?
I would do it myself, however I lack DB experience and authority.
I am willing to contribute as a proofreader, I'll sure come up with some basic why&how questions.
|
|
|
|
|
I actually have a small app that is part of my 'framework' love that word that reads the data structure. I could expand on that I suppose.
My problem is that I know nothing about the adaptors and to be of any value the article should be able to highlight the shortcomings of them rather than just say this is the way you should do it.
I think Dave Kreskowiak may be the person to go to for this, he seems to know an awfull lot about the adapters.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I see your point. If adapters are irresistible but not really the right approach, it deserves explaining. But even then, the right way is what is most important. Let's see how others may react on this.
|
|
|
|
|
How about a partnership - you and Dave - to write about the pros and cons of each approach? 
Will Rogers never met me.
|
|
|
|
|
I think I will start something and then ask Dave to contribute - I will take this to the correct forum.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Cool! I'll be looking forward to seeing the result. I've learned a bunch from both of you, and appreciate your clear, informative presentation style. I expect it will be a great article!
Will Rogers never met me.
|
|
|
|
|
I am a firm believer in not passing datasets (or recordsets) around as universal data objects. I create objects that represent the real-world object, and aggregations of those objects where it makes sense. Those objects may span more than one table, the the consumers of my objects have no knowledge of a database.
For aggregation objects, I create a class that inherits from CollectionBase. That allows me to add a class within the aggregation class that inherits from IComparer so I can provide sorting capabilities. The aggregation class contains the code to handle CRUD, and to allow specifying a filter for querying.
The class that is used for the objects (the item class) collected by the aggregation class inherits from IDataObjectBase. The item class has code to validate, indicate if any values have been changed, etc.
That allows me to use both the aggregation class and the item class for objects that easily bind and can be found in VS's Data Source Explorer.
In a few cases, where I need hierarchical objects, an aggregation class can also be a property within an item class.
Yes, it takes more coding than passing around datasets, but it is more durable. Besides, once you have the first one coded the way you want, you can use it as a template for others, which reduces the coding time.
|
|
|
|
|
You use a more advanced structure than I do  as I use a List<> when coding for the web and datatable for winforms (I like the datagridviews native sorting support for datatable).
You should do an article on your structure, I'd be interested in seeing it as I am always open to new and better ideas.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
There are times I just use List<> or Dictionary<> when I don't need anything more than that. The main reason I use what I described is that 1) I want to encapsulate the CRUD operations at that level so the item classes are lighter weight, and 2) I want to provide other functionality than I get with List<> or Dictionary<>. I could, and have, used factory classes, but for some reason I am partial to strong encapsulation.
|
|
|
|
|
Thanks for the feedback! I have begun to implement my own DAL building the CRUD (create, retrieve, update, delete) methods - but I've managed to do it without using datatables, following the model I used in a project where I was using C# to MySQL:
private static void executeNonQuery(String query) {
SqlCommand command = new SqlCommand(query, Connection);
try {
openConnection();
command.ExecuteNonQuery();
} catch {
throw;
} finally {
closeConnection();
}
}
private static SqlDataReader executeQuery(String query) {
SqlCommand command = new SqlCommand(query, Connection);
SqlDataReader reader;
try {
openConnection();
reader = command.ExecuteReader();
return reader;
} catch {
throw;
} finally {
}
}
These methods are the core of the DAL class. Each other static method (create, retrieve, update, delete) uses one of these two methods and pretty much has the responsibility of building a query string and passing it on. In the case of SELECT queries, a SqlDataReader is returned to be worked on. I haven't felt the need to push this data into a datatable, since it's fairly easy to just move this data into a data object or List<> of data objects. It will require more work on the side of binding data to form controls, but I think I prefer having more control over what is going on anyway.
Thanks again for your quick response!
|
|
|
|
|
Excellent start, however you need to be aware the datareader lock the connection and should be consumed and disposed of ASAP, passing a datareader is asking for problems. For this reason I use datatable.fill. It uses a datareader under the hood but leaves you with a disconnected datatable as a result.
Also I do not make my DAL class static, I often connect to multiple databases. I do have a static class that hold the main copy of the dal but I often create additional copies for specific connections.
I have a single generic method that moves a datatable into a List<> of the object. I also have a UI helper class that binds a List<> to a form, naming discipline is required.
If you send me an email I will send you a copy of the DAL class.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I tried nHibernate once. It was a bit of a pill to get used to but once I did, I found that it really helped me in the whole binding and persisting work. It doesn't fit with my current work of real time processing ( well it would for system configuration and settings ) but otherwise I would use it all the time.
|
|
|
|
|
select gender,amt,age,[address],class ,
Case when gender= '' then 'gender,'when gender is null then 'gender,' Else '' End +
Case when amt = '' then 'amt,'when amt is null then 'amt,' Else '' End +
Case when age = '' then 'age,'when age is null then 'age,' Else '' End +
case when [address] = ''then 'address'when [address] is null then 'address,' else '' End +
Case when class = '' then 'class,'when class is null then 'class,' Else '' End
As [Error Remarks] from mytable
The above query is used to check if any of this row is blank or null display the result in
a column name "error remarks" .
Now the result i m getting is :
gender, amt, age,address,class
all this result is displayed in a single line in a single cell.
now i want the result to be displayed in different line:
like this:
gender,
amt,
age,
[address],
class
all this is single cell with the above query.
i had tried char(13) and 10 . and the value is coming in new line but in sql server textmode and not in grid mode . i want data to be in grid mode.
|
|
|
|
|
scottichrosaviakosmos wrote: i want the result to be displayed in different line
So do this in the UI where display operations belong! Why are doing such a silly thing as formatting your data for the query results grid, you should be the only person looking at SQL Server results.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How did you use the CHAR(13) and CHAR(10)?
|
|
|
|
|
With SQL Enterprise Manager Query Results in grid view you can't display new lines - I came across the same only this week. I think they get converted to spaces deliberately prior to being dumped into the grid so everything stays on the one line. Microsoft made the call - most developers, most of the time, want to see most of their data without stretching the row-height every time they run the query. But trust me, those 13-10s/CR-LFs are working. As someone else said - you are the only person who will see this grid. If you want to whack this query into a .NET winforms grid view, it is possible to get the multi-lines to show there by setting a couple of properties (I could look it up for you, but I won't). And of course, make that query a view, link to the view from Microsoft Access as a linked table and you will see multi-lines. I will now run for cover.
|
|
|
|
|
hi all
i have a very important issue,
read this scenario please
i have three Stored Procedures Sp1,Sp2 and Sp3 .
the first one (Sp1) will execute the second one (Sp2) and save returned data into @tempTB1 and the Second one will execute the third one (Sp3) and save data into @tempTB2.
if I execute the Sp2 it will works and it will returned me all my data from the Sp3 ,but the problem is in the Sp1, when i execute it it will display this Error:
INSERT EXEC statement cannot be nested
I tried to change the place of execute Sp2 and it display me another error:
Cannot use the ROLLBACK statement within an INSERT-EXEC statement.
al hajjaj
|
|
|
|
|
Dear All,
How to create a database in oracle 9i through query.
Mohan Kundena Goud
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
