C#/ MSSQL에 DB(.sql) 설치 프로그래밍

# MS-SQL server 접근하기

 

//MS-SQL server  Edition
    class DataAccess
    {

        // Use for EmployeeMgt.exe.config file   .\SQLEXPRESS 
        static string _ConnectionString = 프로젝트명.Properties.Settings.Default.설정값;

        //  static string _ConnectionString = "Data Source=(local);Initial Catalog=프로젝트명; User ID=sa;Password=sapass123!";

                        //"Data Source= (local)/ DB IP address or   .\\SQLEXPRESS  ; 
					    //Initial Catalog= Database Name; 
					    //User ID= DB User ID;
					    //Password= DB user password";

        //For Windows Authentication Connectionstring
        //static string _ConnectionString = "Data Source=.\\SQLEXPRESS; Initial Catalog=프로젝트명;   Integrated Security=true";

       static SqlConnection _Connection = null;
        public static SqlConnection Connection
        {
            get
            {
                if (_Connection == null)
                {
                    _Connection = new SqlConnection(_ConnectionString);
                    _Connection.Open();

                    return _Connection;
                }
                else if (_Connection.State != System.Data.ConnectionState.Open)
                {
                    _Connection.Open();

                    return _Connection;
                }
                else
                {
                    return _Connection;
                }
            }
        }

        public static DataSet GetDataSet(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            SqlDataAdapter adp = new SqlDataAdapter(cmd);

            DataSet ds = new DataSet();
            adp.Fill(ds);
            Connection.Close();

            return ds;
        }

        public static DataTable GetDataTable(string sql)
        {
            Console.WriteLine(sql);
            DataSet ds = GetDataSet(sql);

            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            return null; 
        }

        public static int ExecuteSQL(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, Connection);
            return cmd.ExecuteNonQuery();
        }
    }

 

 

 

 

# sql-server에 DB 설치하기

 

