Save in Sql Database

Question

Hi

  I have below code & i want to save it in Sql Database

protected void MyOwnVideos()
{
    StringBuilder htmlTable = new StringBuilder();
    var client = new RestClient("googleapis.com/youtube/v3");
    var request = new RestRequest("search", Method.GET);

    request.AddParameter("part", "snippet");
    request.AddParameter("type", "video");
    request.AddParameter("channelId", "UC_nNoaVw"); // Replace with your actual channelId
    request.AddParameter("key", "AIz8..."); // Replace with your actual API key

    IRestResponse<YoutubeSearchListResponse> response = client.Execute<YoutubeSearchListResponse>(request);

    // Check if the response contains data
    if (response.Data == null || response.Data.items == null || response.Data.items.Count == 0)
    {
        Console.WriteLine("No Data Found. Please check your channelId and API key.");
        return;
    }

    // Build the HTML table
    htmlTable.Append("<table class='table table-bordered table-hover datatable-highlight' id='tbldata'>");
    htmlTable.Append("<thead><tr><th>Video Id</th><th>Video Title</th><th>Description</th><th>Published</th></tr></thead>");
    htmlTable.Append("<tbody>");

    foreach (var data in response.Data.items.OrderBy(x => x.snippet.publishedAt))
    {
        htmlTable.Append("<tr>");
        htmlTable.Append($"<td>{data.id}</td>");
        htmlTable.Append($"<td>{data.snippet.title}</td>");
        htmlTable.Append($"<td>{data.snippet.description}</td>");
        htmlTable.Append($"<td>{data.snippet.publishedAt}</td>");
        htmlTable.Append("</tr>");
    }

    htmlTable.Append("</tbody>");
    htmlTable.Append("</table>");

    // Output the HTML table
    Console.WriteLine(htmlTable.ToString());
}

C#

Copy

Thanks

Answer 1

Just try to remove public keyword,

protected void Page_Load(object sender, EventArgs e)
        {
            string Cnn = ConfigurationManager.ConnectionStrings["Cnn"].ConnectionString;

            MyOwnVideos11();
        }

And check your full page code if you are missing any closing } bracket 

Answer 2

Hi

  I have defined in Web.Config . I am getting error } expected when i write Cnn line.

<connectionStrings>
    <add name="Cnn" connectionString="Data Source=JAGJIT\MSSQLSERVER_2019;Initial Catalog=Videos;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

protected void Page_Load(object sender, EventArgs e)
        {
            public string Cnn = ConfigurationManager.ConnectionStrings["Cnn"].ConnectionString;

            MyOwnVideos11();
        }

Thanks

Answer 3

Hello Ramco,

If you do not have SQL table to store data then need to create table with below properties,

CREATE TABLE YoutubeVideos (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    VideoId NVARCHAR(50),
    Title NVARCHAR(255),
    Description NVARCHAR(MAX),
    PublishedAt DATETIME
);

You can store using below code,

using System;
using System.Data.SqlClient;
using System.Text;
using System.Linq;
using RestSharp;

public class YoutubeDataSaver
{
    private string _connectionString = "Server=YOUR_SERVER;Database=YOUR_DATABASE;User Id=YOUR_USERNAME;Password=YOUR_PASSWORD;";

    protected void MyOwnVideos()
    {
        StringBuilder htmlTable = new StringBuilder();
        var client = new RestClient("https://www.googleapis.com/youtube/v3");
        var request = new RestRequest("search", Method.GET);

        request.AddParameter("part", "snippet");
        request.AddParameter("type", "video");
        request.AddParameter("channelId", "UC_nNoaVw"); // Replace with actual channelId
        request.AddParameter("key", "AIz8..."); // Replace with actual API key

        IRestResponse<YoutubeSearchListResponse> response = client.Execute<YoutubeSearchListResponse>(request);

        if (response.Data == null || response.Data.items == null || response.Data.items.Count == 0)
        {
            Console.WriteLine("No Data Found. Please check your channelId and API key.");
            return;
        }

        // Database Insertion
        using (SqlConnection conn = new SqlConnection(_connectionString))
        {
            conn.Open();

            foreach (var data in response.Data.items.OrderBy(x => x.snippet.publishedAt))
            {
                string videoId = data.id.videoId; 
                string title = data.snippet.title;
                string description = data.snippet.description;
                DateTime publishedAt = data.snippet.publishedAt;

                string query = @"
                    INSERT INTO YoutubeVideos (VideoId, Title, Description, PublishedAt) 
                    VALUES (@VideoId, @Title, @Description, @PublishedAt)";

                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    cmd.Parameters.AddWithValue("@VideoId", videoId);
                    cmd.Parameters.AddWithValue("@Title", title);
                    cmd.Parameters.AddWithValue("@Description", description);
                    cmd.Parameters.AddWithValue("@PublishedAt", publishedAt);
                    
                    cmd.ExecuteNonQuery();
                }
            }
        }

        Console.WriteLine("Data saved successfully to the database.");
    }
}

Answer 4

To save the data retrieved from the YouTube API in the provided C# code snippet into a SQL database, you would typically follow these steps:

1. Set up your SQL Database: Make sure you have a SQL database set up where you can store the data. You would typically define a table structure that matches the data you want to save, in this case, the video information.

2. Establish a Connection: You need to establish a connection to your SQL database from your C# application. You can use ADO.NET with SQLClient to achieve this.

3. Prepare SQL Insert Statements: Construct SQL INSERT statements based on the data you want to save. For each video retrieved from the API, you would create an INSERT statement to store it in the database.

4. Execute the SQL Statements: Once you have your INSERT statements prepared, you can execute them using ADO.NET commands.

Here's a simplified example to illustrate the process of saving data to a SQL database in C#:

using System;
using System.Data.SqlClient;

protected void SaveVideosToDatabase()
{
    string connectionString = "Your_SQL_Connection_String"; // Update this with your actual connection string
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        foreach (var data in response.Data.items)
        {
            string insertQuery = $"INSERT INTO Videos (VideoId, VideoTitle, Description, PublishedDate) " +
                                 $"VALUES ('{data.id}', '{data.snippet.title}', '{data.snippet.description}', '{data.snippet.publishedAt}')";

            using (SqlCommand command = new SqlCommand(insertQuery, connection))
            {
                command.ExecuteNonQuery();
            }
        }
    }
    Console.WriteLine("Data saved to the SQL database successfully.");
}

In this example:

- Replace `"Your_SQL_Connection_String"` with your actual SQL Server connection string.

- Ensure you handle exceptions and errors appropriately, and consider using parameterized queries for better security.

By executing the `SaveVideosToDatabase` method, you can save the video information from the YouTube API into your SQL database. Remember to adjust the SQL table structure and queries to match the data you want to store accurately. Let me know if you need further clarification or assistance!