[ahmedramzyaish]

ممكن تطبيق التعديلات هنا

كود :

'Dim dr As System.IO.StreamReader = New IO.StreamReader(Application.StartupPath & "\\servernametext.txt", System.Text.Encoding.Default)
'        Servernametext = dr.ReadLine
'Dim cn As New System.Data.SqlClient.SqlConnection("server=" & Servernametext & ";database=master;integrated security=true")
'Dim con As New System.Data.SqlClient.SqlConnection("server=" & Servernametext & ";database=Companys;integrated security=true")

Imports System.Text
Imports System.Text.RegularExpressions
Imports System.IO

'Imports System.Data.SqlClient

Module CompanyInformation
   Public DatabaseNameXT As String
   Public Servernametext As String = ""
   Dim cn As New System.Data.SqlClient.SqlConnection("server=" & Servernametext & ";database=master;integrated security=true")
   Dim con As New System.Data.SqlClient.SqlConnection("server=" & Servernametext & ";database=Companys;integrated security=true")

   Public Sub ExeSqlUser(ByVal StrSQL As String)
       'Dim conn As New System.Data.SqlClient.SqlConnection("Server =(local);database=" & My.Settings.DatabaseName & ";integrated security=false;User ID=sa;Password=uhwl1981")
       Dim da As New System.Data.SqlClient.SqlDataAdapter(StrSQL, OpenCon)
       Dim dt As New DataTable
       da.Fill(dt)
   End Sub
   Public Function ExeSqlUserDT(ByVal StrSQL As String) As DataTable
       Dim da As New System.Data.SqlClient.SqlDataAdapter(StrSQL, OpenCon)
       Dim dt As New DataTable
       da.Fill(dt)
       Return dt
   End Function
   Function OpenCon() As SqlClient.SqlConnection
       Dim conn As New System.Data.SqlClient.SqlConnection("server=" & Servernametext & ";database=" & My.Settings.DatabaseName & ";integrated security=true")
       Return conn
   End Function
   Public Sub FindDBComName(ByVal Dgv As DataGridView)
       Dim dr As System.IO.StreamReader = New IO.StreamReader(Application.StartupPath & "\\servernametext.txt", System.Text.Encoding.Default)
       Servernametext = dr.ReadLine
       Dim cn2 As New System.Data.SqlClient.SqlConnection("server=" & Servernametext & ";database=master;integrated security=true")
       Dim con2 As New System.Data.SqlClient.SqlConnection("server=" & Servernametext & ";database=Companys;integrated security=true")

       Dim StrSQL As String = "select name from sysdatabases where name='Companys'"
       Dim da As New System.Data.SqlClient.SqlDataAdapter(StrSQL, cn2)
       Dim dt As New DataTable
       da.Fill(dt)
       If dt.Rows.Count = 0 Then
           CreateDatabaseCompanys(Dgv)
       Else
           LoadDgv(Dgv)
       End If
   End Sub
   Sub CreateDatabaseCompanys(ByVal Dgv As DataGridView)
       Dim StrSQL As String = "Create database Companys"
       Dim da As New System.Data.SqlClient.SqlDataAdapter(StrSQL, cn)
       Dim dt As New DataTable
       da.Fill(dt)
       CreateTable(Dgv)
   End Sub
   Sub CreateTable(ByVal Dgv As DataGridView)
       Dim StrSQL As String = "CREATE TABLE [dbo].[CompanyName](" _
& "ID int Not Null," _
& "CompanyName nvarchar(255) Not Null," _
& "StartDate nvarchar(50) Not Null," _
& "EndDate nvarchar(50) Not Null," _
& "CompanyCase bit Not Null," _
& "ServerName nvarchar(255) Not Null," _
& "DatabaseName nvarchar(255) Not Null" _
& ") ON [PRIMARY]"
       Dim da As New System.Data.SqlClient.SqlDataAdapter(StrSQL, con)
       Dim dt As New DataTable
       da.Fill(dt)
   End Sub
   Sub LoadDgv(ByVal Dgv As DataGridView)
       Dim dr As System.IO.StreamReader = New IO.StreamReader(Application.StartupPath & "\\servernametext.txt", System.Text.Encoding.Default)
       Servernametext = dr.ReadLine
       Dim cn1 As New System.Data.SqlClient.SqlConnection("server=" & Servernametext & ";database=master;integrated security=true")
       Dim con1 As New System.Data.SqlClient.SqlConnection("server=" & Servernametext & ";database=Companys;integrated security=true")

       Dim StrSQL As String = "Select * From CompanyName"
       Dim da As New System.Data.SqlClient.SqlDataAdapter(StrSQL, con1)
       Dim dt As New DataTable
       da.Fill(dt)
       For Each row As DataRow In dt.Rows
           Dgv.Rows.Add(row.ItemArray)
       Next
   End Sub
   Public Function MaxId(ByVal ID As String, ByVal TableName As String) As Integer
       Dim dr As System.IO.StreamReader = New IO.StreamReader(Application.StartupPath & "\\servernametext.txt", System.Text.Encoding.Default)
       Servernametext = dr.ReadLine
       Dim cn3 As New System.Data.SqlClient.SqlConnection("server=" & Servernametext & ";database=master;integrated security=true")
       Dim con3 As New System.Data.SqlClient.SqlConnection("server=" & Servernametext & ";database=Companys;integrated security=true")


       Dim StrSQL As String = "Select * From " & TableName
       Dim da As New System.Data.SqlClient.SqlDataAdapter(StrSQL, con3)
       Dim dt As New DataTable
       da.Fill(dt)
       Return dt.Rows.Count + 1
   End Function
   Public Sub ExeSql(ByVal StrSQL As String)
       Dim dr As System.IO.StreamReader = New IO.StreamReader(Application.StartupPath & "\\servernametext.txt", System.Text.Encoding.Default)
       Servernametext = dr.ReadLine
       Dim cn5 As New System.Data.SqlClient.SqlConnection("server=" & Servernametext & ";database=master;integrated security=true")
       Dim con5 As New System.Data.SqlClient.SqlConnection("server=" & Servernametext & ";database=Companys;integrated security=true")


       Dim da As New System.Data.SqlClient.SqlDataAdapter(StrSQL, con5)
       Dim dt As New DataTable
       da.Fill(dt)
       MsgBox("تم بنجاح")
   End Sub
   Public Function GetDatabaseName(ByVal StrSQL As String) As String
       Dim dr As System.IO.StreamReader = New IO.StreamReader(Application.StartupPath & "\\servernametext.txt", System.Text.Encoding.Default)
       Servernametext = dr.ReadLine
       Dim cn4 As New System.Data.SqlClient.SqlConnection("server=" & Servernametext & ";database=master;integrated security=true")
       Dim con4 As New System.Data.SqlClient.SqlConnection("server=" & Servernametext & ";database=Companys;integrated security=true")

       Dim da As New System.Data.SqlClient.SqlDataAdapter(StrSQL, cn4)
       Dim dt As New DataTable
       da.Fill(dt)
       Return "StockDB" & dt.Rows.Count + 1
   End Function
   Public Function ExeSqlDT(ByVal StrSQL As String) As DataTable
       Dim da As New System.Data.SqlClient.SqlDataAdapter(StrSQL, con)
       Dim dt As New DataTable
       da.Fill(dt)
       Return dt
   End Function


#Region "Creat Database And Table From Sql Scrpt"
   'يقرأ ملفات السكول سيرفر من ملفات التكست الموجودة في مسار البرنامج'
   Public Sub ExecutesqlFile(ByVal sqlFile As String, ByVal DatabaseName As String)
       Using connection As New SqlClient.SqlConnection("server=" & Servernametext & ";database=" & DatabaseName & ";integrated security=true")
           If connection.State = ConnectionState.Closed Then connection.Open()
           Dim sql As String = ""
           Using strm As FileStream = File.OpenRead(sqlFile)
               Dim reader As New StreamReader(strm)
               sql = reader.ReadToEnd()
           End Using
           Dim regex As New RegularExpressions.Regex("^GO", RegexOptions.IgnoreCase Or RegexOptions.Multiline)
           Dim lines As String() = regex.Split(sql)
           Dim transaction As SqlClient.SqlTransaction = connection.BeginTransaction()
           Using cmd As SqlClient.SqlCommand = connection.CreateCommand()
               cmd.Connection = connection
               cmd.Transaction = transaction
               For Each line As String In lines
                   If line.Length > 0 Then
                       cmd.CommandText = line
                       cmd.CommandType = CommandType.Text
                       Try
                           cmd.ExecuteNonQuery()
                       Catch generatedExceptionName As SqlClient.SqlException
                           MsgBox(generatedExceptionName.Message, MsgBoxStyle.Critical, "")
                           transaction.Rollback()
                           Throw
                       End Try
                   End If
               Next
           End Using
           transaction.Commit()
           If connection.State = ConnectionState.Open Then connection.Close()
       End Using
   End Sub
