如何使用数组列表运行sql查询vb.net [英] How do I use an array list to run a sql query vb.net

问题描述

您好我目前在我的程序中创建了两个数组



一个名为ariFields，另一个名为ariValues



ariFields包含一组需要保存数据库表列值的字段

虽然ariValues包含需要插入表中的值



我想知道如何编写我的sqlcommand？



这就是我想要查询的内容

Hi I currently have created two arrays in my program

the one is called ariFields and the other ariValues

ariFields holds a set of fields that need hold the names of the column values of a database table
While ariValues holds the values that need to be inserted into the table

I would like to know how would i write my sqlcommand?

this is what i would like my query to read

cmd = new sqlcommand("Insert into tableA ariFields values(ariValues)"",con)

推荐答案

使用字符串构建器从ariField构造SQL字符串，其行包括：

Use a string builder to construct the SQL string from ariFieldsalong the lines of:

INSERT INTO myTable (FieldName1, FieldName2, ...) VALUES (@F0, @F1, ...)

然后快速循环添加参数：

Then a quick loop to add parameters:

mySqlCommand.Parameters.AddWithValue(string.Format("@F{0}", index), ariValues[index]);

示例c＃代码

Sample c# code

public static void Insert(string cs, string table, ArrayList names, ArrayList values)
{
    if (string.IsNullOrEmpty(cs) || string.IsNullOrEmpty(table)||  (names.Count != values.Count))
        return; // invalid inputs
    string strCommand = string.Format("INSERT INTO {0} ({1}) VALUES ({2})",
        table, String.Join(",", names.ToArray()), "@" + String.Join(", @", names.ToArray()));

    using (SqlConnection con = new SqlConnection(cs))
    using (SqlCommand cmd = new SqlCommand(strCommand, con))
    {
        con.Open();
        for (int i = 0; i < names.Count; i++)
        {
            cmd.Parameters.AddWithValue("@" + names[i], values[i]);
        }
        cmd.ExecuteNonQuery();
    }
}

转换后的VB.NET代码

Converted VB.NET code

Public Shared Sub Insert(cs As String, table As String, names As ArrayList, values As ArrayList)
	If String.IsNullOrEmpty(cs) OrElse String.IsNullOrEmpty(table) OrElse (names.Count <> values.Count) Then
		Return
	End If
	' invalid inputs
	Dim strCommand As String = String.Format("INSERT INTO {0} ({1}) VALUES ({2})", table, [String].Join(",", names.ToArray()), "@" & [String].Join(", @", names.ToArray()))

	Using con As New SqlConnection(cs)
		Using cmd As New SqlCommand(strCommand, con)
			con.Open()
			For i As Integer = 0 To names.Count - 1
				cmd.Parameters.AddWithValue("@" + names(i), values(i))
			Next
			cmd.ExecuteNonQuery()
		End Using
	End Using
End Sub

这篇关于如何使用数组列表运行sql查询vb.net的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！