C# database access

[MarkusR]

(VS2005/C#/MSSQL)

I am looking for a very efficient way to do a database lookup.

I am reading 1 to many files in a given directory. I then look up the
information based on the filename into a SQL Server database. There
might be currently only 5 files in my directory but my table may have
500,000+ records. I do not want to cache the data locally, of course.

The following method is too slow:

private int GetFileStatus(string aFilename)
{
SqlConnection Conn;
SqlDataReader reader = null;

Conn = new SqlConnection("user id=myuser;"+
"password=mypwd;" +
"server=myserver;" +
"database=mydatabase;" +
"connection timeout=30");
Conn.Open();
try
{
SqlCommand cmd = new SqlCommand("sp_GetFileID", Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(
new SqlParameter("@filename", aFilename));
reader = cmd.ExecuteReader();

if (reader.HasRows)
{
return Convert.ToInt32(reader[0].ToString());
}
else
return 0;
}
finally
{
if (reader != null)
reader.Close();
if (Conn != null)
Conn.Close();
}
}

Obviously I am new to c# database programming.

What is the best way to point to a table and locate the record base on
the filename?

Thanks

-Markus_R

 

[MarkusR]

Currently reading "New to C# - DB question" just posted.

-Markus_R

 

[Brian Gideon]

Markus,

It doesn't appear to me that the bottleneck is in the code you
provided. I think we'd need to see the contents of the stored
procedure and the schema of your database. Looking up a record among
500,000 in an well indexed table should be nearly instantaneous. If
you do suspect the problem is on the database side (which I do at this
point) then it might be better to post this question in a SQL Server
group.

Brian

 

[MarkusR]

Thanks Brian,

I will look into the schema.

-Markus

Markus,

It doesn't appear to me that the bottleneck is in the code you
provided. I think we'd need to see the contents of the stored
procedure and the schema of your database. Looking up a record among
500,000 in an well indexed table should be nearly instantaneous. If
you do suspect the problem is on the database side (which I do at this
point) then it might be better to post this question in a SQL Server
group.

Brian

(VS2005/C#/MSSQL)

I am looking for a very efficient way to do a database lookup.

I am reading 1 to many files in a given directory. I then look up the
information based on the filename into a SQL Server database. There
might be currently only 5 files in my directory but my table may have
500,000+ records. I do not want to cache the data locally, of course.

The following method is too slow:

private int GetFileStatus(string aFilename)
{
SqlConnection Conn;
SqlDataReader reader = null;

Conn = new SqlConnection("user id=myuser;"+
"password=mypwd;" +
"server=myserver;" +
"database=mydatabase;" +
"connection timeout=30");
Conn.Open();
try
{
SqlCommand cmd = new SqlCommand("sp_GetFileID", Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(
new SqlParameter("@filename", aFilename));
reader = cmd.ExecuteReader();

if (reader.HasRows)
{
return Convert.ToInt32(reader[0].ToString());
}
else
return 0;
}
finally
{
if (reader != null)
reader.Close();
if (Conn != null)
Conn.Close();
}
}

Obviously I am new to c# database programming.

What is the best way to point to a table and locate the record base on
the filename?

Thanks

-Markus_R

 

[ziggyware]

I wouldnt suggest returning a value without closing the record set or
database connection


Check out my implementation of ODBC, SqlClient and OleDb connections
using a generic wrapper:

http://www.ziggyware.com/readarticle.php?article_id=38

Altho it will not help in the specific problem you are facing in the
performance of your stored procedure, it will help in managing the
state of the database connection and allow you to have multiple
DataReader's returned.

There are other articles on my site as well if you are interested in
checking them out.

Thanks,
Ziggy

www.ziggyware.com

Thanks Brian,

I will look into the schema.

-Markus

Markus,

It doesn't appear to me that the bottleneck is in the code you
provided. I think we'd need to see the contents of the stored
procedure and the schema of your database. Looking up a record among
500,000 in an well indexed table should be nearly instantaneous. If
you do suspect the problem is on the database side (which I do at this
point) then it might be better to post this question in a SQL Server
group.

Brian

(VS2005/C#/MSSQL)

I am looking for a very efficient way to do a database lookup.

I am reading 1 to many files in a given directory. I then look up the
information based on the filename into a SQL Server database. There
might be currently only 5 files in my directory but my table may have
500,000+ records. I do not want to cache the data locally, of course.

The following method is too slow:

private int GetFileStatus(string aFilename)
{
SqlConnection Conn;
SqlDataReader reader = null;

Conn = new SqlConnection("user id=myuser;"+
"password=mypwd;" +
"server=myserver;" +
"database=mydatabase;" +
"connection timeout=30");
Conn.Open();
try
{
SqlCommand cmd = new SqlCommand("sp_GetFileID", Conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(
new SqlParameter("@filename", aFilename));
reader = cmd.ExecuteReader();

if (reader.HasRows)
{
return Convert.ToInt32(reader[0].ToString());
}
else
return 0;
}
finally
{
if (reader != null)
reader.Close();
if (Conn != null)
Conn.Close();
}
}

Obviously I am new to c# database programming.

What is the best way to point to a table and locate the record base on
the filename?

Thanks

-Markus_R

 

[MarkusR]

Thanks Ziggy,

I do close the database and reader.

What else do I need to close?

-Markus_R