How to run Application with SQL Database on a mach

问题:

I have developed an application that have a slider that showing some data from a SQL Database. I'm using Visual Studio 2010 and Microsoft SQL Server 2008.

In fact i don't face any problem with my application when i deploy it an run it on my Personal Computer. The problem occurs when i try it on another machine, and the problem is that the application couldn't be connected the SQL database. I tried to figure out the reason of the problem, so i tried it after its deployment on two machines, one of them have a SQL server installed on it, and the other one don't have the SQL Server. The Application worked perfectly on the machine that have the SQL Server installed on it, and it couldn't be connected to the database on the other machine.

This is the Connection String i have used in my application>>

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\KBank.mdf;Integrated Security=True;User Instance=True"

so if there is a way that could enable me to run the application without needing the machine to have a SQL Server installed, i would be grateful. Thanks

回答1:

You need to change the connection string to specify the computer the database is installed on. Something like the following:

connectionString="Data Source=machine\SQLEXPRESS;AttachDbFilename=|DataDirectory|\KBank.mdf;Integrated Security=True;User Instance=True"

Please note: The database on the other machine needs to be configured properly so it can be accessed from other machines.

回答2:

Your connection string is using localhost which is represented via the . in the connection string. This won't work if the machine does not have SQL Server Installed, therefore you need to change the connection string to the remote machine (The machine with a SQL Server Installation, i.e. Your personal computer).

回答3:

If you do not want to have a SQL Server Express installed, you can go for an embedded SQL server via sql server compact(http://en.wikipedia.org/wiki/SQL_Server_Compact) or alternatively have an actual networked database available.

回答4:

You need to do one of the following:

• Include the installation of SQL Server Express on the client's machine as part of the installation process
• Use a centralized SQL server on a server that you and your clients have access to

There are other data access strategies you could employ, such as serializing the data as XML, or using a different database like SQLite or using web services, but put simply, if SQL Server Express isn't installed on your client's machine, you can't force it to work.

回答5:

If you using centerl database install in Server computer and other all client computer can access to that sever.

If it's you can doing the following way:

first you change your program to write File DSN to Hard disk at start up of program

Use the following code as sample

 private void MakeDSN()
        {
            try
            {
                if (!System.IO.Directory.Exists(@"C:\OTPub"))
                {
                    System.IO.Directory.CreateDirectory(@"C:\OTPub");
                }

                if (File.Exists(@"C:\OTPub\Ot.dsn"))    //delete ErrorLogFile
                {
                    File.SetAttributes(@"C:\OTPub\Ot.dsn", FileAttributes.Temporary);
                    File.Delete(@"C:\OTPub\Ot.dsn");
                }
                string con = "[ODBC]";
                string driver = "DRIVER=SQL Server";
                string uid = "UID=sa";
                string DB = "DATABASE=OTData";
                string server = "SERVER=10.63.210.111";

                var tw = new StreamWriter(@"C:\OTPub\Ot.dsn", true); // make file in location
                using (tw)
                {
                    tw.WriteLine(con);   //write  dataline
                    tw.WriteLine(driver);
                    tw.WriteLine(uid);
                    tw.WriteLine(DB);
                    tw.WriteLine(server);
                }

                lbserver.Text="LOGIN "+server;
            }
            catch (Exception)
            {
                MessageBox.Show("File DSN Error!");
            }
        }

Enter your server Ip as string server

Now you can use connection in separate class

using System.Data.Odbc;

  class DataBaseConnection
    {
        private OdbcConnection conn1 = new OdbcConnection(@"FILEDSN=C:/OTPub/Ot.dsn;" + "Uid=sa;" + "Pwd=123;"); 

        //insert,update,delete
        public int SetData(string query)
        {
            try
           {
                conn1.Open();
                OdbcCommand command = new OdbcCommand(query, conn1);
                int rs = command.ExecuteNonQuery();
                conn1.Close();
                return rs;
            }
            catch (Exception ex)
            {
                conn1.Close();
                throw ex;
            }
        }

        //select
        public System.Data.DataTable GetData(string sql)
        {
            try
            {
                conn1.Open();
                OdbcDataAdapter adpt = new OdbcDataAdapter(sql, conn1);
                DataTable dt = new DataTable();
                adpt.Fill(dt);
                conn1.Close();
                return dt;
            }
            catch (Exception ex)
            {
                conn1.Close();
                throw ex;
            }
        }
    }

Now you can write following code your different required place for connect to database,

 DataBaseConnection db = new DataBaseConnection();

If you need SELECT query:

DataTable dt = db.GetData("SELECT * From TestTable");

If you need Insert, Update, Delete query

int i=db.SetData("INSERT INTO TestTable(name,address,tel) VALUES (testname,Colombo,0777125896) ");

This method can use connect to database without any configurations.

Important: keep remember delete DSN file when exit application for security purpose.

Hope this will help you!