#End Region



   ''' <summary>
   ''' Create Database For MS SQL Server
   ''' </summary>
   ''' <param name="DatabaseName">Database Name String </param>
   ''' <param name="DirectoryOfDatabase D:\test data">Directory Of Database String</param>
   ''' <param name="CreateAndCode">Creat The Database And Write Code or Write Code تاكيد على انشاء قاعدة البيانات</param>
   ''' <returns>Return True And Sql Query String,Return False And Sql Query String</returns>'
   Function CreateDatabaseForMSSQLServer(ByVal DirectoryOfDatabase As String, ByVal DatabaseName As String, ByVal CreateAndCode As Boolean) As String
       'تعريف متغيير لوضع الاستعلام فيه'
       Dim SqlStrXT As String = String.Empty
       'تعريف الاتصال بقاعدة البيانات و قاعدة البيانات ماستر حصرا'
       ' Dim Con As New SqlConnection("Server =(local);database=master;integrated security=True")
       Dim Con As New System.Data.SqlClient.SqlConnection("server=" & Servernametext & ";database=" & My.Settings.DatabaseName & ";integrated security=true")
       Try
           'تعريف متغيير سترنغ لحفظ مسار ملف م دي اف'
           Dim DIR1 As String = ""
           'تعريف متغيير لحفظ مسار ملف ال دي اف'
           Dim DIR2 As String = ""
           'تعريف داتا تيبل'
           Dim DTTable As New DataTable
           'تنظيف الداتا تيبل'
           DTTable.Clear()
           'التاكد من وجود مسار المجلد المراد وضع قاعدة البيانات فيه اذا لم ينشا لعد يقوم بانشائه'
           If Not IO.Directory.Exists(DirectoryOfDatabase & "\MyDATABASE_NAME") Then Directory.CreateDirectory(DirectoryOfDatabase & "\MyDATABASE_NAME")
           'و ضع المسار الخاص بملف ام دي اف مع اسم قاعدة البيانات'
           DIR1 = DirectoryOfDatabase & "\MyDATABASE_NAME\" & DatabaseName & ".mdf"
           'وضع مسار ملف ال دي اف مع اسم قاعدة البيانات'
           DIR2 = DirectoryOfDatabase & "\MyDATABASE_NAME\" & DatabaseName & "_log.ldf"
           'وضع الاستعلام مع المسارات و اسم قاعدة البيانات المراد انشائها'
           SqlStrXT = " CREATE DATABASE [" + DatabaseName + "] ON  PRIMARY ( NAME = N'" & DatabaseName & _
    "', FILENAME = N'" & DIR1 & "' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ) LOG ON ( NAME = N'" & _
    DatabaseName & "_log', FILENAME = N'" & DIR2 & "' , SIZE = 832KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)" & vbNewLine
           'التاكد من انك تريد انشاء قاعدة البيانات'
           If CreateAndCode Then
               'تعريف داتا ادبتر لتنفيذ الاستعلام او اذا اردت عرف كومند و اعمل له اكس كيوت نون كويري'
               Dim da As New SqlClient.SqlDataAdapter(SqlStrXT, Con)
               'التاكد من الاتصال اذا كان مغلق يقوم بفتحه'
               If Con.State = ConnectionState.Closed Then Con.Open()
               'تنفيذ الاستعلام'
               da.Fill(DTTable)
               'اغلاق الاتصال'
               If Con.State = ConnectionState.Open Then Con.Close()
           End If
           'رسالة بنص الاستعلام مع رسالة نجاح بناء قاعدة البيانات بالمسار المراد وضعها به'
           MsgBox(SqlStrXT & vbNewLine & "Database Created Successfully", MsgBoxStyle.Information, "")
           'اغلاق الاتصال اذا كان لايزال مفتوحا'
           If Con.State = ConnectionState.Open Then Con.Close()
           'ارجاع قيمة الاستعلام من الفانكشن'
           Return True & " - " & SqlStrXT '' &  vbNewLine  &  "Database Created Successfully"
       Catch ex As Exception
           'رسالة الخطا'
           MsgBox("Error : " & ex.Message, MsgBoxStyle.Critical, "Error")
           'اغلاق الاتصال اذا كان لايزال مفتوحا'
           If Con.State = ConnectionState.Open Then Con.Close()
           'ارجاع قيمة الاستعلام من الفانكشن'
           Return False & " - " & SqlStrXT
       End Try
   End Function



End Module

الكود الموجود على فورم انشاء شركة

كود :

Public Class CreateCompany
    Private Sub BtnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnAdd.Click
        Txt_CompanyName.Clear()
        Txt_DatabaseName.Clear()
        Txt_ServerName.Clear()
        Dtp_StartDate.Value = Now.ToString
        Dtp_EndDate.Value = Now.AddYears(1)
        Chk_CompanyCase.Checked = False
        Txt_ID.Text = MaxId("ID", "CompanyName")
        Txt_DatabaseName.Text = GetDatabaseName("select name from sysdatabases where name Like '%StockDB%'")
        BtnAdd.Enabled = False
        BtnEdit.Enabled = False
        BtnDelete.Enabled = False
        BtnUpdate.Enabled = True
        BtnCancel.Enabled = True
        Txt_CompanyName.Focus()
    End Sub
    Private Sub BtnEdit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnEdit.Click
        Dim StrSQL As String = "UPDATE CompanyName SET CompanyName='" & Txt_CompanyName.Text & "', StartDate='" & Dtp_StartDate.Value & "', EndDate='" & Dtp_EndDate.Value & "', CompanyCase='" & Chk_CompanyCase.Checked & "', ServerName='" & Txt_ServerName.Text & "', DatabaseName='" & Txt_DatabaseName.Text & "' Where ID='" & Txt_ID.Text & "'"
        ExeSql(StrSQL)
        MsgBox("تمت عملية تعديل معلومات الشركة بنجاح")
    End Sub
    Private Sub BtnUpdate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnUpdate.Click
        Dim SqlStrXT As String = "create Database " & Txt_DatabaseName.Text
        ExeSql(SqlStrXT)
        Try
            ExecutesqlFile(Application.StartupPath & "\SqlCreateCompany.Sql", Txt_DatabaseName.Text)
        Catch ex As Exception
            MsgBox(ex.Message)
            Exit Sub
        End Try
        Dim StrSQL As String = "INSERT INTO CompanyName (ID,CompanyName,StartDate,EndDate,CompanyCase,ServerName,DatabaseName ) VALUES (" & Txt_ID.Text & ",N'" & Txt_CompanyName.Text & "','" & Dtp_StartDate.Value & "','" & Dtp_EndDate.Value & "','" & Chk_CompanyCase.Checked & "',N'" & Txt_ServerName.Text & "',N'" & Txt_DatabaseName.Text & "')"
        ExeSql(StrSQL)
        BtnAdd.Enabled = True
        BtnEdit.Enabled = True
        BtnDelete.Enabled = True
        BtnUpdate.Enabled = False
        BtnCancel.Enabled = False
    End Sub
    Private Sub BtnCancel_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnCancel.Click
        Dim StrSQL As String = "Select * From CompanyName"
        Dim DT As New DataTable
        DT.Clear()
        DT = ExeSqlDT(StrSQL)
        If DT.Rows.Count > 0 Then
            Txt_CompanyName.Text = DT.Rows(0)("CompanyName")
            Txt_DatabaseName.Text = DT.Rows(0)("DatabaseName")
            Txt_ID.Text = DT.Rows(0)("ID")
            Txt_ServerName.Text = DT.Rows(0)("ServerName")
            Dtp_StartDate.Value = CDate(DT.Rows(0)("StartDate"))
            Dtp_EndDate.Value = CDate(DT.Rows(0)("EndDate"))
            Chk_CompanyCase.Checked = CBool(DT.Rows(0)("CompanyCase"))
        End If
        BtnAdd.Enabled = True
        BtnEdit.Enabled = True
        BtnDelete.Enabled = True
        BtnUpdate.Enabled = False
        BtnCancel.Enabled = False
    End Sub
    Private Sub BtnDelete_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnDelete.Click
        Dim StrSQL As String = "Delete From  CompanyName  Where ID = '" & Txt_ID.Text & "'"
        Dim TxtID As String = Txt_ID.Text
        If MsgBox("هل أنت متأكد من حذف السجل المحدد " & Txt_ID.Text, MsgBoxStyle.YesNo + MsgBoxStyle.Question + MsgBoxStyle.MsgBoxRight + MsgBoxStyle.MsgBoxRtlReading, "حذف سجل") = MsgBoxResult.Yes Then
            Dim SqlStrXT As String = "ALTER DATABASE [" & Txt_DatabaseName.Text & "] Set SINGLE_USER With ROLLBACK IMMEDIATE" & vbNewLine
            SqlStrXT += "DROP DATABASE [" & Txt_DatabaseName.Text & "]" & vbNewLine
            ExeSql(SqlStrXT)
            ExeSql(StrSQL)
            MsgBox("تم حذف السجل المحدد " & Txt_ID.Text & " بنجاح ", MsgBoxStyle.Information + MsgBoxStyle.MsgBoxRight + MsgBoxStyle.MsgBoxRtlReading, "حذف سجل")
        End If
    End Sub
    Private Sub BtnSearch_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnSearch.Click
        Dim StrSQL As String = "Select * From CompanyName  Where CompanyName = '" & TxtSearch.Text & "'"
        ExeSql(StrSQL)
    End Sub
    Private Sub BtnClose_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnClose.Click
        Close()
        CompanysFrm.Show()
    End Sub

    Private Sub CreateCompany_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub
End Class