|
Hi,
Which is better and than guarantees better rapidity and perfomance?
">>> SProcedure, SqlCommand, SqlReader, SqlDataAdapter <<<<"
I have a transaccional system and I am using SqlDataAdapter with DataSet and the times of process are very high. If somebody can help me, thanks.
Thanks,
|
|
|
|
|
First and foremost, I suppose that "SProcedure" is short for "stored procedure", since I know no object in the .NET framework with that name. The other three have their different purposes.
SqlReader: Lightweight, very fast. Read-Only, forward-read, server-side cursor. Good for populating lists or retrieving other data that you do not need to keep readily available for transactions.
SqlCommand: Pretty much a necessity if you are going to issue a transaction to a database. The only question is whether you are going to do it in a stored procedure or a text string. If the RDBMS you are using supports stored procedures, I would unconditionally support that. Not only do you wrap your insert values in parameter objects ( prevents SQL injection hacks - another column entirely ), but the stored procedure is already pre-compiled and should execute faster than a SQL-text insert.
SqlDataAdapter: Used to fill datasets / datatables with data resulting from a query. Very powerful, as you can use this object to populate relational data from your database and use the dataset object to maintain the information in memory. You just have to be very careful about how you construct your transaction logic, as it can become a major headache.
Also, check the layout of your database's table structure. Make sure that each table has ( at least ) one index defined and that you are utilizing it as often as possible when retrieving and updating table information. Finally, check your querys and check any joins that you may have. Full table joins ( joins without indexes ) and < gasp > Cartesian joins can stall even the best server if the tables are large enough.
Hope this helps,
Darien
"I don't know. I haven't tried today." - Trumpet icon Maynard Ferguson's response when asked how high he could play.
|
|
|
|
|
There's got to be something wrong with what I'm doing. The database responds fine with the following code:
** Bracketed code is edited to protect the innocent **
<code>
Imports System
Imports System.Data.OracleClient
Public Class Driver
Public Shared Sub Main()
Dim conn as New OracleConnection
conn.ConnectionString = "Data Source=[DBName]; User Id=[User Id]; Password=[Password]"
Dim cmd as New OracleCommand
cmd.CommandText = "SELECT * FROM USERS WHERE USER_ID = '[USER_ID]'"
cmd.Connection = conn
cmd.CommandType = CommandType.Text
Try
Dim odr as OracleReader
odr = cmd.ExecuteReader()
Catch ex As Exception
Console.Writeline( ex.Message )
End
End Sub
End Class </code>
Now, upon replacing :
<code> cmd.CommandText = "SELECT * FROM USERS WHERE USER_ID = '[USER_ID]'" </code>
with a parameterized query like :
<code>
cmd.CommandText = "SELECT * FROM USERS WHERE USER_ID = :pUserId"
cmd.Parameters.Add( new OracleParameter( ":pUserId", OracleType.VarChar )).Value = "[USER_ID]" ) </code>
results in the return of a ORA-12571 : TNS:Packet Writer Failure
I've tried most everything I can think of and I can't around this.
The environment is Windows 2000 Server, Oracle 9i, and VS 2003.
Thanks in advance,
Darien
"I don't know. I haven't tried today." - Trumpet icon Maynard Ferguson's response when asked how high he could play.
|
|
|
|
|
I'm working on application, that will recive multiple Excel files
and displaing it in a DataGrid.
The problem is, that when I select 2 or 3 files from OpenDialog, it
displays only the data from the first selected file, the other values
in DataGrid are null values. Why is that? And how can I solve this, that
the DataGrid will display all values. Thank you.
Here is the code:
private void btnOpen_Click(object sender, System.EventArgs e)<br />
{<br />
try<br />
{<br />
Stream myStream;<br />
OpenFileDialog openFileDialog1 = new OpenFileDialog();<br />
openFileDialog1.InitialDirectory = "E:\\" ;<br />
openFileDialog1.Filter = "txt files (*.txt)|*.txt|Allfiles(*.*)|*.*" ;<br />
openFileDialog1.FilterIndex = 2 ;<br />
openFileDialog1.RestoreDirectory = true ;<br />
openFileDialog1.Multiselect = true;<br />
<br />
if(openFileDialog1.ShowDialog() == DialogResult.OK)<br />
{<br />
if((myStream = openFileDialog1.OpenFile())!= null)<br />
{<br />
DataSet ds = new DataSet();<br />
<br />
foreach (string strFileName in openFileDialog1.FileNames)<br />
{<br />
<br />
string connStr = <br />
@"Driver={Microsoft Excel Driver (*.xls)};DBQ=" + strFileName;<br />
<br />
string sql = "SELECT * FROM [Sheet1$]";<br />
OdbcDataAdapter adapter = new OdbcDataAdapter(sql,connStr);<br />
adapter.Fill(ds,"[Sheet1$]");<br />
<br />
}<br />
gridPorocila.DataMember = "[Sheet1$]";<br />
gridPorocila.DataSource = ds.DefaultViewManager;<br />
<br />
myStream.Close();<br />
}<br />
}<br />
<br />
}<br />
catch (Exception ex)<br />
{<br />
MessageBox.Show(ex.Message.ToString());<br />
}<br />
<br />
}
|
|
|
|
|
I have the same problem with you but I can't
|
|
|
|
|
i am doing some project..and i am using vb.net language with access 2000 database. client wanna use backup function for database in case. how can i back up the database to another directory?? and wanna use save file dialog box. thanks.
|
|
|
|
|
Since Access is a file based DB, you can just copy it to a different folder:
<br />
<br />
'Gonna use FSO - need a reference to Windows Script Host object model<br />
Dim FSO as new FileSystemObject<br />
<br />
<br />
'Close any connections to the DB to ensure no access rights probs<br />
Conn.Close()<br />
<br />
'Use the FSO to copy the file<br />
FSO.CopyFile SrcMDB, DestMDB, bOverwrite<br />
<br />
Where SrcMDB is the DB to copy, DestMDB is the destination, and bOverwrite speaks for itself really - hope this helps
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
Hello,
I noticed that when I add a new row to my DB (MS Access 2002 with VB.Net), the row is inserted to a specific place according to the primary key and not at the end of the table - my primary key is client's ID.
I use a form with datagrid control for searching a row, but after adding a new row, its location in the dataset is different from the location in the database (?!?!), this form returns the row number to the main form...but the row number in incorrect...
In the searching form I use DataView and also in the main form I use the default view which sorted by the ID.
the main form gets the row number from the searching form.
Another thing is that after rebuilding the DB, the row number is correct but that's not a good solution...
I need to understand if I did something wrong with inserting or using the dataview objects. and how do I get the real row's number.
//**************************
//after double-clicking the row on the datagrid, get the default row number
RowNum = dbDsDB.Tables("clients").DefaultView.Find(DtGrd.Item(hti.Row, 2))
If you need more info, let me know - I'm stuck
thanks, Roy.
|
|
|
|
|
Although MS Access gives you a lot of flexibility, you really should not be departing so far from DDBMS principles.
-- Never rely upon storage order. You should consider that a black box and never be concerned with or rely on the order in which the rows are stored. There has never been a guarantee that the data will be stored in the order it was received. Therefore, don't use RowId!
You should use Primary Keys & Foreign Keys (referential integrity) to join tables and to match tables to forms. If enter order is important for you application, I suggest you add a time stamp attribute to your rows and reference it in your ORDER BY clause.
|
|
|
|
|
Hey Michael,
I guess I did make myself clear, I don't need the row's actual position in the DB.
I have thid code:
RowNum = dbDsDB.Tables("clients").DefaultView.Find(DtGrd.Item(hti.Row, 2))
the: DtGrd.Item(hti.Row, 2) is the current row in the datagrid(column 2).
(this is in the searching form that contains datagrid)
So, after this line of code, I suppose to get the row number in the default view:
Me.BindingContext(dbDsDB, "clients").Position = frmSrch.RowNum
(this is in the main form that calls the frmSrch).
But I won't seem to work and I get other row instead
..
Any Ideas??
thank Roy
|
|
|
|
|
I am writing a stored procedure where the WHERE clause may contain 100's of conditionals (i.e. UserID=1 OR UserID=2, etc). The field being searched will always be the same. It is just the number records to be returned I won't about. Is there anyways to pass the stored procedure an array or something similar to specify the records to return? Writing a statement that includes 100's of "UserID=1 OR UserID=2 OR..." breaks my layer abstraction so I'd rather just send the list of User ID's I want to get. Is there any way to do something like this?
|
|
|
|
|
Can you pass a comma-delimited string of values as a single varchar parameter? Then use that parameter as the list in an IN(...) operand?
|
|
|
|
|
You can try passing a string with the user id's and use it in the WHERE clause of your statement,
e.g.
SELECT *
FROM tblUsers
WHERE UserID IN (SELECT [Value] FROM dbo.Split(<code>@ListOfUserIDs</code>,','))
The code above uses the user defined function Split, which splits a string by the delimiter and converts it into a table.
Below is the user defined function that you can modify as needed.
CREATE FUNCTION dbo.Split
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
WHILE (CHARINDEX(@SplitOn,@List)>0)
BEGIN
INSERT INTO @RtnValue (value)
SELECT
Value = LTRIM(RTRIM(SUBSTRING(@List,1,CHARINDEX(@SplitOn,@List)-1)))
Set @List = SUBSTRING(@List,CHARINDEX(@SplitOn,@List)+LEN(@SplitOn),LEN(@List))
END
INSERT INTO @RtnValue (Value)
SELECT Value = LTRIM(RTRIM(@List))
RETURN
END
There is another way using the EXEC statement in your stored procedure to execute the SQL, and you don't need the split function for this. Some users argued about the efficiency and safety of using EXEC statement, but it's up to you to decide.
DECLARE SqlStr varchar(1000)
SET SqlStr = 'SELECT * FROM tblUser WHERE UserID IN (' + @ListOfUserID + ')'
EXEC (SqlStr)
Both code will work even if you only pass one ID.
I hope it helps .
|
|
|
|
|
Thanks for the function. It works great.
|
|
|
|
|
Hello all .....
i m getting a Sql Error "Log file is not available"............when i m taking the backup....its both mdf and ldf are in its predefined location.....
let me know if any solution.....
******
|
|
|
|
|
Hi everyone,
I am using Access to compare records between a SQL Server database instance and Oracle. I have the tables linked and most of the comparisons work fine. I am using joins on the table and comparing each column.
However, I have columns that have Memo fields and Access does not let me compare these fields. Also, it does not do any comparisons between the date fields.
Is there a way to compare these MEMO and date fields using some VB script. I have not used VB scripts much and would appreciate some help.
Thanks,
Pankaj
Without struggle, there is no progress
|
|
|
|
|
Hi,
I'm just wondering if it's possible to create a new table in a database based on a programatically generated DataTable without having to do it by hand?
thanks,
matt
|
|
|
|
|
If I understand you correctly - No.
|
|
|
|
|
lol dagnammit i didn't think so but thought i'd check.
|
|
|
|
|
Hi,
I created a dataset with 2 tables and a relation in a module.(VB.NET)
Now, I have a form with datagrid that suppose to show only the parent table.
How can I hide the symbol (+) from the parent datagrid without deleting the relation.
Thanks
Roy.
|
|
|
|
|
hi to all
How to connect Oracle Lite Database with ADO.NET in VB.Net for Smart Devaice Applications if anybody know that then plz. inform me.
thanks in advance.
Good bye
Manoj Mevada
|
|
|
|
|
Hi,
my query is given below.
"select dp.detail from procdure p, mentryi m, dtentryp dp
where m.master=p.proc
and dp.master =m.detail
and m.item=481"
when i am running the query its giving me the output, that is fine. now using ado.net DataReader, how do i read individual columns from different tables. I am using DataReader b'coz, I am reading a BLOB field from mysql database and converting into .jpg images, that is also working fine, if i am reading from just one table.
I need to read specified columns from different tables in the same DataReader. if i am using the instance like "p" for procdure table, it is giving me error and its not identifying the variables associated with the table.
Any help would be appreciated..
vijay
|
|
|
|
|
u can acess any column by this
dr.getstring(x)
where dr is datareader object
getstring is data type column
and x index of column which starts from 0 for the first column;P
|
|
|
|
|
I need to create a query where I pass it 'Person' and I create a column called 'There'. I'm using SQL Server 2000.
------------------
Table X
------------------
ID Desc
------------------
1 Glasses
2 Red Hair
3 Blue Eyes
-----------
Table Y
-----------
Person ID
-----------
856 1
856 3
900 1
900 2
900 3
-------------------------------
Needed Result when I pass '856'
-------------------------------
Desc There
-----------------------
Glasses True
Red Hair False
Blue Eyes True
This means that I must always output every possible 'Desc' and set 'There' to 'True' when we have a match otherwise set to 'False'.
Thank You in advance for your help!
|
|
|
|
|
This should do it:
SELECT x.Desc, case when y.ID is null then 'False' else 'True' end as There
FROM X
LEFT OUTER JOIN Y ON Y.ID = X.ID
WHERE Y.Person = 856
I hope I didn't just do your homework assignment for you!
my blog
|
|
|
|