using MetroFramework.Forms;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace 프로젝트.Datamanager
{
    public partial class Database_Setup : MetroForm
    {
        public Database_Setup()
        {
            InitializeComponent();
            this.tabPageCreateDB.Parent = null;
            this.tabPageStartSetup.Parent = null;  
        }

        

        #region  Tab # 1
        string script = null;
        private void btntestConnection_Click(object sender, EventArgs e)
        {
            try
            {
               // prg();  
                if(rdsqlsrvauthentication.Checked == true)
                {
                  //  timer1.Start();
                    using (SqlConnection connection = new SqlConnection("Data Source='" + CmbServerName.Text + "'; User ID='" + txtUserID.Text + "';Password='" + txtpassword.Text + "'"))
                    {
                        connection.Open();
                        if (connection.State == ConnectionState.Open)
                        {
                            progressBar1.Increment(90);
                            prg();
                            MessageBox.Show("SQL Connection tested Successfully", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            progressBar1.Increment(100);
                            btnSetupstart.Visible = true;
                           // script =  richTextBox1.Text ;
                            script = File.ReadAllText(Application.StartupPath + @"\SCRIPT\DB.sql");
                            this.tabPageCreateDB.Parent = this.tabControlDBsetup; //show     
                            tabControlDBsetup.SelectedTab = tabPageCreateDB;
                        }
                        else
                        {
                            //  progressBar1.Increment(25); Initial Catalog='" + txtdatabasename.Text + "';
                            prg(); 
                            MessageBox.Show("Connection Failed");
                        }
                      
                    } 
                }
                else
                {
                    using (SqlConnection connection = new SqlConnection("Data Source='" + CmbServerName.Text + "'; Integrated Security=true" ))
                    {

                        connection.Open();

                        if (connection.State == ConnectionState.Open)
                        {
                            timer1.Stop();
                            MessageBox.Show("SQL Connection tested Successfully", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
                            btnSetupstart.Visible = true;
                            script = File.ReadAllText(Application.StartupPath + @"\SCRIPT\DB.sql");
                            this.tabPageCreateDB.Parent = this.tabControlDBsetup; //show     
                            tabControlDBsetup.SelectedTab = tabPageCreateDB;
                        }
                        else
                        {
                            MessageBox.Show("Connection Failed");
                        } 
                    } 
                }
            
            }
            catch (Exception ex)
            {
              
                MessageBox.Show("Failure: {0}", ex.Message);
            }
        }

        public void prg()
        {
            progressBar1.Visible = true;
            progressBar1.Increment(5);
           // lblprgbarCount.Text = " " + progressBar1.Value.ToString() + "%";
            if (progressBar1.Value == progressBar1.Maximum)
            {
                timer1.Stop();                 
            }
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
           // progressBar1.Visible = true;
           // progressBar1.Increment(5);
        }

        private void Database_Setup_Load(object sender, EventArgs e)
        {
            try
            {
                CmbServerName.Text = Environment.MachineName;
            }
            catch { }
        }

        private void lnkGetDatasourcename_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            progressBar1.Visible = true;
            progressBar1.Increment(40);
            try 
            {
                
                string myServer = Environment.MachineName;
                DataTable servers = SqlDataSourceEnumerator.Instance.GetDataSources();
                for (int i = 0; i < servers.Rows.Count; i++)
                {
                    if (myServer == servers.Rows[i]["ServerName"].ToString()) ///// used to get the servers in the local machine////
                    {
                        if ((servers.Rows[i]["InstanceName"] as string) != null)
                            CmbServerName.Items.Add(servers.Rows[i]["ServerName"] + "\\" + servers.Rows[i]["InstanceName"]);
                        else
                            CmbServerName.Items.Add(servers.Rows[i]["ServerName"]);
                        progressBar1.Increment(98); 
                        MessageBox.Show("Done");
                    }
                }
 
            }
            catch
            {

            }
        }
        #endregion

        #region Tab # 2
        private void btnCreateDB_Click(object sender, EventArgs e)
        {
            try
            {  
                string sqlConnectionString = "";
                if (rdsqlsrvauthentication.Checked == true)
                {
                    sqlConnectionString = @"Data Source='" + CmbServerName.Text + "' " +
                  // "; Initial Catalog='" + txtdatabasename.Text + "' " +
                   "; User ID='" + txtUserID.Text + "' " +   // Remove this line if Window Authentication  
                   "; Password='" + txtpassword.Text + "' "; // Remove this line  if Window Authentication   
                  
                }
                else
                {
                    sqlConnectionString = @"Data Source='" + CmbServerName.Text + "'; Integrated Security=true "; 
                }
                SqlConnection conn = new SqlConnection(sqlConnectionString);
                conn.Open();
                string sql = " IF NOT EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'" + txtdatabasename.Text + "') " +
                       " CREATE DATABASE   " + txtdatabasename.Text;
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                conn.Close();
                progressBar1.Increment(99);
                MetroFramework.MetroMessageBox.Show(this, "Successfully database has been Created", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);

                this.tabPageStartSetup.Parent = this.tabControlDBsetup; //show     
                tabControlDBsetup.SelectedTab = tabPageStartSetup;
            }
            catch (Exception ex)
            {
                MetroFramework.MetroMessageBox.Show(this, ex.Message);
            }
        }
        #endregion

        #region Tab # 3  execute database script
        private void btnSetupstart_Click(object sender, EventArgs e)
        {
            try
            {  
                string sqlConnectionString = "";
                if (rdsqlsrvauthentication.Checked == true)
                {
                     sqlConnectionString = @"Data Source='" + CmbServerName.Text + "' " +
                    "; Initial Catalog='" + txtdatabasename.Text + "' " +
                    "; User ID='" + txtUserID.Text + "' " +   // Remove this line if Window Authentication  
                    "; Password='" + txtpassword.Text + "' "; // Remove this line  if Window Authentication   
                    
                }
                else
                {
                    sqlConnectionString = @"Data Source='" + CmbServerName.Text + "' " +
                    "; Initial Catalog='" + txtdatabasename.Text + "'; Integrated Security=true"; 
                }           

                string[] ScriptSplitter = script.Split(new string[] { "GO" }, StringSplitOptions.None);
                using (SqlConnection connection = new SqlConnection(sqlConnectionString))
                {
                    connection.Open();
                    foreach (string str in ScriptSplitter)
                    {
                        using (SqlCommand cm = connection.CreateCommand())
                        {
                            cm.CommandText = str;
                            cm.ExecuteNonQuery();
                        }
                    }
                }
              
                string showdbsetuplink = Application.StartupPath + @"\SCRIPT\showdblnk.txt"; 
                if (System.IO.File.Exists(showdbsetuplink))
                {
                    System.IO.File.WriteAllText(showdbsetuplink, "Welcome", Encoding.UTF8); // Hide Database setup link hide
                }
                MessageBox.Show("Successfully Setup Database \n\nPlease close the Software and Restart again. And Login: \n\nUser name: admin \nPassword: admin ", "Successful", MessageBoxButtons.OK, MessageBoxIcon.Information);
                
                Login go = new Login();               
                go.Show();
                this.Close();
            }
            catch (Exception ex)
            { 
                MessageBox.Show(ex.Message);
            }
        }

        private void Database_Setup_FormClosing(object sender, FormClosingEventArgs e)
        {
            Login go = new Login();
            go.Show();
        }
        #endregion
    }
}