Imports SystemImports System.DataImports System.Data.SqlClientImports System.Data.SqlDbTypeImports System.IOImports System.CollectionsImports System.TextImports System.Security.Cryptography
Private mCSP As New DESCryptoServiceProviderPrivate 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
