读取文件,解密,加密,写入文件,存储过程的应用

本文链接：https://blog.youkuaiyun.com/xiaowengang/article/details/1519293

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Imports System.IO
Imports System.Collections
Imports System.Text
Imports System.Security.Cryptography

Private mCSP As New DESCryptoServiceProvider
Private Keys As Byte() = {12, 34, 56, 78, 90, 22, 45, 66}

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        '读取文件
        Dim road As String = Application.StartupPath + "/fileroad.txt"
        'MessageBox.Show(road)
        Dim roadreader As New StreamReader(road)
        Dim roadstr As String = ""
        roadstr = roadreader.ReadLine()
        roadreader.Close()
        'MessageBox.Show(roadstr)
        'Dim filestr As String = System.Configuration.ConfigurationSettings.AppSettings("fileroad")
        If roadstr <> "" Then
            Dim objreader As New StreamReader(roadstr)
            Dim line As String = ""
            line = objreader.ReadLine()
            objreader.Close()
            'Sqlconn = Trim(line)
            Dim Decryptstr As String = DecryptString(Trim(line))
            Sqlconn = Decryptstr
        Else
            MessageBox.Show("密码文件路径未设定")
        End If
    End Sub
    '解密
    Private Function DecryptString(ByVal Value As String) As String
        Dim encryptKey As String = "abcdefgh"
        Dim rgbKey As Byte()
        rgbKey = Encoding.UTF8.GetBytes(encryptKey.Substring(0, 8))
        mCSP.Key = rgbKey
        Dim rgbIV As Byte() = Keys
        mCSP.IV = rgbIV
        Try
            Dim ct As ICryptoTransform
            Dim ms As MemoryStream
            Dim cs As CryptoStream
            Dim byt() As Byte

ct = mCSP.CreateDecryptor(mCSP.Key, mCSP.IV)

byt = Convert.FromBase64String(Value)

            ms = New MemoryStream
            cs = New CryptoStream(ms, ct, CryptoStreamMode.Write)
            cs.Write(byt, 0, byt.Length)
            cs.FlushFinalBlock()

cs.Close()

            Return Encoding.UTF8.GetString(ms.ToArray())
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function

'加密
    Private Function EncryptString(ByVal Value As String) As String
        Dim encryptKey As String = "abcdefgh"
        Dim rgbKey As Byte()
        rgbKey = Encoding.UTF8.GetBytes(encryptKey.substring(0, 8))
        mCSP.Key = rgbKey
        Dim rgbIV As Byte() = Keys
        mCSP.IV = rgbIV
        Try
            Dim ct As ICryptoTransform
            Dim ms As MemoryStream
            Dim cs As CryptoStream
            Dim byt() As Byte

ct = mCSP.CreateEncryptor(mCSP.Key, mCSP.IV)

byt = Encoding.UTF8.GetBytes(Value)

            ms = New MemoryStream
            cs = New CryptoStream(ms, ct, CryptoStreamMode.Write)
            cs.Write(byt, 0, byt.Length)
            cs.FlushFinalBlock()

cs.Close()

            Return Convert.ToBase64String(ms.ToArray())
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Function
    '写入文件
    Function writefile(ByVal wrfile As String, ByVal wrconent As String)
        If Not File.Exists(wrfile) Then
            Return "文件 " & wrfile & " 未找到，请确认路径和文件名是否正确！"
            Exit Function
        End If
        wrfile.Remove(0, wrfile.Length)
        Dim iniwrite As New StreamWriter(wrfile, False)
        iniwrite.Write(wrconent)
        iniwrite.Close()
    End Function

存储过程的应用,借用SQL:

