Results 1 to 3 of 3

Thread: sql querry

  1. #1
    Join Date
    Jul 2011
    Posts
    2

    Unanswered: sql querry

    Hi,

    There is a table with two columns message_id and receiver_id. For one message there are many receivers. Hence we need to insert multiple rows with multiple receiver_id but same message_id. I am having message_id in a variable and receiver_ids I am carrying thru an xml. Can anybody help me with the syntax how I can do this. I am using following code but its working only when I select one receiver id and doesn’t work when receiver ids are more. Please help.

    ALTER PROCEDURE dbo.InsertMsgInfo
    (@senderUserId uniqueIdentifier,

    @date datetime,

    @subject nvarchar(50),

    @body text,

    @admindt xml,

    @distdt xml,

    @franchdt xml,

    @membdt xml,

    @advertdt xml)

    AS
    BEGIN TRY
    BEGIN TRANSACTION

    Declare
    @msgId int,
    @UserId uniqueIdentifier

    Insert into Message(senderUserId,date,subject,body)values(@sen derUserId,@date,@subject,@body)

    SET @msgId=(select max(messageId) From Message)

    /*For admin*/
    Insert into Receiver (messageId,ReceiverUserId)

    Select @msgId,
    (Select x.d.value('adminUserId[1]','uniqueIdentifier') as adminUserId
    FROM @admindt.nodes('/DocumentElement/Admindt') as x(d))

    Thanks & Regard
    rajani

  2. #2
    Join Date
    Feb 2004
    Location
    Bangalore, India
    Posts
    250
    could u post a sample xml?
    Cheers....

    baburajv

  3. #3
    Join Date
    Jul 2011
    Posts
    2
    Database db = MatrimonySqlHelper.CreateConnection(_sqlConnection String);
    //DbCommand dbCommand = db.GetSqlStringCommand("Insert into Message(senderUserId,date,subject,body)values(@sen derUserId,@date,@subject,@body)");
    DbCommand dbCommand = db.GetStoredProcCommand("InsertMsgInfo");

    //Create a stream object and Write the content of the DataTable in to it.
    MemoryStream ms_admin = new MemoryStream();
    admindt.WriteXml(ms_admin, true);
    //retreive the text from it.
    ms_admin.Seek(0, SeekOrigin.Begin);
    StreamReader sr_admin = new StreamReader(ms_admin);
    string xmlstring_admin = sr_admin.ReadToEnd();
    //close stream reader.
    sr_admin.Close();
    sr_admin.Dispose();

    //Create a stream object and Write the content of the DataTable in to it.
    MemoryStream ms_dist = new MemoryStream();
    distdt.WriteXml(ms_dist, true);
    //retrieve the text from it.
    ms_dist.Seek(0, SeekOrigin.Begin);
    StreamReader sr_dist = new StreamReader(ms_dist);
    string xmlstring_dist = sr_dist.ReadToEnd();
    sr_dist.Close();
    sr_dist.Dispose();

    //Create a stream object and Write the content of the DataTable in to it.
    MemoryStream ms_franchise = new MemoryStream();
    franchdt.WriteXml(ms_franchise, true);
    //retrive the text from it.
    ms_franchise.Seek(0, SeekOrigin.Begin);
    StreamReader sr_franchise = new StreamReader(ms_franchise);
    string xmlstring_franch = sr_franchise.ReadToEnd();
    //close stream reader.
    sr_franchise.Close();
    sr_franchise.Dispose();

    //Create a stream object and Write the content of the DataTable in to it.
    MemoryStream ms_member = new MemoryStream();
    membdt.WriteXml(ms_member, true);
    //retrieve the texy from it.
    ms_member.Seek(0, SeekOrigin.Begin);
    StreamReader sr_member = new StreamReader(ms_member);
    string xmlstring_member = sr_member.ReadToEnd();
    //close stream reader.
    sr_member.Close();
    sr_member.Dispose();

    //Create a stream object and Write the content of the DataTable in to it.
    MemoryStream ms_advert = new MemoryStream();
    advertdt.WriteXml(ms_advert, true);
    //retrieve the texy from it.
    ms_advert.Seek(0, SeekOrigin.Begin);
    StreamReader sr_advert = new StreamReader(ms_advert);
    string xmlstring_advert = sr_advert.ReadToEnd();
    //close stream reader.
    sr_advert.Close();
    sr_advert.Dispose();

    db.AddInParameter(dbCommand, "senderUserId", DbType.Guid, msg.senderUserId);
    db.AddInParameter(dbCommand, "date", DbType.DateTime, msg.date);
    db.AddInParameter(dbCommand, "subject", DbType.String, msg.subject);
    db.AddInParameter(dbCommand, "body", DbType.String, msg.body);
    db.AddInParameter(dbCommand, "admindt", DbType.Xml, xmlstring_admin);
    db.AddInParameter(dbCommand,"distdt", DbType.Xml, xmlstring_dist);
    db.AddInParameter(dbCommand,"franchdt", DbType.Xml, xmlstring_franch);
    db.AddInParameter(dbCommand,"membdt", DbType.Xml, xmlstring_member);
    db.AddInParameter(dbCommand,"advertdt", DbType.Xml, xmlstring_advert);

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •