|
Clearing DataTable really works. But, what if there are thousands rows?
Anywayay, here's the code:
I have a Static Class which holds the only DataSet in my program. All DataTables created are stored in that DataSet.
/*******************************************************************/
public static DataSet dsObj = new DataSet(); //the one and only DataSet in my program
public static MySqlConnection connObj = new MySqlConnection(connStr);
//method to add Table or Tables to DataSet
public static void addTblsToDsObj(String tblName)
{
String[] tblArr = tblName.Split(',');
dsObj.EnforceConstraints = false; //I remove constrain before clearing DataTable, because of ForeignKey problem
for (int i = 0; i < tblArr.Length; i++ )
{
if(dsObj.Tables.Contains(tblArr[i])){ //If DataSet has same Table, I clear that table before fill it again
dsObj.Tables[tblArr[i]].Clear();
}
}
dsObj.EnforceConstraints = true; //after needed tables are cleared, I set it to true because I want to load the primary key too.
for (int i = 0; i < tblArr.Length; i++)
{
MySqlDataAdapter daObj = new MySqlDataAdapter("SELECT * FROM " + tblArr[i], connObj);
MySqlCommandBuilder cbObj = new MySqlCommandBuilder(daObj);
daObj.MissingSchemaAction = MissingSchemaAction.AddWithKey;
daObj.Fill(dsObj, tblArr[i]);
}
}
/*******************************************************************/
Now, everytime my Form load a Table from MySQL, I call method addTblsToDsObj(String tblName).
Note that code above works fine with deleted data outside my Program environtment (because if Clear method).
I repeat my question above, what if I have thousand of rows? it means I will clear a lot of unnecessary rows right? so I guess it's not an optimal performance.
Thx for any help. I'll try to post this as new message too.
|
|
|
|
|
hi
well I wanted to write a query that deletes a specific no of rows from a table wwhich are specified by the user
is there a way that u can do that..
plz help
thanks in advance
Rocky
|
|
|
|
|
DELETE Table T1
WHERE T1.ID IN (SELECT TOP @NoOfRecords T1.ID
FROM Table T1 ORDER BY T1.ID)
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
yea that looks pretty helpful I'll try that in a llittle while
thanks a lot Pete
Rocky
|
|
|
|
|
Hl, installed SRSS 2005 on my PC and every thing works fine exept when I try to deploy my report locally into my machine I get the error bellow
Reporting Services Error
--------------------------------------------------------------------------------
The report server has encountered a configuration error. See the report server log files for more information. (rsServerConfigurationError)
Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\RSReportServer.config' is denied.
Any ideas how to fix this problem?
Thanks in advance
Jimbo25
|
|
|
|
|
Hi all,
I've just installed the express edition of Sql Server 2005 + Management Studio Express and I can't get how to import data from a MS Access file. From what I've read is not possible to do through the Management Studio Express Edition so is there anu other tool to do it?
Thanks in advance,
Marc Soleda
... she said you are the perfect stranger she said baby let's keep it like this... Dire Straits
|
|
|
|
|
Marc Soleda wrote: I've just installed the express edition of Sql Server 2005 + Management Studio Express and I can't get how to import data from a MS Access file. From what I've read is not possible to do through the Management Studio Express Edition so is there anu other tool to do it?
You could write your own tool. The one I was going to suggest (SSIS) is only available with a full SQL Server edition.
|
|
|
|
|
Is there a way to change a TextBox Value in the MAIN REPORT, from a SubReport?
Thanks.
|
|
|
|
|
Hello all!
I'm trying to do a web application using open database (MS Access alternative provided by OpenOffice.org which uses the HSQL engine) but I can't find any info on it on the web...
Can you help me by telling me which driver and connection string should I use?
Thanks a lot,
b4silence
|
|
|
|
|
Is there a stored procedure that I can use to check if a database exist before I create it using C#?
|
|
|
|
|
If you are using SQL Server 2005 you can write a proc to use
select * from sys.databases.
|
|
|
|
|
I am using SQL Server 2005 Express, is there something similar that I can use to check if a table exists in the database? Thanks.
|
|
|
|
|
This will return all of the tables from the db the query is running under...
select * from sysobjects
where xtype = 'u'
|
|
|
|
|
Given a query like the following
select open_date, close_date
from view
where ( open_date between D1 and D2 or close_date between D1 and D2 )
and knowing that close_date may be NULL sometimes and that open_date is always <= to close_date, what kind of date manipulation could I do, so that I can add another field to the select list so that when both open_date and close_date are between D1 and D2, the value for the field would be 1, other wise it would be zero.
Thanks.
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
I finally figured out to add two fields and the query looks like this
select open_date, close_date,
decode(sign(open_date-D1),-1,0,1) open_date_in_range,
decode(sign(nvl(close_date-D1,-1)),-1,0,1) close_date_not_null
...
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
hi to all,
i have a problem. i have "users" table with Id. now i have a function which acceprt user_id range and give output of all "Name" in one row.
ie if select * from users where iser_id in (1,2,3)
user_id Name
1 A
2 B
15 D
45 X
3 W ...
now i want query which give me output like
"A,B,w"
i.e coma seperated output.
can anybody help me for this?
p.s Not use cusor for this (to combine string)
thanks in advance
|
|
|
|
|
hi,
getting "unsupported data" in the column when selected Price from table field .
This field is defined to have "money" type in table using
Sql server 2005
Please guide.
yog
hui gfgh kgdgrt njjn hjgkn
|
|
|
|
|
Use a cast to get it into a different format.
CAST(Price as REAL)
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Hi All,
I need to execute a command line executable from a stored procedure, any ideas on how I would do this?
Thanks
Kevin
|
|
|
|
|
It is an unwise thing to do because it is a security risk but you can use: xp_cmdshell[^]
|
|
|
|
|
Thanks, works great. Whats the security risk?
|
|
|
|
|
A sql injection attack may use that to run any code on your server. Read Colin's article on SQL Injection attacks.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Kevin Nicol wrote: Whats the security risk?
Allowing external commands to function outside of SQL Server opens some holes in a data based application. If a poorly written front appliction is attacked, or a firewall misconfigured, it is possible for an attacker to gain access to the SQL server and then from there launch commands on the server.
The problem is exacerbated by the fact that many SQL Server installations are runing in the System Account. (If I remember correctly, this was the default before Service Pack 3. After that Full SQL Server installations suggested you create a specific account for SQL Server process to run in, however they still kept a nice handy radio button to swap it back to the system account - many lazy DBAs install SQL Server to use the system account).
The system account has greater authority than the Admin account on the box. For example, did you ever notice in RegEdit that there are apparently empty folders called SECURITY in various locations? If you launch RegEdit in the system account you can see what's in there. But try it with an admin account and they look empty again.
In my SQL Injection Attack presentations I use xp_cmdshell to show how an attacker can go in through a web application and rip a full directory listing off the hard disk. I could continue the presentation and actually have it stream a file that has been routed through SQL Server into the web application then over HTTP. I don't go that far because the directory listing trick is enough to get people to sit up and consider security more seriously.
|
|
|
|
|
I need to archive the live database so that all the data from some specific date time to today(now) is copied to the other database which is infact the exact copy of the live/production database.
I can run a query which can read records, puts into new table(s) and deletes records from live database.
but
a. is there any way of doing it automatically
b. are there any tools available for this
c. What considerations should I keep in mind while doing this.
thanks
Imran
|
|
|
|
|
You are very likely going to have to learn some sql and do it your self.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|