Save Data in Sql

Hello Ramco,

Steps to Implement:

1. Save Video Details in SQL Database

   • Create a table to store video details.
   • Insert video metadata into the table.

2. Download Videos into a Folder with VideoId as Filename

   • Use youtube-dl or yt-dlp (a Python-based tool) to download the video.
   • Save the file in a specified folder using the VideoId.

-- Create SQL Table

CREATE TABLE Videos (
    VideoId NVARCHAR(50) PRIMARY KEY,
    Title NVARCHAR(255),
    Description NVARCHAR(MAX),
    PublishedAt DATETIME,
    VideoUrl NVARCHAR(500)
);

Than modify you code as:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Diagnostics;
using Newtonsoft.Json;
using RestSharp;

public class Program
{
    public static void Main()
    {
        MyOwnVideos();
    }

    protected static void MyOwnVideos()
    {
        string connectionString = "your_connection_string"; // Replace with actual SQL Server connection string
        string downloadFolder = @"C:\DownloadedVideos\"; // Folder to save videos

        if (!Directory.Exists(downloadFolder))
            Directory.CreateDirectory(downloadFolder);

        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 channel ID
        request.AddParameter("key", "YOUR_YOUTUBE_API_KEY"); // Replace with actual API key
        
        IRestResponse response = client.Execute(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;
        }

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();

            foreach (var video in response.Data.items)
            {
                string videoId = video.id.videoId;
                string title = video.snippet.title.Replace("'", "''"); // Escape single quotes for SQL
                string description = video.snippet.description.Replace("'", "''");
                DateTime publishedAt = video.snippet.publishedAt;
                string videoUrl = $"https://www.youtube.com/watch?v={videoId}";

                // Save to SQL Database
                string query = $"INSERT INTO Videos (VideoId, Title, Description, PublishedAt, VideoUrl) " +
                               $"VALUES ('{videoId}', '{title}', '{description}', '{publishedAt}', '{videoUrl}');";

                using (SqlCommand cmd = new SqlCommand(query, conn))
                {
                    try
                    {
                        cmd.ExecuteNonQuery();
                        Console.WriteLine($"Inserted: {videoId}");
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine($"Error inserting {videoId}: {ex.Message}");
                    }
                }

                // Download video using yt-dlp
                DownloadVideo(videoId, downloadFolder);
            }
        }
    }

    static void DownloadVideo(string videoId, string downloadFolder)
    {
        string videoUrl = $"https://www.youtube.com/watch?v={videoId}";
        string outputPath = Path.Combine(downloadFolder, $"{videoId}.mp4");

        ProcessStartInfo psi = new ProcessStartInfo
        {
            FileName = "yt-dlp", // Ensure yt-dlp is installed and added to PATH
            Arguments = $"-o \"{outputPath}\" {videoUrl}",
            RedirectStandardOutput = true,
            UseShellExecute = false,
            CreateNoWindow = true
        };

        using (Process process = new Process { StartInfo = psi })
        {
            process.Start();
            process.WaitForExit();
            Console.WriteLine($"Downloaded: {videoId}");
        }
    }
}

// Define YouTube API response classes
public class YoutubeSearchListResponse
{
    public List items { get; set; }
}

public class Snippet
{
    public DateTime publishedAt { get; set; }
    public string title { get; set; }
    public string description { get; set; }
}

public class Id
{
    public string videoId { get; set; }
}

public class Item
{
    public Id id { get; set; }
    public Snippet snippet { get; set; }
}

Good Luck!

To save the video data in an SQL database and download the videos to a folder with the VideoId as the filename, you'll need to extend the existing code to include the database interaction and file handling functionalities. Here's an overview of the steps involved:

1. Setting up a Database Connection: Before saving data to an SQL database, establish a connection to the database using ADO.NET or an ORM (Object-Relational Mapping) tool like Entity Framework.

2. Create a Table: Design a table in the database that corresponds to the structure of the video data you intend to store. This table could include fields for VideoId, VideoTitle, Description, Published date, and potentially a filepath for the downloaded video.

3. Database Insertion: Within your `MyOwnVideos` method, modify the loop that generates the HTML table to also insert the video data into the SQL database. Utilize ADO.NET commands like `SqlCommand` to perform SQL insert operations.

4. File Handling: After saving the video data to the database, download the actual videos to a designated folder. Make sure to handle filenames using the VideoId to maintain uniqueness.

5. Exception Handling: Implement error handling mechanisms to manage any exceptions that may occur during database interactions or file operations.

6. Security: Prioritize securing your database connection and ensuring proper permissions for file read/write operations to prevent unauthorized access.

By integrating these steps into your existing code, you can achieve the goal of saving video data in an SQL database and downloading the videos to a specific folder based on their VideoId. Should you require more detailed assistance or specific code snippets for any of the steps outlined, feel free to ask!