Dim str As String = ""
                str = "declare @P1 nvarchar(257)"
                str = str + " set @P1=N'" + job_new + "'"
                str = str + " declare @P2 smallint"
                str = str + " set @P2=N'" + Suffix_new + "'"
                str = str + " declare @P3 nvarchar(257)"
                str = str + " set @P3=NULL"
                str = str + " declare @P4 nvarchar(257)"
                str = str + " set @P4=NULL"
                str = str + " declare @P5 nvarchar(257)"
                str = str + " set @P5=NULL"
                str = str + " declare @P6 int"
                str = str + " set @P6=NULL"
                str = str + " declare @P7 int"
                str = str + " set @P7=NULL"
                str = str + " declare @P8 tinyint"
                str = str + " set @P8=N'" + CopyBom + "'"
                str = str + " declare @P9 tinyint"
                str = str + " set @P9=NULL"
                str = str + " declare @P10 nvarchar(257)"
                str = str + " set @P10=NULL"
                str = str + " declare @P11 nvarchar(257)"
                str = str + " set @P11=NULL"
                str = str + " declare @P12 nvarchar(257)"
                str = str + " set @P12=NULL"
                str = str + " declare @P13 nvarchar(2802)"
                str = str + " set @P13=NULL"

                Dim ss As String
                ss = ""
                ss = "insert into w_inout(proname,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13)"
                ss = ss + " values('CopyBomJobChangeSp','" + job_new + "','" + Suffix_new + "','NULL','NULL','NULL','NULL','NULL','" + CopyBom + "','NULL','NULL','NULL','NULL','NULL')"
                sql_command(ss)

                str = str + " exec CopyBomJobChangeSp N'E', @P1 output, @P2 output, @P3 output, @P4 output, "
                str = str + " @P5 output, @P6 output, @P7 output, @P8 output, @P9 output, @P10 output, "
                str = str + " @P11 output, @P12 output, @P13 output"
                str = str + " select @P1, @P2, @P3, @P4,@P5, @P6, @P7, @P8, @P9, @P10, @P11, @P12, @P13"
                mycmd.CommandText = str

                'myconn.Open()
                'mycmd.Connection = myconn
                Dim adapter_1 As New SqlDataAdapter(mycmd.CommandText, myconn) '(cmd0.CommandText, myconn) ' conn)
                Dim ds_1 As New DataSet
                ds_1.Clear()
                adapter_1.Fill(ds_1, "table1")
                Dim Job_0, Suffix_0, PsNum_0, Item_0, ItemRev_0, StartOper_0, EndOper_0, CopyBom_0, CoProductMix_0, Model_0, ConfigId_0, ConfigGid_0, Infobar_0 As String
                Dim dr_1 As DataRow
                For Each dr_1 In ds_1.Tables(0).Rows
                    Job_0 = dr_1(0).ToString
                    Suffix_0 = dr_1(1).ToString
                    PsNum_0 = dr_1(2).ToString
                    Item_0 = dr_1(3).ToString
                    ItemRev_0 = dr_1(4).ToString
                    StartOper_0 = dr_1(5).ToString
                    EndOper_0 = dr_1(6).ToString
                    CopyBom_0 = dr_1(7).ToString
                    CoProductMix_0 = dr_1(8).ToString
                    Model_0 = dr_1(9).ToString
                    ConfigId_0 = dr_1(10).ToString
                    ConfigGid_0 = dr_1(11).ToString
                    Infobar_0 = dr_1(12).ToString
                    bom_pub = CopyBom
                    FromJob_pub = Job_0
                    FromSuffix_pub = Suffix_0
                    FromItem_pub = Item_0
                    StartOper_pub = StartOper_0
                    EndOper_pub = EndOper_0
                Next

                ss = ""
                ss = "insert into w_inout(proname,col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13)"
                ss = ss + " values('CopyBomJobChangeSp','" + Job_0 + "','" + Suffix_0 + "','" + PsNum_0 + "','" + Item_0 + "','" + ItemRev_0 + "','" + StartOper_0 + "','" + EndOper_0 + "','" + CopyBom_0 + "','" + CoProductMix_0 + "','" + Model_0 + "','" + ConfigId_0 + "','" + ConfigGid_0 + "','" + Infobar_0 + "')"
                sql_command(ss)
                'myconn.Close()
                adapter_1.Dispose()
                ds_1.Dispose()

存储过程运用,output+return+success:
Dim conn As New SqlConnection
        conn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings("sqlconn")
        conn.Open()
        'Dim cmd As New SqlCommand
        'cmd.Connection = conn
        'cmd.CommandText = "declare @p3 int set @p3=0 select @p3"
        Dim da As New SqlDataAdapter
        da.SelectCommand = New SqlCommand
        da.SelectCommand.Connection = conn
        da.SelectCommand.CommandText = "procetest"
        da.SelectCommand.CommandType = CommandType.StoredProcedure
        'give a input varibes
        Dim param1 As New SqlParameter("@p1", Int)
        param1.Direction = ParameterDirection.Input
        param1.Value = Trim(Me.TextBox1.Text) 'Convert.ToInt32(Me.TextBox1.Text)
        da.SelectCommand.Parameters.Add(param1)

        Dim param2 As New SqlParameter("@p2", Int)
        param2.Direction = ParameterDirection.Input
        param2.Value = Trim(Me.TextBox2.Text)
        da.SelectCommand.Parameters.Add(param2)
        'add a outpute varibes
        Dim param3 As New SqlParameter("@p3", Int)
        param3.Direction = ParameterDirection.InputOutput
        param3.Value = Trim(Me.TextBox3.Text)
        da.SelectCommand.Parameters.Add(param3)
        'da.SelectCommand.ExecuteNonQuery()
        'Me.TextBox3.Text = param3.Value.ToString

        Dim param4 As New SqlParameter("@p4", Int)
        param4.Direction = ParameterDirection.ReturnValue
        da.SelectCommand.Parameters.Add(param4)
        da.SelectCommand.ExecuteNonQuery()
        Me.TextBox3.Text = param3.Value.ToString
        Me.Label1.Text = param4.Value.ToString
        conn.Close()

存储过程运用,input+output:
Dim conn As New SqlConnection
        conn.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings("sqlconn")
        conn.Open()
        Dim da As New SqlDataAdapter
        da.SelectCommand = New SqlCommand
        da.SelectCommand.Connection = conn
        da.SelectCommand.CommandType = CommandType.StoredProcedure
        da.SelectCommand.CommandText = "procetest_inputoutput"

        Dim param1 As New SqlParameter("@p1", Int)
        param1.Direction = ParameterDirection.Input
        param1.Value = Trim(Me.TextBox1.Text)
        da.SelectCommand.Parameters.Add(param1)

        Dim param2 As New SqlParameter("@p2", Int)
        param2.Direction = ParameterDirection.Input
        param2.Value = Trim(Me.TextBox2.Text)
        da.SelectCommand.Parameters.Add(param2)

        'Dim param3 As New SqlParameter("@p3", Int)
        'param3.Direction = ParameterDirection.InputOutput
        'param3.Value = Trim(Me.TextBox3.Text)
        'da.SelectCommand.Parameters.Add(param3)
        da.SelectCommand.ExecuteNonQuery()
        'Me.TextBox3.Text = param3.Value.ToString