|
|
In a C# 2010 application that uses linq to sql datacontext object that connects to a sql server 2008 r2 database, I need to do a maintenance change to modify the size of a notes field from varchar(500) to varchar(900).
I am getting the error message, "changing the data type of column 'field2' on table 'test1' from 'varchar(500)' to 'varchar (900)' causes the following indexes to exceed the maximum index size of 900 bytes: index2 when change the column size using sql server management studio 2008 r2.
I am assuming that the datacontext object added this notes field to the index? If I am correct or not, can you tell me why a data field of this size would be used as a foreign key by the data context object.
I am trying to determine what my options are to fix this problem. The following is what I noted so far:
1. I saw the table I am referring to is a clustered index and I do not want to change the index for this reason. Note there are several foreign keys in the table I am referring to, however the primary key to this table is an 'int'. The only option I can see is to increase the size of the field to varchar(886) instead of varchar(900).
2. Could I change the field to a text size? This way the field can be the size the user may need. Can you tell me if this is a good option or not any why?
3. Is there a way I could change the data context object so this field is not a foreign key to another table? If so, how would I accomplish this goal?
4. Are there any other options I would have?
|
|
|
|
|
following up on a thread in 'weird and wonderful', should i be calling .dispose at the end of a method on all objects that have it? i just checked the objects in a function here to see which ones have a dispose method (with intellisense) and they are:
//connection.Dispose(); // OleDbConnection
//selectCMD.Dispose(); // OleDbCommand
//adapter.Dispose(); // OleDbDataAdapter
//sources.Dispose(); // DataSet
should i be calling these every time (here and in other functions)?
(ignore the possibility of refactoring for now, unless necessary.)
|
|
|
|
|
Only if you instantiated it and aren't returning it to the caller.
Personally, I prefer to keep my Connections and Commands for the life of the app, so I wrap my Connections and Commands in another class and dispose them in my class' Dispose.
And I don't use DataAdapters and DataSets.
|
|
|
|
|
Countered!
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
|
|
|
|
|
may be worth looking into the using keyword, for example.
using (var connection = new OleDbConnection())
{
}
MSDN info on the using statement here.
Code project artice also here
|
|
|
|
|
As PIEBALDconsult said: if you create it, you should dispose it when you are finished with it.
The problem is that some objects use scarce resources, and unless they are disposed those resources do not become available for other classes within your app, or other applications until the Garbage Collector identifies them as unused, or the app ends. Since this may not happen for minutes, days, or even months, it is a very good idea to get into the habit of Disposing everything which implements IDispose. This is particularly true of SQL components, Graphics releted items, and files.
You may not notice a problem, but it is potentially there to bite you in production, and extremely difficult to track down and fix - since all you get is normally "out of memory" errors, with no indication of what caused it.
The easiest and cleanest way if to wrap the object in a using block as this also takes the object out of scope when it is Disposed, so that it cannot be accidentally referenced later.
Ideological Purity is no substitute for being able to stick your thumb down a pipe to stop the water
|
|
|
|
|
You should dispose of instances which you are 'in charge of' (i.e. you created and have not handed out persistent references of to other classes), when you are done with them.
Because of how database interaction is limited (concurrent connections usually being the limiting factor), you usually want to create, use and dispose of connections, commands and transactions quickly. For this you should use a using statement.
|
|
|
|
|
BobJanova wrote: you usually want to create, use and dispose of connections, ... quickly
Buuut... Connections use connection pooling; they don't actually use a connection until you open it, then it's returned to the pool when you close it. So you can hold onto a Connection for a long time and only open it when you need it.
|
|
|
|
|
So you should use Open and Close instead of new and Dispose on them?
|
|
|
|
|
No, use all four.
new, open, close, open, close, ..., dispose.
|
|
|
|
|
i see what ur saying. there's a clear diff b/t the (conn) object and what you do with it (opening/closing). don't destroy it if ur going to keep using it, etc. (for conn obj).
tnx for the 'using' tips et al.
thanks all
|
|
|
|
|
For a moment, let's assume that we are talking about a SqlConnection here. When you call Dispose on the connection, all it does is call Close and then calls Dispose on the object it derives from (which just fires off the EventDisposed event to any listeners, and removes the instance from any Site Container instance. So, what does all this have to do with the price of fromage?
Well, the only part that is of any real interest there is the Close, so given that this is all that the Dispose is really doing, you are fine keeping the connection available for use, and just use Close on it until you really are ready to get rid of it.
|
|
|
|
|
PIEBALDconsult wrote: So you can hold onto a Connection for a long time and only open it when you need it.
True but in normal practice that shouldn't happen.
First the significance in the object is the connection. If there is no connection then the object itself is light weight so creation itself isn't doing anything (again for most situations.)
Second if you keep it around you must explicitly manage the state. So to reuse it you would need to either explicitly check the state or implicitly assume the state is correct. The first case requires additional code and the second leads to complexity as the maintenance engineer would need to insure correct state. Both of those cases are more easily solved by using creation.
|
|
|
|
|
jschell wrote: If there is no connection then the object itself is light weight
I wouldn't assume that; this is .net.
jschell wrote: you must explicitly manage the state
I have no problem with that. I've written Windows Services that hold a single Connection for months and never had any trouble using Open and Close as needed, perhaps millions of times.
|
|
|
|
|
PIEBALDconsult wrote: I have no problem with that. I've written Windows Services that hold a single Connection for months and never had any trouble using Open and Close as needed, perhaps millions of times.
I didn't say it couldn't be done. And usage of course would have nothing to do with whether it was correctly implemented in all cases except of course that if there is an incorrect usage then it is more likely to show up with high volume.
|
|
|
|
|
jschell wrote: more likely to show up with high volume.
Indeed.
|
|
|
|
|
Hi All,
I develop a windows application using c# that use sql server databases, i want to create a setup for this application to install application and install sql server as prerequisites, but i want to set a specific configuration for this sql server such as instancename, sa password and mix mode.
please help me if you know how to do that.
Thanks all,
|
|
|
|
|
Installing SQL Server is not trivial and you would need to deal with licensing issues as well. There are other issues as well such as whether SQL Server is already installed and whether the user has SQL Server but has it on another box.
The license problem by itself would concern me. But just automating the SQL Server install is going to require a lot of work.
But I would suppose that to do automation that you would need to look into custom actions in the installer.
http://msdn.microsoft.com/en-us/library/windows/desktop/aa368066%28v=vs.85%29.aspx[^]
|
|
|
|
|
Hi,
I agree with jschell and Paul Conrad, but it is easy to but sql express as a prerequisites in c# deployment problem, but i want it to installed with my configuration not the default settings,
is it possible to do that?.
Thanks alot
|
|
|
|
|
zead wrote: is it possible to do that?.
Yes. But I doubt it is easy.
As I suggested custom actions with the MS install would be required.
Or buy an installer and learn out to use that.
|
|
|
|
|
Why not just have an installment of SQL Server be a requirement to install your application? Have you looked into SQL Server Compact Edition?
As jschell said, you may run into licensing issues if you have your set up install SQL Server when setting up your application.
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
It is possible to create a VBScript which installs SQL Server and sets some values like instance name. I do not know if the sa password can be set also. The call is something like
SQLEXPR_x86_DEU.exe /QS /Action=Install /IAcceptSQLServerLicenseTerms=true /Features=SQL,Tools /InstanceName="MyInstance"
It could be necessary to install a new version of Windows Installer before that.
After installation of SQL server, the database can be restored/attached by a call to osql with many parameters.
I don't know all the details, but with this information you can start and try.
|
|
|
|
|
Hi,
I did that using procss.start and i pass the arguments, this is the code:
Process myProcess = new Process();
string sqlExpressSetupFileLocation=@"C:\Users\Administrator\Desktop\SID.Installer.BootStrap\bin\Debug\SQLEXPR_x64_ENU.exe"
myProcess.StartInfo.FileName = sqlExpressSetupFileLocation;
string startupPath = sqlExpressSetupFileLocation.Substring(0, sqlExpressSetupFileLocation.LastIndexOf("\\"));
myProcess.StartInfo.Arguments = " [SQLSERVER2008] /INSTANCEID=\"" + instanceName + "\" /ACTION=Install " +
" //IAcceptSQLServerLicenseTerms=true /FEATURES=SQLENGINE,REPLICATION " +
" /INDICATEPROGRESS=True /QUIET=False /QUIETSIMPLE=True " +
" /INSTALLSHAREDWOWDIR=C:\\Program Files (x86)\\Microsoft SQL Server " +
" /INSTANCEDIR=C:\\Program Files\\Microsoft SQL Server\\ " +
" /INSTANCENAME=" + instanceName + " " +
" /MEDIASOURCE=" + startupPath + "\\" + " " + " /SAPWD=" + saPassword + " ";
myProcess.StartInfo.UseShellExecute = false;
myProcess.Start();
myProcess.WaitForExit();
the installation run but at the end not completed successfully
and i can't know what is the reason
thanks all;
|
|
|
|
|
you can use Install shield Software
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
