使用sp将数据插入表中 [英] insert data into table using sp

问题描述

我在字符串变量中有多个值，例如

string = values（'Aloo'，'fry'，'54'，1,5）（'DalTadka'，'fyr'，'34 '，1,5）

i希望将这些数据插入多个字段的单个表中

like

table veg_table

id名称详细价格MenuType HotelId

1 Aloo油炸54 1 5

2 DalTadka油炸34 1 5





如何通过存储过程将这些数据插入veg_table

i have multiple values in string variable like
string=values('Aloo','fry','54',1,5)('DalTadka','fyr','34',1,5)
i want to insert these data in a single table in a multiple fields
like
table veg_table
id name detail price MenuType HotelId
1 Aloo fry 54 1 5
2 DalTadka fry 34 1 5


how i insert these data into veg_table through stored procedure

推荐答案

嗨Arvind，



我不知道你在这里要问的是你想用SP插入数据，但你已经用Asp.Net标记了。

我把ASP.Net作为第一个优先级并尝试了这个

首先拆分字符串，然后将其传递给插入查询或sp。如你所愿。

Hi Arvind,

I dont know what you are trying to ask here you want to insert data using SP, but you have tagged with Asp.Net.
I took ASP.Net as first priority and tried this
First split the string and then pass it to either insert query or to sp as you wish.

protected void Page_Load(object sender, EventArgs e)
        {
string temp1 = @"('Aloo','fry','54',1,5)('DalTadka','fyr','34',1,5)";
            bool val = splitRow(temp1);
}

 private bool splitRow(string fullRow)
        {
            string inputRowVal=string.Empty;
            foreach (var row in fullRow.Split(')'))
            {
                inputRowVal = row.Replace("(", "");
                SplitAndInsert(Convert.ToString(inputRowVal));
            }
            return true;
        }

private bool SplitAndInsert(string inputString)
        {
            bool isInserted = false;
            string temp=string.Empty;
            int count = 0;
            string[] param=new string[5];
            foreach (var str in inputString.Split(','))
            {
                if (str.Contains("'"))
                {
                    param[count] = str.Replace("'", "");
                }
                else
                {
                    param[count] = str;
                }
                count++;
            }

            if (param != null && param.Count() == 5)
            {
                using(SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["connSpionshopString"].ConnectionString))
                {
                    connection.Open();
                    string sql =  "INSERT INTO yourTableName VALUES(@param1,@param2,@param3,@param4,@param5)";
                    SqlCommand cmd = new SqlCommand(sql,connection);
                    cmd.Parameters.Add("@param1", SqlDbType.VarChar).Value = param[0];
                    cmd.Parameters.Add("@param2", SqlDbType.VarChar).Value = param[1];
                    cmd.Parameters.Add("@param3", SqlDbType.Int).Value = Convert.ToInt32(param[2]);
                    cmd.Parameters.Add("@param4", SqlDbType.Int).Value = Convert.ToInt32(param[3]);
                    cmd.Parameters.Add("@param5", SqlDbType.Int).Value = Convert.ToInt32(param[4]);
                    cmd.CommandType = CommandType.Text;
                    if (cmd.ExecuteNonQuery()==1)
                    {
                        isInserted = true;
                    }
                }
            }
            return isInserted;
        }

如果你通过SplitAndInsert（@'Aloo'，'fry'，'54'，1,5 ）像这样它会拆分并插入数据库。

只需把它作为参考。

希望这可以帮到你。



问候，

RK

If you pass SplitAndInsert(@"'Aloo','fry','54',1,5") like this then it will split and inserted in the database.
Just take it as reference.
Hope this helps you.

Regards,
RK

这篇关于使用sp将数据插入表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！