# [.net] .NET and Databases

This topic is 4304 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

## Recommended Posts

I'm still relatively new to .NET, but I'm working on a windows forms based application that I want to communicate with a remote database. Assuming that I have or can get access to a web host that allows remote database connections, what is the recommended way to do database communication with .NET apps? The database is to be relatively simple, basically being a database where users can upload files, rate files, and then search/download from the collection of files. The files are typically less than 100Kb uncompressed, significantly less when compressed. Is it recommended to store these types of things in blob fields of the database? Now assume that I can't get a host that allows remote access, is it possible to have the application query the database via a php script on the server? I know for getting plain data this would be extremely simple, as the php could put together an xml formatted response to a url with the search term and stuff, but I'm curious how the actual file download would be handled in this case. Any thoughts/ideas please feel free to share.

##### Share on other sites
Quote:
 Original post by DrEvilAssuming that I have or can get access to a web host that allows remote database connections, what is the recommended way to do database communication with .NET apps?

This depends on the database engine, for MSSQL look into the data.sqlclient namespace (there lots of examples online).
Quote:
 The database is to be relatively simple, basically being a database where users can upload files, rate files, and then search/download from the collection of files. The files are typically less than 100Kb uncompressed, significantly less when compressed. Is it recommended to store these types of things in blob fields of the database?

IMO, you shouldn't store files in the database. Hundred Kb may be small for a file, but as a database field value is very large. But if you want to do it, you would use blobs, yes.
Why not store the files as ehh.. files? [smile] And perhaps store some extra meta information in the database?
Quote:
 Now assume that I can't get a host that allows remote access, is it possible to have the application query the database via a php script on the server? I know for getting plain data this would be extremely simple, as the php could put together an xml formatted response to a url with the search term and stuff, but I'm curious how the actual file download would be handled in this case.

Sure. It would become something like a web service. Since you'll be using .NET, you can load the response XML into a data set or XML document (again, loads of samples on the interwebs).

##### Share on other sites
That's why I'm asking here, my prior database + application experience is limited to an app querying php on a web server to formulate an xml response, and displaying that to the user. I haven't done anything with files yet so I'm not sure of my options.

How does an application give files to the web server? How does the web server store them? Wherever I want? Would it would scramble the filenames to prevent clashes, and possibly store these names in the fields in the database so it knows what to send later?

I suppose I can have my application log in to an ftp and upload the files to folders or whatever, but I'm interested in a more elegant/cleaner solution if possible(which is why I was considering storing the files in the db).

I would also like the tool to have options that will package up a group of files and allow them to be downloaded in batches as well. Which method would be best with this in mind?

##### Share on other sites
from memory from the last time I did this sort of thing... Correct me if I'm wrong.

AFAIR

in System.Data...

In .net, the way you access a database is using an active data object (ADO.net).
This wraps an unmanaged database connection. You use a connection 'string' object to setup the connection parametres, etc. All of this is interface driven, so your connection is represented by an IDbConnection.
You build your connection string with another interface, but I forget what it is. You then can start a transaction (IDbTransaction) if you want. To create a query, you create a command (IDbCommand).
AFAIK you can do this either by manually entering sql/whaterver into a new command object, or you can construct it using methods. I'm not 100% sure on this however.
From that point, executing your command (there are a few different ways) will return a data set (DataSet). A data set simply stores the tables returned from the command. It allows surprisingly easy access to the data as well. All this is using a data reader, which interperts the underlying databases results (IDataReader). A column from the data set will be of the type IDataRecord, which will have all sorts of methods and operators making it very easy to extract the data.

All of this sounds quite complex but it's actually *really* simply when you implement it. In theory, what it means, is that if you use the interfaces correctly, and you happen to change database, all that needs to change code wise is the original new DbConnection() needs to change to reflect the change in DB. Ie, the effort is put in by the database to conform. Which is great.

That is, of course, on my memory of the last time I did this (using firebird SQL then later mysql) which was a good while ago, and also was on .net 1.1 etc. I can say I was extremly pleased with the system, and it ran very well. Then we moved server and had a firewalled DB, so now I do things the really ugly way :-(

##### Share on other sites
I'm going to have to talk to Krisc to find out how he go SQL statements running on his MySQL server from a .NET application.

##### Share on other sites
The 'proper' way to achieve total database independant businesslogic is by specifying a database provider and connectionstring in your web.config

<appSettings/>	<connectionStrings>		<add name="DBConnection" providerName="System.Data.SqlClient" connectionString="Data Source=localhost;Integrated Security=SSPI;Initial Catalog=blabla;"/>	</connectionStrings>	<system.web>

In .net 2.0 you have the new DBProviderFactory class, which provides you a database connection based on your providerName (see above). Like this:

        private DbConnection Connection        {            get            {                DbProviderFactory provider = DbProviderFactories.GetFactory(connectionSettings.ProviderName);                DbConnection connection = provider.CreateConnection();                connection.ConnectionString = connectionSettings.ConnectionString;                return connection;            }        }        private DbCommand Command        {            get            {                DbCommand command = Connection.CreateCommand();                command.CommandType = CommandType.Text;                return command;            }                    }        private DbCommand GetCommand(string query, DbParameter[] parameters)        {            DbCommand command = Command;            command.CommandText = query;            foreach (DbParameter parameter in parameters)            {                command.Parameters.Add(parameter);            }            return command;        }        protected DbParameter GetParameter(string parameterName, object value)        {            DbParameter paramater = Command.CreateParameter();            paramater.ParameterName = parameterName;            paramater.Value = value;            return paramater;        }        protected int ExecuteNonQuery(string query, params DbParameter[] parameters)        {            DbCommand command = GetCommand(query, parameters);            command.Connection.Open();            int rowsAffected = command.ExecuteNonQuery();            command.Connection.Close();            return rowsAffected;                    }        protected object ExecuteScalar(string query, params DbParameter[] parameters)        {            DbCommand command = GetCommand(query, parameters);            command.Connection.Open();            object o = command.ExecuteScalar();            command.Connection.Close();                        return o;        }        protected DbDataReader ExecuteReader(string query, params DbParameter[] parameters)        {            DbCommand command = GetCommand(query, parameters);            command.Connection.Open();            DbDataReader reader = command.ExecuteReader();            command.Connection.Close();            return reader;        }

This will also work if you have a MySQL provider installed, and by default you can connect to Oracle, SQL Server, Access, Excel.

As for the file uploading, you can just use a webservice and up- and download byte arrays. This is by no means optimal, actually it's quite slow, but it should be OK for < 100k.

For your kind of application I honestly believe that storing your files in a database would be best. This way you can perform numerous sorting/updating actions because it's easier to store metadata with your files.