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

MarkusR wrote:

(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

[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 wrote:

(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 Brian,

I will look into the schema.

-Markus

Brian Gideon wrote:

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 wrote:

(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
MarkusR wrote:

Thanks Brian,

I will look into the schema.

-Markus

Brian Gideon wrote:

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 wrote:

(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

zi*******@gmail.com wrote:

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
MarkusR wrote:

Thanks Brian,

I will look into the schema.

-Markus

Brian Gideon wrote:

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